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 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | SQL> CREATE OR REPLACE PROCEDURE sp_test(p_var in varchar2) 2 AS 3 l_var VARCHAR2(1) := p_var; 4 BEGIN 5 dbms_output.put_line( '***START***' ); 6 exception 7 WHEN others THEN 8 dbms_output.put_line( '***Exception***' ); 9 raise; 10 END sp_test; 11 / Procedure created. SQL> exec sp_test(p_var => 'abc' ); 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 3 ORA-06512: at line 1 |
For documentation of how PL/SQL propagates exceptions raised in declarations go here. If you haven't already done so, I'd recommend reading the entire PL/SQL Error Handling documentation.