2 Replies Latest reply: Mar 6, 2013 4:19 AM by rukbat RSS

    calling PL SQL code from shell

    Rami Reddy R
      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
          Catch-22
          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
            Moderator Action:
            This doesn't appear to be a Linux issue.
            Post is now moved out of the Generic Linux forum.