RETURN statement inside a Procedure

Can we have a RETURN statement inside a Procedure?

Yes, as long as RETURN does not return any values

In procedures, a RETURN statement cannot return a value, and therefore cannot contain an expression. The statement simply returns control to the caller before the normal end of the procedure is reached.

Exception Handling - II

RAISE / FORMAT_ERROR_STACK / FORMAT_ERROR_BACKTRACE

RAISE will get you the right error message only if that was the last program executed; assume a scenario where the RAISE is placed in a subprogram which does not handle exception: you will lose the error message, FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE will help there

Sample

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200)); 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

in p2 after Calling P1

in p3 b4 Calling P2

When the control comes to P3 we do not know what error happened, Adding a RAISE statement will help.

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    RAISE;

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);   

 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

ORA-01403: no data found

From P3:ERROR100 ORA-01403: no data found

ORA-01403: no data found

RAISE throws the error. Now FORMAT_ERROR_STACK does not give much information more than SQLERRM, but FORMAT_ERROR_BACKTRACE will give you more information than ERROR_STACK, the output will be similar to the output of the SQLERRM function.

 

CREATE OR REPLACE PROCEDURE p1 IS

BEGIN

  dbms_output.put_line('in p1');

  RAISE no_data_found;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From p1:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    RAISE;

END;

CREATE OR REPLACE PROCEDURE p2 IS

BEGIN

  dbms_output.put_line('in p2 b4 Calling P1');

  p1;

  dbms_output.put_line('in p2 after Calling P1');

END;

CREATE OR REPLACE PROCEDURE p3 IS

BEGIN

  dbms_output.put_line('in p3 b4 Calling P2');

  p2;

  dbms_output.put_line('in p3 b4 Calling P2');

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('From P3:' || 'ERROR' || SQLCODE || ' ' ||

                         substr(SQLERRM, 1, 200));

    dbms_output.put_line(dbms_utility.format_error_stack);

    dbms_output.put_line(dbms_utility.format_error_backtrace);

 

END;

Output

in p3 b4 Calling P2

in p2 b4 Calling P1

in p1

From p1:ERROR100 ORA-01403: no data found

ORA-01403: no data found

From P3:ERROR100 ORA-01403: no data found

ORA-01403: no data found

ORA-06512: at "SCOTT.P1", line 10

ORA-06512: at "SCOTT.P2", line 4

ORA-06512: at "SCOTT.P3", line 4

 

Exception Handling

How do you throw the exception after logging in the error?

Do a raise after logging the exception

CREATE OR REPLACE PROCEDURE sp_test IS

  ln NUMBER;

BEGIN

  ln := 5 / 0;

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('Error');

    RAISE;

END;

Output would be

Error

And “ORA-01476: divisor is equal to zero” Will be raised

Function inside Procedure

Can we have a function inside procedure?

 

 Sure we can

CREATE OR REPLACE PROCEDURE sp_test (ID IN number)

AS

FUNCTION fn_test (Id IN number)

  RETURN varchar 2

IS

DECLARE eName varchar2(50);

BEGIN

  SELECT Emp_Name INTO eName

    FROM emp WHERE EmpId = Id;

  RETURN oID;

EXCEPTION

  WHEN no_data_found THEN

    eName:= NULL;

    RETURN eName;

END;

BEGIN

  IF(fn_test (ID) IS NULL) THEN

    RAISE_APPLICATION_ERROR(-20001,'EmpId is not valid');

  END IF;

END; 

/

The only disadvantage with this, you will not be able to use this function from any other packages or procedures