Re: not a pretty sight - PL/SQL global private elements

Wayne Stewart wrote:
>
> Hidee again, folks,
>
> We have a buggy problem today, pertaining to private global package
> elements in PL/SQL. It looks as though we've run into a bug with
> the persistence (or lack of same) for those supposedly "global"
> elements:
>
> Seems as though they pop into and out of existence according to
> hermeneutic rules not found in the textbooks.
>
> For those elite and unfortunate few of you who might know about such
> PL/SQL arcana, I append sections of the code with explanations below.
>
> I'd appreciate any comments; and I apologize for the length of
> this bothersome document.
>

[Long code sample removed]

Hi,

could it be that you run into the following bug, I've logged with Oracle Support:

Platform: Windows NT 3.51, NT 4.0. Intel. RDBMS: 7.3.2.2
PL/SQL: 2.3.2.2 Problem

Record members are not initialized when used in PL/SQL tables (new 2.3 feature).

Reproduction

Execute the following in SQL*Plus:

set serveroutput on

declare

• Record, unitialized members. type rec1 is record ( x number, y number ); r1 rec1;
• Record, initialized members. type rec2 is record ( x number := 0, y number := 0 ); r2 rec2;
• Table of records with uninit. members. type rec1_table is table of rec1 index by binary_integer; rt1 rec1_table;
• Table of records with init. members. type rec2_table is table of rec2 index by binary_integer; rt2 rec2_table;

begin

• All 4 OK. dbms_output.put_line('r1.x = ' || to_char(r1.x)); dbms_output.put_line('r1.y = ' || to_char(r1.y)); dbms_output.put_line('r2.x = ' || to_char(r2.x)); dbms_output.put_line('r2.y = ' || to_char(r2.y));

   rt1(1).x := 1;

• rt1(1).y should now be null. dbms_output.put_line('rt1(1).x = ' || to_char(rt1(1).x)); dbms_output.put_line('rt1(1).y = ' || to_char(rt1(1).y));

   rt2(1).x := 1;

• rt2(1).y should now be 0. dbms_output.put_line('rt2(1).x = ' || to_char(rt2(1).x)); dbms_output.put_line('rt2(1).y = ' || to_char(rt2(1).y)); end; /

The following output will be generated:

r1.x =
r1.y =
r2.x = 0
r2.y = 0
rt1(1).x = 1
rt1(1).y = ;00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E-65

rt2(1).y = ;00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E-65

Conclusion

Members of stand-alone records are initialized correctly to either supplied value or null. Members of records are not initialized when a member of a table cell record is set to a value. Expected behaviour would be to initialize the rest of the members to the supplied constant from the record definition (if any, else null).

Hope this helps.

Cheers,

Finn

-- 
--------------------------------------------------------------------------------
 Finn Ellebaek Nielsen                      Oracle Associated Senior Consultant
 E-mail:         ellebuk_at_vip.cybercity.dk   Ellebaek Consulting
 Mob. Phone:     +45 20 32 49 25            Niels Ebbesens Vej 9, 3. th.
 Mob. Phone SMS: 20324925_at_sms.tdm.dk        DK-1911  Frederiksberg C
 Private Phone:  +45 33 25 34 50            Denmark
--------------------------------------------------------------------------------
 "Life is a beach and then you dive"                      "Divers do it deeper"