Hello, Tom.

I am invoking PL/SQL from within a shell function in a UNIX Korn shell script.

This PL/SQL returns a VARCHAR2 which I in turn use later in the shell script.

If the PL/SQL raises an error, I would like to exit the shell script entirely (from within the shell function), and not just exit the shell function.

Is it possible? FYI, I'm on version 8.1.7.4.

CREATE OR REPLACE PACKAGE pkg
AS
FUNCTION normal_call RETURN VARCHAR2;
FUNCTION error_call RETURN VARCHAR2;
END pkg;
/
CREATE OR REPLACE PACKAGE BODY pkg
AS
FUNCTION normal_call RETURN VARCHAR2 IS
BEGIN
-- Return a random, spelled-out number between one and ten
RETURN (TO_CHAR(TO_DATE(TRUNC(DBMS_RANDOM.VALUE(1,11)),'J'),'jsp'));
END normal_call;

FUNCTION error_call RETURN VARCHAR2 IS
e1 EXCEPTION;
BEGIN
RAISE e1;
RETURN NULL;
EXCEPTION
WHEN e1 THEN
RAISE_APPLICATION_ERROR (-20566
, 'Art''s error has been raised');
END error_call;
END pkg;
/

#!/bin/ksh
# Name = shelfun1.ksh
get_plsql_result()
{
plsql_result=`sqlplus -s scott/tiger <<!
SET SERVEROUTPUT ON FORMAT WRAPPED
SET PAGESIZE 0
SET FEEDBACK OFF
VARIABLE outtext VARCHAR2(250)
BEGIN
:outtext := SUBSTR(scott.pkg.$1,1,250);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
/
PRINT :outtext
EXIT
!`
echo $plsql_result
}
echo "$1"
myvar=`get_plsql_result "$1"`
echo "myvar = "$myvar
echo "here I am after the function call"
exit 0

$ shelfun1.ksh normal_call
normal_call
myvar = eight
here I am after the function call
$ shelfun1.ksh error_call
error_call
myvar = BEGIN ajmadhoc.sql abal_00.txt abal_01.txt abal_02.txt abal_03.txt addrchk.sql addrconv1.sql addrconv2.sql
here I am after the function call
$

Oddly, when the error is raised, get_plsql_result returns "BEGIN" plus the results of an "ls" on my home directory....?

So next I tried having the PL/SQL return a string regardless of error status, and then exiting the shell script if this SQLERRM string matched "ORA-nnnnn:". But this approach isn't exiting the shell script, it only exits the shell function and continues on in the shell script.

#!/bin/ksh
# Name = shelfun2.sh
get_plsql_result()
{
plsql_result=`sqlplus -s scott/tiger <<!
SET SERVEROUTPUT ON FORMAT WRAPPED
SET PAGESIZE 0
SET FEEDBACK OFF
VARIABLE outtext VARCHAR2(250)
BEGIN
:outtext := SUBSTR(scott.pkg.$1,1,250);
EXCEPTION
WHEN OTHERS THEN
:outtext := SUBSTR(SQLERRM,1,250);
END;
/
PRINT :outtext
EXIT
!`
if [ `echo "$plsql_result" | grep '^ORA-[0-9]\{5\}:' | wc -l` -eq 1 ]
then
echo "$plsql_result"
exit 1
echo "after the error exit"
else
echo "$plsql_result"
echo "after the normal exit"
fi
}
echo "$1"
myvar=`get_plsql_result "$1"`
echo "myvar = "$myvar
echo "here I am after the function call"
exit 0

$ shelfun2.sh normal_call
normal_call
myvar = two after the normal exit
here I am after the function call
$ shelfun2.sh error_call
error_call
myvar = ORA-20566: Art's error has been raised
here I am after the function call
$

As a workaround, I could perform the regular-expression check for "ORA-nnnnn:" EVERY time AFTER this function is called, but I would really like to encapsulate the "exit-the-shell-script-completely" behavior within the shell function itself, if at all possible.

Can it be done?

Thanks, Tom.

Art.