CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle11gexception
Alexandru Antochi
Alexandru Antochi

Reputation: 1465

Handle an exception from a Function that is used in another Procedure in Oracle SQL Developer

I have this function:

CREATE OR REPLACE FUNCTION TEST_FUNCTION (p_test IN NUMBER) RETURN NUMBER
AS
    my_exception EXCEPTION;
    PRAGMA EXCEPTION_INIT(my_exception, -20001);
BEGIN
    IF (p_test = 0) THEN
         RAISE my_exception;
    ELSE
        RETURN 1;
    END IF;
EXCEPTION
    WHEN my_exception THEN
        raise_application_error(-20001, 'p_test = 1');
        RETURN 0;
END;

There is a procedure that uses my function. The problem is that when my function raises the error 20001, the procedure can not handle the exception; when I try to compile it, I get PLS-00201: identifier my_exception must be declared.

If I run the procedure without treating this exception, it will still throw out the exception and error code, halting its execution. How can I treat my_exception from that function?

Upvotes: 0

Views: 3871

Answers (2)

Belayer
Belayer

Reputation: 14861

What your current code is doing: 1. A procedure calls TEST_FUNCTION. 2. TEST_FUNCTION raises my_exception. 3. TEST_FUNCTION traps the my_exception and replaces it with -20001 4. The calling procedure receives user defined exception -20001.

As I understand what your asking is: 1. A procedure calls TEST_FUNCTION. 2. TEST_FUNCTION raises user defined exception (my_exception) -20001. 3. The calling procedure should handle error.

To have the calling procedure handle you need to move declaration of my_exception and he pragma to the that procedure, remove exception block from TEST_FUNCTION and just raise he user defined error directly in the TEST_FUNCTION. Then in the calling procedure handle my_exception.

SO: 

create or replace 
function test_function (p_test in number) 
  return number
as
begin
    if (p_test = 0) then
         raise_application_error(-20001,'p_test = 0'); 
    end if;
    return 1;
end test_function;

create or replace
procedure caller_for_test_function
is 
    my_exception EXCEPTION;
    PRAGMA EXCEPTION_INIT(my_exception, -20001);

    x number;
begin
    ...
    x := text_function(0);
    ...
exception
   when my_exception then
      <Handle my_exception here>;

end caller_for_test_function;

There are of course many other valid ways of laying out the code, but the main idea is that the exception declaration and the Pragma directive need to be in procedure/function that handles the error, NOT the one that raises the error.

Upvotes: 1

APC
APC

Reputation: 146239

You have declared my_exception in the private namespace of your function. That means it is hidden from any other program unit.

This is one advantage of packages: anything we declare in a package spec is available to any other program in our schema (or other users if we have granted them execute privilege.

So you could do this: 

CREATE OR REPLACE PACKAGE app_exceptions
AS
    my_exception EXCEPTION;
    PRAGMA EXCEPTION_INIT(my_exception, -20001);
end;
/

Now your function would look like this:

CREATE OR REPLACE FUNCTION TEST_FUNCTION (p_test IN NUMBER) RETURN NUMBER
AS
BEGIN
    IF (p_test = 0) THEN
        RAISE app_exceptions.my_exception;
    ELSE
        RETURN 1;
    END IF;
END;

If we want the calling program to handle the exception (and usually we do) it's fine to just propagate it.

Your calling procedure will look something like this:

    ....
    l_n := TEST_FUNCTION ( l_whatever );
exception
    when app_exceptions.my_exception then
         -- handling code here

Upvotes: 3

Related Questions