June 29th, 2010, 06:45 AM
|
|
Registered User
|
|
Join Date: Oct 2009
Posts: 4
Time spent in forums: 1 h 54 m 18 sec
Reputation Power: 0
|
|
|
Calling sql file from shell script with parameters.
Hi,
I am calling a sql file script.sql from shell script and passing few parameters also as shown below:
Code:
sqlplus -S id/password @script.sql $param1 $param2
Now,In sql file I have to create a extract text file after querying oracle tables based on the parameters passed(param1,param2) as shown below:
Code:
SPOOL a.txt
SELECT col1,col2,col3,……..coln
FROM table
where col1 ='&1' and col2=`&2’;
/
SPOOL OFF
This may contain thousand of rows. The extract file will contain data row by row and each column value will be separated with a tab space. I am using SPOOL to this but the extract file is giving me values of single row in multiple rows and the passed parameter values (param1 & param2) are also getting printed in the extract file at the top with old and new labels, which I don’t want to be present in my extract file. I just want the data rows in my extract file separated by tab.
I also want some status code to be returned back to my shell script which will determine whether the sql file is successfully executed or not based on which error message will be sent vail email.
Please help me with the sample examples to resolve this problem.
Thanks in advance.
Regards,
Anil
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
