Friday, November 14, 2008

How to Send Parameter to sql file?

To run a set of queries (DDL or DML or DQL) in sql plus , we should write them in a sequence required to a sql file and execute that sql file to have the relevant output.

As we do open sql plus editor where we write our query language and execute them,
Similarly we save a sql file in a specific location with all set of queries in it can be executed with one line of statement.


Say set if queries I want to execute

Select * from tab;
Select sysdate fom dual;
Select 1 as Number from dual;

I can save all these sql queries in a sql file at c:\queries.sql;
In sql plus at the prompt I say

@c:\queries.sql and press enter.

The queries which are written in this file are executed.

Now there may be situation where we need to send an input parameter to this sql file which reads that in one of its query.


Select ‘My Name is &1 ‘ as MyName from dual;
Save at c:\myname.sql;

Here &1 denotes to consider first parameter among the input parameter list.
Now from sql plus I say
@c:\myname.sql Srinivas

Output :

No comments:

Post a Comment