In the shell script, I am calling a PLSQL stored procedure
sqlplus -s <
But when there is an error in the plsql block, I need to error out shell script which is running as a host concurrent program in oracle but it is not doing so.
I am new to the shell script and any help is really appreciated.
How about using WHENEVER SQLERROR EXIT N in your SQL script? This returns N to your shell script.
Documentation here.
Unfortunately, there is no simple, easy way to do this. SQLPLUS mostly returns success (0) regardless of the status of the underlying PL/SQL commands. Even using the 'WHENEVER SQLERROR EXIT' does not pass that error numerical status back up to the calling shell.
SQLPLUS returns 0 because it was able to successfully execute the command.
However, I have found a way around this, but you need to create a log file from your sql session and then grep out the error.
sqlplus -s /nolog <<EOD >> ${sql_local_output_file}
....
PROCEDURE error( p_msg VARCHAR2 ) IS
BEGIN
DECLARE
error_date VARCHAR2(20);
BEGIN
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO error_date FROM DUAL;
dbms_output.put_line( '[' || error_date || ']: ERROR ' || p_msg );
END;
END error;
....
EOD
if (grep -q -e "ERROR" ${sql_local_output_file}); then
logInfo "Error found in sql script, ( ${sh_script} )."
fi
