Home » Infrastructure » Unix » Suppressing output (Oracle 10g)
Suppressing output [message #352806] Thu, 09 October 2008 12:53 Go to next message
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 #352809 is a reply to message #352806] Thu, 09 October 2008 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sqlplus -s username/password <<eof
    whenever SQLERROR exit 2 rollback
    whenever OSERROR exit 3 rollback
    set serveroutput off
    set termout $TERMOUT
    set term off
    set echo off
    set show off
    set feedback off
    set heading off
    set VERIFY off
    exec database_utils.test_error;
  eof

Then set $TERMOUT variable to on or off depending of what you want.

Regards
Michel
Re: Suppressing output [message #352834 is a reply to message #352809] Thu, 09 October 2008 18:32 Go to previous messageGo to next message
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 #352864 is a reply to message #352834] Thu, 09 October 2008 23:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, termout does not imply for interactive commands just for commands executed from a SQL script file. So if you want to make it work you have to put you "exec" statement inside a SQL script file and call it.

Regards
Michel
Re: Suppressing output [message #353034 is a reply to message #352864] Fri, 10 October 2008 12:25 Go to previous messageGo to next message
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
Re: Suppressing output [message #354131 is a reply to message #353034] Thu, 16 October 2008 08:59 Go to previous message
podzach
Messages: 6
Registered: October 2008
Location: Sioux Falls, SD
Junior Member
That got it. Thanks Andrew again
Previous Topic: Error when running runInstaller
Next Topic: delete last character from every line in a text file
Goto Forum:
  


Current Time: Thu Mar 28 07:30:39 CDT 2024