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.<\/p>\n
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.<\/p>\n
\r\nSQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2)\r\n 2 AS\r\n 3 l_var VARCHAR2(1);\r\n 4 BEGIN\r\n 5 dbms_output.put_line('***START***');\r\n 6 l_var := 'abc';\r\n 7 exception\r\n 8 WHEN others THEN\r\n 9 dbms_output.put_line('***Exception***');\r\n 10 raise;\r\n 11 END sp_test;\r\n 12 \/\r\n \r\nProcedure created.\r\n \r\nSQL> exec sp_test(p_var => 'abc');\r\n***START***\r\n***Exception***\r\nBEGIN sp_test(p_var => 'abc'); END;\r\n \r\n*\r\nERROR at line 1:\r\nORA-06502: PL\/SQL: numeric or value error: character string buffer too small\r\nORA-06512: at \"ODTUG.SP_TEST\", line 10\r\nORA-06512: at line 1\r\n<\/pre>\n