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

One Response to The mystery of the disappearing error message

  1. Karl says:

    H,
    run same code on 10.2;

    SQL> set serveroutput on
    SQL> select * from v$version;

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bi
    PL/SQL Release 10.2.0.2.0 – Production
    CORE 10.2.0.2.0 Production
    TNS for Linux: Version 10.2.0.2.0 – Production
    NLSRTL Version 10.2.0.2.0 – Production

    SQL> exec buffer_size_test.p_do_test

    begin buffer_size_test.p_do_test; end;

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at “ISIS.BUFFER_SIZE_TEST”, line 5
    ORA-06512: at line 2

    SQL> exec buffer_size_test.p_do_test

    Something
    g_just_right1=JUST_RIGHT
    g_just_right2=

    PL/SQL procedure successfully completed

    SQL>

    Same behavior : on the first call the package is loaded and the initialization of the globals is done and causes the error. but not on second call – globals then are not touched anymore;

    that’ts not consequent if the initialisation was not successful ;-)

    Karl

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 69 other followers

%d bloggers like this: