OraFAQ Forum: Unix » Passing Unix variables to SQL scripts withe a shell

Home » Infrastructure » Unix » Passing Unix variables to SQL scripts withe a shell

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Passing Unix variables to SQL scripts withe a shell [message #97071]

Wed, 14 November 2001 06:49

SCM
Messages: 1
Registered: November 2001

Junior Member

If I execute sql scripts from a unix shell how does one pass a unix variable to the scripts in the shell?

----------------------------------------------------------------------

Report message to a moderator

Re: Passing Unix variables to SQL scripts withe a shell [message #97074 is a reply to message #97071]

Thu, 15 November 2001 11:38

andrew again
Messages: 2577
Registered: March 2000

Senior Member

You could use the "here document" systax of exbedding the statements directly in the shell script, or call an external file.

Replace each '~' below with a '<' (the correct source wont post).

#!/bin/ksh
v_limit=5

echo "====>Here is the first"
# note escaped $
sqlplus -s scott/tiger@dev ~~EOF > log1.txt
set pagesize 0 feedback off verify off heading off echo off
WHENEVER SQLERROR EXIT 1
SELECT name from v$parameter where rownum < $v_limit;
exit;
EOF

echo "====>Here is the second"
sqlplus -s scott/tiger@dev @t.sql $v_limit > log2.txt

This is t.sql
=============
set pagesize 0 feedback off verify off heading off echo off
spool log3.txt
SELECT name from v$parameter where rownum < &1 ;
spool off
exit;

----------------------------------------------------------------------

Report message to a moderator