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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting To pass the .sql file as a paramter to sqlplus through shell programming
# 1  
Old 06-16-2008
To pass the .sql file as a paramter to sqlplus through shell programming

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.

Last edited by Hemamalini; 06-17-2008 at 05:39 AM..
# 2  
Old 06-16-2008
use a here document
Code:
sqlplus -s user/pswd@somedb <<EOF
START 1.sql
EXIT
EOF

START is pretty much the same as @.
# 3  
Old 06-17-2008
Question Urgent pls help

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  
Old 06-17-2008
Bug chk once ur parameters

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  
Old 06-17-2008
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 - - EndSmilie 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  
Old 06-17-2008
Hi Kamalesh

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  
Old 06-17-2008
Hi Hanu

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.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Pass value from sqlplus to shell on AIX

hello friend good morning I have a problem, how can I take the value that the PROCEDURE returns to me in the variable "CodError", when the connection to the bbdd is closed I lose the value and I need it in the shell #AIX cat <<EOF | sqlplus -s ${ORA_LOGIN}/${ORA_PASSWORD} > $logftmp set... (6 Replies)
Discussion started by: tricampeon81
6 Replies

2. Shell Programming and Scripting

Unable to pass value from .Shell script to .SQL file

Hi All, I am new to shell script. I am trying to pass value from .sh file to .sql file . But I am able to run the .sql file from .sh file with values in sql file. But I am unable to pass the values from .sh file. can some one please help to resolve this. here is my .sh file s1.sh ... (4 Replies)
Discussion started by: reddy298599
4 Replies

3. Programming

How to pass parameter from file to sqlplus in UNIX?

i have file in which i have employee id are there and every time number of employee id are different in file means number of count of employee id in file are every time different. 343535435 365765767 343534543 343543543 i want to pass this file to sqlplus and sql command is ... (7 Replies)
Discussion started by: pallvi_mahajan
7 Replies

4. Shell Programming and Scripting

How to pass Variable from shell script to select query for SqlPlus?

echo "set echo off"; echo "set feedback off"; echo "set linesize 4000"; echo " set pagesize 0"; echo " set sqlprompt ''"; echo " set trimspool on"; Select statement is mentioned below echo "select res.ti_book_no from disney_ticket_history res where res.ti_status =${STATUS} and... (7 Replies)
Discussion started by: aroragaurav.84
7 Replies

5. Shell Programming and Scripting

Can't get shell parameters to pass properly to sqlplus

Gurus, The issue I'm having is that my Shell won't accept SQL parameters properly...... Here's they way I'm running it.... applmgr@ga006hds => sh CW_MigrationDeployScript.sh apps <appspwd> <SID> '01-JAN' '31-MAR' The process just hangs not submitting the SQL job... ... (3 Replies)
Discussion started by: WhoDatWhoDer
3 Replies

6. UNIX and Linux Applications

how to execute multiple .sql scripts from within a shell script using sqlplus

using sqlplus I want to execute a .sql script that has dbms_output statments in rhe script. I want to write the dbms_output statements from .sql file to a log file. is this possible. thanks any help would be appreciated :wall: (1 Reply)
Discussion started by: TRS80
1 Replies

7. Shell Programming and Scripting

How to pass arguments to SQL file passed in shell script?

Hi, I am using SYBASE database. in my script i am connecting to DB via using isql. isql -U${S_USER} -S${S_SERV} -D${S_DB} -P${S_PWD} -b0 -w3000 -h0 -s"|" -i${MYDIR}/ABC.sql -oXYZ.txt << FINSQL i am taking a ABC.sql file to use the queries written in it and storing the output in... (3 Replies)
Discussion started by: dazdseg
3 Replies

8. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

9. Shell Programming and Scripting

How to pass parameter from sqlplus(procedure completed) to your shell script

if then # mail -s "Import failed file does not exist" sanjay.jaiswal@xyz.com echo "FILE does not exist" exit 1 fi echo "FILE EXIST" size=-1 set $(du /export/home/oracle/nas/scott21.dmp.gz) while do echo "Inside the loop" size=$1 set $(du... (1 Reply)
Discussion started by: sanora600
1 Replies

10. Shell Programming and Scripting

How to pass Shell variables to sqlplus use them as parameters

Hi, I am trying to pass some of the variables in my shell scripts to the sqlplus call and use them as parameters. For example, I would like to replace the 'SAS', and '20050612' with $var1 and $var2, respectively, how can I do that? --------------------------------------------------------... (1 Reply)
Discussion started by: Jtrinh
1 Replies
Login or Register to Ask a Question