Problems with SQLPLUS in shellscript

Shell Programming and Scripting
1

Hi All,

I need to run one sql query from my shellscript.

query is something like this

SELECT DISTINCT 1 FROM ******* WHERE ***** ('CRE','CRA')
AND TO_CHAR (***** , 'DD:MM:YYYY') = TO_CHAR ( SYSDATE, 'DD:MM:YYYY')
AND ****** IN ( 0) AND ******** =0;

I can't call sql file inside shellscript (i need to write the sql query inside shell script itself) . and i need to store the output of sql to a unix variable.

How is this possible in unix ?

THanks in advance

2

You can use SQLPLUS on the command line or in shell .

sqlplus -S <user_id>/<password>@<instance_name> @<sql_file>

"-S" will run the SQL in silent mode so that it returns just the query output and nothing else.

Note : sql file should contain "exit ;" statement to close the connection .

Hope this helps .

3

Thanks for the reply.

HOw can i store the output to a shell variable ?

4

Search for sqlplus on this forum, there are a lot of threads regarding this topic here.

Regards

5

VAR=`sqlplus -S <user_id>/<password>@<instance_name> @<sql_file>

your sql code here

`

VAR will have the output.

or

you can use the Spool option in sqlplus, which will store the output into a flat file.

6

Here is script

7

Worked well thanks...However we had to remove the exception block to get it done...Don't know the exact reason behind it..

Neways great work..

8

good to know it solved your problem.you can keep exception block there as there was small typo from my end which caused error

EXCEPTION WHEN NO_DATA_FOUND THEN
out_value:=null; -- it was out_value=null;
END;