| Suppressing output [message #352806] |
Thu, 09 October 2008 12:53  |
podzach
Messages: 6
Registered: October 2008
Location: Sioux Falls, SD
|
Junior Member |
|
|
I have a database procedure that will sometimes produce and error. Sometimes I want to see this error, and other times I want to suppress this error. One idea I had was to pass in a parameter to the procedure and if the parameter is true then in the exception handling, just put null. This seems a bit clunky though. This is how I call sqlplus from a UNIX script
sqlplus -s username/password <<eof
whenever SQLERROR exit 2 rollback
whenever OSERROR exit 3 rollback
set serveroutput off
set termout off
set term off
set echo off
set show off
set feedback off
set heading off
set VERIFY off
exec database_utils.test_error;
eof
The procedure is
PROCEDURE test_error IS
BEGIN
raise NO_DATA_FOUND;
END;
Is there an elegant way for this to happen? I am using Oracle 10g and AIX for the shell scripting.
[Updated on: Thu, 09 October 2008 12:58] Report message to a moderator |
|
|
|
|
|
| Re: Suppressing output [message #352834 is a reply to message #352809] |
Thu, 09 October 2008 18:32  |
podzach
Messages: 6
Registered: October 2008
Location: Sioux Falls, SD
|
Junior Member |
|
|
Thank you for the quick reply. Unfortunately set termout off does not work. When executing the shell, it still puts out
BEGIN database_utils.test_error; END;
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "DATABASE_UTILS", line 9
ORA-01403: no data found
ORA-06512: at line 1
I know that this procedure is going to error and I want to ignore that error.
|
|
|
|
|
|
| Re: Suppressing output [message #353034 is a reply to message #352864] |
Fri, 10 October 2008 12:25 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I'm not sure I properly understand your requirement, but here's a way to enable/disable pl/sql exception handler.
abc.ksh
-------
#!/bin/ksh
# exception handler gets commented out
OPT=--
sqlplus -s scott/tiger@dev <<EOF
whenever sqlerror exit 2
declare
v1 varchar2(1);
begin
select 'X' into v1 from dual where 1=0;
${OPT}EXCEPTION
${OPT} when no_data_found then null;
end;
/
EOF
echo Return code is $?
# exception handler gets used
OPT=
sqlplus -s scott/tiger@dev <<EOF
whenever sqlerror exit 2
declare
v1 varchar2(1);
begin
select 'X' into v1 from dual where 1=0;
${OPT}EXCEPTION
${OPT} when no_data_found then null;
end;
/
EOF
echo Return code is $?
my_host>abc.ksh
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
Return code is 2
PL/SQL procedure successfully completed.
Return code is 0
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
