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.

use a here document
START is pretty much the same as @.

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.

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

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.

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,....




Thanks
kamalesh.

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.