Re: Oracle 10g, SQLPLUS, unix, passing argument question

On Feb 1, 11:26 am, zw <MPC8..._at_gmail.com> wrote:
> Hi
>
> Could someone give 2 simple examples using sqlplus command line on
> 1 - how to pass unix shell argument/variables to a sql file, which
> will use the arguments (eg $1, $2, etc) to execute sql statements.
> 2 - how to retrieve variable values in sql file and pass to unix shell
> variables
>
> Are there books out there that show how to use unix/linux shell
> variables with sqlplus scripts ?
>
> Thanks

1
simply
sqlplus <username/password> @sqlfile "${1}" -- literals or
sqlplus <username/password> @sqlfile ${1} -- numbers or
sqlplus <username/password> <<EOD
connect ${UID}
define var="${1}"
select * from dual;
select * from v\$session where ...;
exit
EOD will all work

2 sqlplus fully supports IO redirection, so you can assign sqlplus output to a variable or multiple variables

You need to make sure you have
set heading off newpage 0 pagesize 0 feedback off in your sqlcode.
Then you can
var=`sqlplus <username>/<passw> @<script> or
sqlplus <username>/<password> @<script> | while read var1 var2 do
echo $var1
echo $var2
done

etc.

--
Sybrand Bakker
Senior Oracle DBA