Getting Errors back in routine frm dsexecute->sqlscript

[psluser]

hi,

we are using before/afterjob subroutines to call stored procedures through sqlplus before/after our job executes. We are not able to get back errors when there are some issues with Stored Procedure execution. For example even when there is no such Stored Procedure present in database, the job terminates fine without even logging a warning. Please suggest what changes should be done to get back errors into the subroutine and abort the job in case of errors.

DS subroutine code:

Code: Select all

inputStrCount = DCOUNT(input_string,",")
Dimension A(inputStrCount )

FOR X = 1 TO inputStrCount
   inputToken = FIELD(input_string,",",X )
   A(X) = trim(inputToken) 
   Call DSLogInfo ("InputArg " :A(X),'');
NEXT X

Call DSExecute('DOS','SQLPLUS' : ' ' : A(2) : '/' : A(3) : '@' : A(1) : ' @' : A(4),Output, SystemReturnCode );
ErrorCode = SystemReturnCode;

The sql file being called(file path specified in one of the parameters) contains:

Code: Select all

CALL CDB_SELF_PREF('Y'); 
UPDATE STG_MIGRATION_STATUS SET JOB_STATUS = 'Completed' where TABLE_NAME = 'XYZ';
COMMIT;
EXIT;

[chulett]

You need a script to call sqlplus, one that can check for errors and pass them back. All you are getting now is the exit code of sqlplus and would only fail if it couldn't find sqlplus itself.

[psluser]

Thanks for the reply Craig.

You need a script to call sqlplus, one that can check for errors and pass them back.

Can you point me to where to find it and how to go about it.

[chulett]

Your DBAs would be the best resource for something like that. Or try searching the forums for 'sqlplus' to see if one has been posted. Maybe even Google would turn something up.

[chulett]

Here's a snippet from one of mine, should get you started. You'll need to flesh it out but this is the meat of it:

Code: Select all

    # Execute via Sql*Plus capturing output
    $ORACLE_HOME/bin/sqlplus <user>/<pwd>@<instance> >/tmp/check$$.out <<!!
    @<sql_file>
    quit;
!!
    # Grep for errors and set status to Good if none are found
    IsGood=-1
    eCount=`grep -E 'SP2-|ORA-' /tmp/check$$.out |wc -l`
    # Cat out the results so that they will appear in the job's log.
    cat /tmp/check$$.out
    rm /tmp/check$$.out
    if [ $eCount -eq 0 ] ;then
      echo ""
      echo "SQL File executed without errors..."
      IsGood=0
    fi
    STATUS=$IsGood

I pass in <user>, <pwd> etc as arguments to the script so they are $1, $2, etc inside the script but you can handle things as you see fit. The key is to echo out a 'transcript' of what happens inside the sqlplus session somewhere else and then check what happened to see if Something Went Wrong.

This only passes back either a 0 or a -1 for success or failure ("exit $STATUS"), you could certainly expand that if you need more detail when things fail.