This discussion is archived
2 Replies Latest reply: Mar 6, 2013 2:19 AM by rukbat RSS

calling PL SQL code from shell

RamiReddyR Newbie
Currently Being Moderated
Hello everyone,

I have a shell script, which connects to the database and runs .sql file.
after executing of .sql file, i need to store the results in log table.

How can i achieve this one? could you please give your suggestions.

Here is my code.

#!/bin/sh
set -vx

#SCHEMA_NAME=$1
LOG="/db01/apps/applvis/VIS/apps/apps_st/appl/xxhex/12.0.0/sql/test1.log_rundate_`date +%Y%m%d`.`date +%H%M%S`"

echo ' '>>$LOG
echo 'start sqlplus .sql '>>$LOG
date >>$LOG

code="declare
v_sqlcode number;
v_sqlerrm varchar2(200);
begin
@/db01/apps/applvis/VIS/apps/apps_st/appl/xxhex/12.0.0/sql/test1.sql
exception
when others then
v_sqlcode:=sqlcode;
v_sqlerrm:=sqlerrm;
insert into error_msg values(v_sqlcode,v_sqlerrm,'FAILED');
end;";
/
RETVAL=`sqlplus -s apps/apps@VIS <<EOF >>$LOG
set serveroutput on
$code
exit;
EOF`

echo $RETVAL

EXIT_CODE=$?
if [ $EXIT_CODE = 0 ]
then
echo "Program successfull" >> $LOG
echo '<<< End of program ' >> $LOG
date >>$LOG
else
echo "ERROR Program did not successfully execute " >> $LOG
echo "EXIT CODE = $EXIT_CODE"
date >>$LOG
report_footer
exit 1
fi
echo ' ' >>$LOG
echo 'end sqlplus .sql ' >>$LOG
echo ' ' >>$LOG

Thanks,
Rami Reddy.
  • 1. Re: calling PL SQL code from shell
    Dude! Guru
    Currently Being Moderated
    echo $RETVAL
    EXIT_CODE=$?
    if [ $EXIT_CODE = 0 ]
    $? is the return status of the last command. In your example, EXIT_CODE will always be 0, unless the echo command fails, which is never going to happen.
    RETVAL=`sqlplus -s apps/apps@VIS <<EOF >>$LOG
    The $RETVAL variable simply contains the output or string returned by your sqlplus command. What you need to do is to check for content of the $RETVAL variable. What content is considered successful and what failure?

    In your case however, $RETVAL is always empty, because you redirect all output to $LOG. If you want to write the complete output of your sqlplus command to a log file, use the following after your sqlplus procedure. For example:
    exit;
    EOF
    `
    echo "$RETVAL" >> $LOG
    if [ `echo "$RETVAL" | grep FAILED` ]; then ....
  • 2. Re: calling PL SQL code from shell
    rukbat Guru Moderator
    Currently Being Moderated
    Moderator Action:
    This doesn't appear to be a Linux issue.
    Post is now moved out of the Generic Linux forum.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points