Returning values from SQL*Plus to shell

There are several projects that I worked on where I had to get return values from Oracle stored procedures or PL/SQL block into shell script. If the embedded script returns only one value then shell back-tick can serve the purpose.

#!/usr/bin/bash

ORACLE_HOME=/cygdrive/c/app/abhattacharya/oracle

DW_DB_USER=abhijit

DW_DB_PWD=abhijit

DW_DB_NAME=knut

OUTPUT=`${ORACLE_HOME}/bin/sqlplus -s ${DW_DB_USER}/${DW_DB_PWD}\@${DW_DB_NAME}<<EOF

SET HEADING OFF FEEDBACK OFF VERIFY OFF TIME OFF TIMING OFF

SET SERVEROUTPUT ON SIZE 1000000

WHENEVER SQLERROR EXIT SQLCODE

DECLARE

   l_x INT ;

BEGIN

   l_x := 5 ;

   DBMS_OUTPUT.PUT_LINE(l_x) ;

END ;

/                                                                                                                                                                                                                                                                                                                     

This technique doesn’t work if the embedded code needs to return more than one rows.  Bash shell arrays come handy for that scenario. The following script simulates multiple values being returned using DBMS_OUTPUT statements.

#!/usr/bin/bash

ORACLE_HOME=/cygdrive/c/app/abhattacharya/oracle

DW_DB_USER=abhijit

DW_DB_PWD=abhijit

DW_DB_NAME=knut

OUTPUT_LIST=(`${ORACLE_HOME}/bin/sqlplus -s ${DW_DB_USER}/${DW_DB_PWD}\@${DW_DB_NAME}<<EOF

SET HEADING OFF FEEDBACK OFF VERIFY OFF TIME OFF TIMING OFF

SET SERVEROUTPUT ON SIZE 1000000

WHENEVER SQLERROR EXIT SQLCODE

DECLARE

   l_x INT ;

BEGIN

   l_x := 5 ;

   DBMS_OUTPUT.PUT_LINE(l_x) ;

   DBMS_OUTPUT.PUT_LINE(l_x) ;

END ;

/

The script worked well till I needed another functionality – to return Oracle error code to shell in case PL/SQL fails for violating any business rule. In case Oracle raises an exception shell return status variable “$?” can be used to capture that. But it will not provide SQL error message (SQLERRM). To make the script robust, I decided to handle any exception within PL/SQL exception block and return SQLERRM & SQLCODE as part of DBMS_OUTPUT statement.

To differentiate between an error and successful execution, it is useful to give the output a structure similar to record. We can use the first field to indicate success or failure with additional fields separated by field separator. It is necessary to make sure that the field separator will not appear in the data. I have used ‘~’ as separator here but one field separator that I really like is Ctrl-A. It is quite rare to find Ctrl-A in regular data set. After these changes my script looks like as follows:

#!/usr/bin/bash

ORACLE_HOME=/cygdrive/c/app/abhattacharya/oracle

DW_DB_USER=abhijit

DW_DB_PWD=abhijit

DW_DB_NAME=knut

OUTPUT_LIST=(`${ORACLE_HOME}/bin/sqlplus -s ${DW_DB_USER}/${DW_DB_PWD}\@${DW_DB_NAME}<<EOF

SET HEADING OFF FEEDBACK OFF VERIFY OFF TIME OFF TIMING OFF

SET SERVEROUTPUT ON SIZE 1000000

SET LINESIZE 1000

WHENEVER SQLERROR EXIT SQLCODE

DECLARE

   l_x INT ;

   l_err_code INT := 0 ;

   l_err_msg VARCHAR2(4000) := '';

BEGIN

   l_x := 5 ;

   DBMS_OUTPUT.PUT_LINE(l_err_code || '~' || l_err_msg || '~' || l_x ) ;

   DBMS_OUTPUT.PUT_LINE(l_err_code || '~' || l_err_msg || '~' || l_x) ;

   RAISE_APPLICATION_ERROR(-20001,'Testing-of-error') ;

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE || '~' || REPLACE(SQLERRM,' ' ,CHR(1)) ) ;

END ;

/

 

Similar technique can be used for returning values from mySQL to shell as well.