Calling PL/SQL Script in Shell Programming

Hi all,

In a shell script I need to pass two parameters to a pl/sql script and get the ouput of the pl/sql script and use it in shell script.

For example

Shell script : test.sh

PL/SQL script : get_id.sql parameter1 parameter2

Actually get_id.sql has a select statement something like this

select max(sal) from emp where emp_id in (parameter1,parameter2)

Now I need to get the max(sal) from the sql script back ino the shell script say a variable and echo it .

Would be great if you outline the script at a high level..

Thanks in advance
LM

Lijju,
See if this would work for you.
Create a shell file named "my_shell.sh" as follows:

mParm1=$1
mParm2=$2
mSQL="database your_database;"
mSQL=$mSQL"select max(sal) from emp where emp_id in (${mParm1},${mParm2});"
echo $mSQL
echo $mSQL | <sql_pgm_call> 1>mOutFile 2>>mLogFile

<sql_pgm_call> is the name of the program you use to run your
SQL statements, ie, in Informix is either isql or dbaccess.

Then call the above shell as:

my_shell.sh first_parm second_parm