Re: Passing UNIX shell variables to a stored procedure

Assumption: you want to comunicate between Unix Shell and SQL*Plus:

If you are trying to pass stuff from shell to sqlplus, have the shell create .sql file filled with DEFINEs that you will execute in SQLPlus.

eg: use

echo "DEFINE abc = $1 " >> pass_to_sql.sql
echo "VARIABLE acd number" >> pass_to_sql.sql
echo "EXECUTE :acd := $2" >> pass_to_sql.sql

sqlplus scott/tiger_at_ora1
> @pass_to_shell
> prompt &abc
> print :acd

If you are trying to pass stuff from sqlplus to shell, redirect SQLPlus output to a .sh file that you can 'read'

eg: something like
sqlplus scott/tiger_at_ora1
> rem turn off all unnecessary settngs such as verify
> spool "pass_to_shell.sh"
>
> select "export ABC=" || sysdate from dual;
> select "echo Hi " from dual;
> spool off
>
> host pass_to_shell.sh
>

And so on.
'Hans Received on Tue Nov 04 2003 - 16:40:14 CST