To pass the .sql file as a paramter to sqlplus through shell programming

Shell Programming and Scripting
1

Hi,

Currently i have a .sql file 1.sql.
I need to pass that as a parameter through a shell script to the sqlplus inside the same shell script.

How I should I do.can anyone help me pls.

I have an req where I need to send the .sql file and the place where the script has to create a .csv file with the out of the query as a parameter when the shell script is called.

Currently My script looks like this.

#!/bin/ksh
set timing on
Filename $1
Sqlfile $2
sqlplus -S username/Pwd@dbname<<EOF
set timing off heading on feedback off pagesize 0 linesize 250
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR EXIT FAILURE
spool ${filename}
@${sqlfile}
spool off;

Exit;
EOF

I am calling the script like this

./hema.sh path/sqlfile.sql path/sqlfile.csv

But I am getting an error stating

ld.so.1: sqlplus: fatal: libclntsh.so.9.0: open failed: No such file or directory

can anyone pls help me as its very urgent pls.

Thanks,
Hema.

2

use a here document

sqlplus -s user/pswd@somedb <<EOF
START 1.sql
EXIT
EOF

START is pretty much the same as @.

3

Hi Thanks for your Reply.

But I have an req where I need to send the .sql file and the place where the script has to create a .csv file with the out of the query as a parameter when the shell script is called.

Currently My script looks like this.

#!/bin/ksh
set timing on
Filename $1
Sqlfile $2
sqlplus -S username/Pwd@dbname<<EOF
set timing off heading on feedback off pagesize 0 linesize 250
WHENEVER SQLERROR EXIT FAILURE
WHENEVER OSERROR EXIT FAILURE
spool ${filename}
@${sqlfile}
spool off;

Exit;
EOF

I am calling the script like this

./hema.sh path/sqlfile.sql path/sqlfile.csv

But I am getting an error stating

ld.so.1: sqlplus: fatal: libclntsh.so.9.0: open failed: No such file or directory

can anyone pls help me as its very urgent pls.

Thanks,
Hema.

4

hi

chk ur parameters passing to ur scripts ..

./hema.sh path/sqlfile.sql path/sqlfile.csv

$1 is path/sqlfile.sql
$2 is path/sqlfile.csv

parameter passing will be like this
myscript.sh first second third

but u have taken in reverse
$1 is ur spool file
$2 is ur sql file to be ran

parameter u used in
spool ${filename}
@${sqlfile}

try like this
./hema.sh path/sqlfile.csv path/sqlfile.sql

5

Hai Hema,

Spool concept is not required in the shell scripting. Simply you can create a blank .csv file using `touch` command in unix.

Spool concept is useful only in Windows. what ever you are passing the sqlfile.sql( Input to Script )those are declared in the SQL-Part (Begin - - End;) of the below mentioned code (Execute simple Procedure):

RETVAL=`sqlplus -s USERNAME/PASSWORD@DBNAME <<EOF
SET SERVEROUTPUT ON SIZE 100000
Declare
OUT_STATUS NUMBER;
OUT_MSG VARCHAR2(200);
Begin
ODS_SP_REMOVE_PRE_SUB_DUP(OUT_STATUS, OUT_MSG);
dbms_output.put_line ('KeepThis '||OUT_STATUS ||' '||nvl(OUT_MSG,''));
End;
/
SET SERVEROUTPUT OFF
EXIT;
EOF`

X=`echo $RETVAL | grep KeepThis | awk '{print $2}'`
Y=`echo $RETVAL | grep KeepThis | awk '{print $3}'`

echo " " >> $USER_LOG
echo "Procedure: ODS_SP_REMOVE_PRE_SUB_DUP output is: " >> $USER_LOG.CSV
echo "OUT_STATUS= $X" >> $USER_LOG.CSV
echo "OUT_MSG= $Y " >> $USER_LOG.CSV

Note: You need to create the CSV file before call the Oracle Procedure.

6

Kamalesh,

thanks for noticing that error.

I tried the same way as you told but even then I am getting the same error.

./hema.sh[3]: Filename: not found
./hema.sh[4]: Sqlfile: not found
ld.so.1: sqlplus: fatal: libclntsh.so.9.0: open failed: No such file or directory
./hema.sh[5]: 18041 Killed

Thanks,
Hema.

7

Could you Please explain me what you are trying to do in your code as I am not very good in shell.

Y I am passing the ,sql file name as a paramater is mainly because code resuablity.

so could you please explain what you are passing as a parameter to you procedure.

Thanks,
Hema.

8

hemamalini,

i guess the prob is with the variables that ur using,....

unix is case sensitive ...chk the variables u r using ....

some r in title case .....and some r in lower case,....:b:

Thanks
kamalesh.

9

Hi Hema,

I had given choice to u only. U know oracle - - I am only executing the simple Stored Procedure{ODS_SP_REMOVE_PRE_SUB_DUP(OUT_STATUS, OUT_MSG)}. Instead of SP, You can use the simple queries inside the PLSQL loop.

Declare

    • Declaration section
      Begin
    • Executable Section
    • Eg: SQL Queries, Functions, Stored Procedures, Packages ...
      End;

Thats all...

For code re-usability. Take the $1 variable value in the SQL-Query WHERE condition.
If you are perfect in PLSQL, u are make this code very easily & confidently..

Regards
Hanuma

10

Kamalesh i tried the same even now I amgetting the same Error.

Thanks Hanu,

Will try that too.

Anybody else have any idea pls help me.