June 29th, 2010, 07:45 AM
Calling sql file from shell script with parameters.
I am calling a sql file script.sql from shell script and passing few parameters also as shown below:
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:
sqlplus -S id/password @script.sql $param1 $param2
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.
where col1 ='&1' and col2=`&2’;
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.