Values from Oracle to Unix

Shell Programming and Scripting
1

Hello Experts
how do I get a output value from Oracle to Unix . Please give me sample Unix codings

Scenario (Filne name : test.sh <argument > )
---------

  1. Passing argument from unix to sql
  2. connecting to Oracle & from oracle get output
  3. Pass the output to unix

Thanks
Krishna

2

You could spool the output from Oracle to a file, then interogate the file.

In Unix script ....
sqlplus $USER @$BATCH/somesql.sql $GMT_OFFSET >> $BATCHLOG

In somesql.sql ..... (in part) ...

SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SET LINESIZE 250
SET PAGESIZE 50000
SET NEWPAGE  0
SET HEADING  OFF
SET RECSEP   OFF
SET FEEDBACK OFF
SET TERMOUT  OFF
SET VERIFY   OFF
SET DEFINE ~

SPOOL $SQLREPORT/somesql_report.txt;

--PL/SQL block
DECLARE
........
        AND F.FINALISED_DATE + (~1 / 24) > RUN_DATE1
        AND F.FINALISED_DATE + (~1 / 24) <= RUN_DATE2)
        AND F.ADD_TIMESTAMP + (~1 / 24) > RUN_DATE1
        AND F.ADD_TIMESTAMP + (~1 / 24) <= RUN_DATE2)
......

From my simple understanding of sql, the ~1 substitutes for the value of $GMT_OFFSET.

Hope the above is of some help to you.

3

returnedvalue=`sqlplus -s id/pwd <<EOF
set serveroutput on;
Declare

message VARCHAR2(200);

Begin

select tname into message from all_tables;

dbms_output.put_line ('tablenames '||nvl(message,''));
End;
/
set serveroutput off;
EXIT;
EOF`

X=`echo $returnedvalue | grep tablenames | awk '{print $2}'`

echo "The Query output is: "

echo "Query message 1= $X "

If you have more outputs from query, print those results in $2, $3,......

4

Please have a try......

let i/p be
rmpid=$1

echo " started passing argrument to SQL Block"

return_value=`sqlplus -s id/pwd <<EOF
select * from emp where empid=$empid;
EXIT;
EOF`

echo " fetched value from SQL is $return_value"