To pass the .sql file as a paramter to sqlplus through shell programming | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

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

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 06-16-2008
Hemamalini Hemamalini is offline
Registered User
 
Join Date: Jun 2008
Last Activity: 17 June 2008, 8:10 AM EDT
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
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 04:39 AM..
Sponsored Links
    #2  
Old 06-16-2008
jim mcnamara jim mcnamara is offline Forum Staff  
...@...
 
Join Date: Feb 2004
Last Activity: 22 October 2014, 11:15 AM EDT
Location: NM
Posts: 10,247
Thanks: 281
Thanked 803 Times in 747 Posts
use a here document

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

START is pretty much the same as @.
Sponsored Links
    #3  
Old 06-17-2008
Hemamalini Hemamalini is offline
Registered User
 
Join Date: Jun 2008
Last Activity: 17 June 2008, 8:10 AM EDT
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
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
kamalesh.p kamalesh.p is offline
Registered User
 
Join Date: Nov 2007
Last Activity: 9 June 2009, 12:59 AM EDT
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
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
Sponsored Links
    #5  
Old 06-17-2008
hanu_oracle hanu_oracle is offline
Registered User
 
Join Date: Mar 2008
Last Activity: 24 September 2014, 1:50 AM EDT
Location: MUMBAI, INDIA
Posts: 48
Thanks: 0
Thanked 1 Time in 1 Post
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.
Sponsored Links
    #6  
Old 06-17-2008
Hemamalini Hemamalini is offline
Registered User
 
Join Date: Jun 2008
Last Activity: 17 June 2008, 8:10 AM EDT
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
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.
Sponsored Links
    #7  
Old 06-17-2008
Hemamalini Hemamalini is offline
Registered User
 
Join Date: Jun 2008
Last Activity: 17 June 2008, 8:10 AM EDT
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
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.
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Can't get shell parameters to pass properly to sqlplus WhoDatWhoDer Shell Programming and Scripting 3 06-15-2011 04:26 PM
How to pass parameter from sqlplus(procedure completed) to your shell script sanora600 Shell Programming and Scripting 1 08-08-2008 05:55 AM
How to pass unix variable to SQLPLUS chiru UNIX for Advanced & Expert Users 1 06-10-2006 02:11 AM
How to pass variable to SQLPLUS in a ksh script? rwunwla Shell Programming and Scripting 6 05-25-2006 05:41 PM
How to pass Shell variables to sqlplus use them as parameters Jtrinh Shell Programming and Scripting 1 07-13-2005 04:15 AM



All times are GMT -4. The time now is 04:41 AM.