It’s always good to know how any language handles and propagates exceptions, Oracle PL/SQL being no different. They’re plenty of examples online about raising and handling exceptions on the web, but one thing you may not have realized is how PL/SQL propagates exceptions that occur in the variable declaration section of a procedure.
In the first example I created a procedure that has a variable, l_var, which can handle one character. As expected, when I assign more then one character an exception is raised and is propagated to the EXCEPTION block of the procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2) 2 AS 3 l_var VARCHAR2(1); 4 BEGIN 5 dbms_output.put_line('***START***'); 6 l_var := 'abc'; 7 exception 8 WHEN others THEN 9 dbms_output.put_line('***Exception***'); 10 raise; 11 END sp_test; 12 / Procedure created. SQL> exec sp_test(p_var => 'abc'); ***START*** ***Exception*** BEGIN sp_test(p_var => 'abc'); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "ODTUG.SP_TEST", line 10 ORA-06512: at line 1 |
Continue reading on my blog for the next example…
Load comments