The mystery of the disappearing error message

I was running into a few problems with a bit of code yesterday. And the observations just go to show how important it is to try to distill your problem to the smallest possible reproducible routine, getting rid of the noise and distration of the context itself.

I was having two different error messages. It was strange but the first time my session called a procedure, I got one message and then any subsequent calls got a different message from elsewhere. My investigations wrongly focused on the subsequent error.

Anyway, that’s all by the by.

Here is the problem, issue, feature, call it what you will in distilled form:


CREATE OR REPLACE PACKAGE buffer_size_test
AS
--
PROCEDURE p_do_test;
--
END buffer_size_test;
/



CREATE OR REPLACE PACKAGE BODY buffer_size_test
AS
--
g_just_right1 CONSTANT VARCHAR2(10) := 'JUST_RIGHT';
g_too_small CONSTANT VARCHAR2(1) := 'TOO_SMALL';
g_just_right2 CONSTANT VARCHAR2(10) := 'JUST_RIGHT';
--
PROCEDURE p_do_test
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Something');
DBMS_OUTPUT.PUT_LINE('g_just_right1='||g_just_right1);
DBMS_OUTPUT.PUT_LINE('g_just_right2='||g_just_right2);
END;
--
END buffer_size_test;
/



SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jul 6 09:04:15 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production

SQL> set serveroutput on
SQL> exec buffer_size_test.p_do_test
BEGIN buffer_size_test.p_do_test; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DIGITAL_SERVICES.BUFFER_SIZE_TEST", line 5
ORA-06512: at line 1

SQL> exec buffer_size_test.p_do_test
Something
g_just_right1=JUST_RIGHT
g_just_right2=

PL/SQL procedure successfully completed.

SQL>

So, the package is validly compiled in 9i. The first time my session runs my package, it identifies that the buffer size of my g_too_small global variable is too small.  But any subsequent calls are fine. Any of the global variables declared after the wrongly sized are initialised as null. Slightly unexpected behaviour, unexpected by me at least. I’ve not got a later version of Oracle available right now but I’ve a sneaky suspicion that the compiler might pick this up in 10, or if not maybe 11.

About these ads