| Transforming an embedded PL/SQL exception to exit from shell script [message #98176] |
Wed, 21 April 2004 13:02  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
I want to call a little PL/SQL repeatedly within a Korn shell script, so I set up a shell-script function.
This shell-script function, get_plsql_result, in turn calls a PL/SQL function (its argument, $1) and returns the PL/SQL function's output to the shell-script function's output.
The problem: If the PL/SQL function raises an exception, what I want to happen is for the error message to be written out to the screen (echoed), and completely exit the shell script.
But the following code is treating the error message as the output from the function, which I don't want to happen!
#!/bin/ksh
get_plsql_result ()
{
plsql_result=`sqlplus -s scott/tiger <<!
SET SERVEROUTPUT ON
SET HEADING OFF
SET TRIMSPOOL ON
SET PAGESIZE 0
SET FEEDBACK OFF
SET DEFINE OFF
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
DBMS_OUTPUT.PUT_LINE(SUBSTR($1,1,250));
EXCEPTION
-- If there's an error, write it out.
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/
!`
error_indicator=`echo "$plsql_result" | cut -f1 -d':' | grep '^ORA-[[0-9]]{5}:' | wc -l`
# Normal condition
if [[ "$error_indicator" -eq 0 ]]
then
echo "$plsql_result"
# Error condition
elif [[ "$error_indicator" -eq 1 ]]
then
echo "$plsql_result"
exit 1
fi
}
I really want to differentiate between an echo that means "return this value from this function" and "write out this string to the screen".
How can I translate the embedded PL/SQL exception to a fatal error in the shell script?
Thanks,
Art.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
