Handling User defined exceptions in SQL*PLUS 1

[nbateman]

Hi.

I have a procedure that throws a user defined exception that I want to handle in SQL*PLUS where the procedure is called. When the exception is thrown, I want a user message to the console and for SQL*PLUS to exit with a non-zero return code.

Currently getting "unhandled user-defined exception".

Can this be done? What am I doing wrong? Is there a better approach?

Thanks in advance, Nick.

=====================================

SQL> CREATE OR REPLACE PROCEDURE test1 (xxx IN NUMBER) IS
2 ex1 EXCEPTION;
3
4 BEGIN
5 IF xxx = 1 THEN
6 RAISE ex1;
7 END IF;
8
9 EXCEPTION
10 WHEN ex1 THEN
11 dbms_output.put_line('XXX=1');
12 RAISE;
13
14 END test1;
15 /

Procedure created.

SQL>
SQL>
SQL> whenever sqlerror exit failure
SQL> Declare
2 ex1 exception;
3 begin
4 test1(1);
5 exception
6 when ex1 then
7 RAISE;
8 end;
9 /
Declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "MMN.TEST1", line 12
ORA-06512: at line 4

 

[Dagon]

What exactly are you achieving with

exception
when ex1 then
RAISE;

that wouldn't be achieved simply by allowing the procedure to raise its own exception?

For Oracle-related work, contact me through Linked-In.

 

[nbateman]

Dagon, thanks for reply.

Specifically, I was trying to remove the "ORA-06510: PL/SQL: unhandled user-defined exception" that it says is "ORA-06512: at line 4".

This was just my latest attempt; the following simpler alternative has a similar problem:
=============================================
SQL> exec test1(1)
BEGIN test1(1); END;
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "MMN.TEST1", line 12
ORA-06512: at line 1
SQL>
=============================================

I think my requirement is simple enough: "When the exception is thrown, I want a user message to the console and for SQL*PLUS to exit with a non-zero return code."

I'm sure I must be missing something basic either in concept or detail.

Thanks again,

Nick.

 

[Dagon]

The line number is useful information and you ought to try to retain it somewhere. When the job is failing in the live environment and want to know which line out of 200 lines in the procedure is giving the error, you'll be glad you did.

SQL*Plus isn't a sophisticated development tool, so you may have to do something quite convoluted to achieve exactly what you want. A possible approach would be something like this:

accept input prompt 'What value to run?'

variable job_failed number

exec :job_failed := 0

set serveroutput on
begin
  if &input = 1 then
    raise_application_error(-20500, 'xxxx');
  else
    null;
  end if;
exception when others then
  rollback;
  insert into errors_table (text_string) values (dbms_utility.format_error_stack||' - '||dbms_utility.format_error_backtrace);
  dbms_Output.put_line('The procedure failed with an error');
  :job_failed := 1;
end;
/

set termout off
set feedback off
set heading off
set verify off
spool fail_job.sql

select decode(:job_failed, 1, 'exit 1', null)
from dual
/

spool off

set termout on

@@fail_job

For Oracle-related work, contact me through Linked-In.

 

[nbateman]

Dagan,

Yes, that is quite convoluted, which I think answers my question "Can this be done?" with "not nicely".

It does work though. Response appreciated. Star.

Nick.

 

[nbateman]

I meant to add that if I change the PROCEDURE into a FUNCTION that returns an error indicator...

CREATE OR REPLACE FUNCTION test1 (xxx IN NUMBER) RETURN NUMBER IS
ex1 EXCEPTION;
BEGIN
IF xxx = 1 THEN
RAISE ex1;
END IF;

RETURN 0;

EXCEPTION
WHEN ex1 THEN
dbms_output.put_line('XXX=1');
RETURN 1;
END test1;
/

... this helps in two ways:

1. it returns an indication I can use to drive the creation of the spool file

2. It means the User Exception is now "handled" and resolves the "ORA-06510: PL/SQL: unhandled user-defined exception". Basically, you can't just keep on re-raising a user defined exception, you have to do something with it. The Return statement is that something.

Nick.

 

[nbateman]

And finally, what I think is a neater adaptation of the same approach, which requires two files to be set up in advance:
1. exit.sql containing the single line "exit"
2. continue.sql which will be empty


I can then continue (when it returns 0) or exit (when it returns 1) using:

   COLUMN action NEW_VALUE action noprint
   SELECT decode(test1(1), 0, 'continue', 'exit') AS action FROM dual;
   @&&action

Personally, I'm quite happy with this as a solution. An SQLPLUS "if" statement would be better, but this is neat enough.


Hope this thread is as useful to others as it has been to me.

Nick.