Home » Infrastructure » Unix » Passing Unix variables to SQL scripts withe a shell
Passing Unix variables to SQL scripts withe a shell [message #97071] Wed, 14 November 2001 06:49 Go to next message
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?

----------------------------------------------------------------------
Re: Passing Unix variables to SQL scripts withe a shell [message #97074 is a reply to message #97071] Thu, 15 November 2001 11:38 Go to previous messageGo to next message
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;

----------------------------------------------------------------------
Re: Passing Unix variables to SQL scripts withe a shell [message #97363 is a reply to message #97071] Wed, 27 March 2002 15:10 Go to previous messageGo to next message
Jen
Messages: 23
Registered: June 2001
Junior Member
thanks andrew! that worked!
Re: Passing Unix variables to SQL scripts withe a shell [message #98223 is a reply to message #97074] Fri, 14 May 2004 11:20 Go to previous message
Pranav
Messages: 4
Registered: January 2002
Junior Member
Is there a way to get value fetched using SQL statement back to shell script?
Previous Topic: ORA-00600: internal error code
Next Topic: 8.1.7-installation on HP-unix hangs
Goto Forum:
  


Current Time: Sat Feb 29 05:38:09 CST 2020