Wednesday, May 28, 2008

How can one pass operating system parameters to SQL*Plus?

Suppose you want to pass Operating System parameter var1, var2 to Sql*plus. Then you can write commnad as,
sqlplus username/password @cmdfile.sql var1 var2
Here OS parameter var1 will be mapped to SQL*Plus variable &1 var2 will be mapped to &2.
With an example I will demonstrate the procedure. This example will take two parameter SYDATE and dual will will display current date.

1)Create a sql file.
-bash-3.00$ vi x.sql
select &1 from &2;
exit ;

2.Invoke the sqlplus with parameters.
-bash-3.00$ sqlplus arju/a @x.sql SYSDATE dual
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 02:13:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

old 1: select &1 from &2
new 1: select SYSDATE from dual

SYSDATE
---------
29-MAY-08

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Here sysdate is returned.

No comments:

Post a Comment