Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Command line arguement for SQLPlus

SOLVED
Go to solution
Chris Fung
Frequent Advisor

Command line arguement for SQLPlus

Hi all,

Just wondering if it is possible to pass command line arguement to .sql file?

E.g. SQL> abc.sql 1 2

Just like shell script accepting paramter.

Thanks,

Chris,
3 REPLIES
Kenneth_19
Trusted Contributor
Solution

Re: Command line arguement for SQLPlus

Yes, you can.

In the SQL script file, similar to shell scripts, instead of using "$1", "$2", ..., replace it with "&1", "&2", ...

If you do not want the &1 or &2 to be definded repeatedly in the subsequent reference inside the sql scripts, use &&1, &&2, ... instead.

Regards,
Kenneth
Always take care of your dearest before it is too late
Pierce Byrne_1
Frequent Advisor

Re: Command line arguement for SQLPlus

You can also run this via a script
eg
#!/bin/ksh
MYVAR=10
sqlplus user/password << EOF
select count(1)
from mytable m
where m.myrow = $MYVAR
/
EOF
Zafar A. Mohammed_1
Trusted Contributor

Re: Command line arguement for SQLPlus

You can pass any no. of parameters. Example

!/bin/ksh
echo "Enter Database User Name : \c "
read username
echo "Enter Password : \c "
read password
echo "Enter Employee Name : \c "
read ename

sqlplus -s << SQLEOF
${username}/${password}
SELECT *
FROM EMPLOYEE
WHERE ENAME='${ename}';

SQLEOF


Thanks
Zafar