Command line arguement for SQLPlus

[Chris Fung]

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,

[Kenneth_19 · Accepted solution]

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

[Pierce Byrne_1]

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]

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