not a pretty sight - PL/SQL global private elements

[Quoted] Hidee again, folks,

[Quoted] 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.

> "If I'd had more time, I would have written you a shorter letter."

>                                                            -- Pascal

Thank ye,

--

Wayne Stewart
MCI - Campbell Creek

tel:    972-918-7009
vnet:   777-7009
fax:    972-918-6097

--

My package has a private global record, EQUIP_REC. EQUIP_REC contains 7 arrays of equipment information.

So EQUIP_REC(1) is the first record, storing 7 pieces of information about that first equipment.

EQUIP_REC(2) is the second record.

EQUIP_REC(3) is the third record.

And so on.

Now 3 separate procedures in this package need to use EQUIP_REC. For this reason it is a private global record. Its record_type declaration, EQUIPMENT_RECORDTYPE, is in the package specification. And its instance, EQUIP_REC, is declared in the package body before the first procedure. The declarations look like this:

CREATE or REPLACE package ENTER_EVENT
IS

< 7 array type declarations go here... >

• RECORD type for storing equipment entries before final submission

        type    equipment_recordtype
                is record
                (
                site    site_array_tabletype,
                equip   equip_array_tabletype,
                lalpha  low_alpha_array_tabletype,
                halpha  high_alpha_array_tabletype,
                mux     mux_array_tabletype,
                lslot   low_slot_array_tabletype,
                hslot   high_slot_array_tabletype
                );

< After this specification section, I create a private instance of the record_type in the package body.... >

CREATE or REPLACE package body ENTER_EVENT IS

• Global variables
  • record of arrays of eqpt entered via procedure 'enter_4'

        equip_rec equipment_recordtype;

< Then the first procedure goes here... >

3 procedures call this EQUIP_REC private global record, and I list relevant sections from all 3 below. The 3 procedures are:

        ENTER_4         SUBMIT_4         and

        ENTER_5. ENTER_4 prints out the number of the LAST EQUIP_REC array element populated, as a visual debug.

SUBMIT_4 populates EQUIP_REC record elements with field variables.

         Afterwards, if the user has pressed a 'More' submit button,     SUBMIT_4 calls ENTER_4 again so that the user can enter more records.

         This loop continues until the user has entered all the records into the

    record array. Then the user presses 'Complete', and SUBMIT_4 moves on to

    the ENTER_5 procedure.

ENTER_5 just prints all the EQUIP_REC records on screen.

--

As you can see from the logic above, there are 2 paths of action the user can take:

--

PATH ONE The user can:

        enter one list of equipment in ENTER_4,
        press 'Complete',
        and view the result in ENTER_5.

PATH TWO The user can:

        enter one list of equipment in ENTER_4,
        press 'More',
        enter more lists of equipment in ENTER_4 ( repeatedly looping
back to
              it with 'More' button ),
        press 'Complete',
        and view the result in ENTER_5.

--

HERE'S WHERE THE MOUSETRAP GAME MALFUNCTIONS. %-)

If the user follows PATH ONE everything works OK. All 3 procedures see EQUIP_REC, and the user can review his list of equipment in ENTER_5.

However, if the user follows PATH TWO, not everything works OK. ENTER_4 and SUBMIT_4 work OK, but ENTER_5 does NOT. ENTER_5 does NOT see EQUIP_REC. It skips the EQUIP_REC print loop, and prints out the default dummy debug text, namely:

> 'I HAVE COMPLETELY FORGOTTEN ABOUT EQUIP_REC!'
--

It seems like a PL/SQL bug to me ( v2.3 on NT Oracle RDBMS 7.3.2.2.0 ).

But I may have done something wrong myself. It's happened before. :-)

Here are the important sections of each of the 3 procedures mentioned previously. Thanks for taking time to review the code: I think it's darn tedious, myself. But maybe a guru can see my mistake just by skimming through it.

PROCEDURE enter_4 ( < some input parameters... > )

< Declare some variables... >

BEGIN < Print some stuff... >

        htp.p ( 'last element in equip_rec is ' || equip_rec.equip.last );

PROCEDURE submit_4 ( < some input parameters... > )

< Declare some variables... >

BEGIN < Print some stuff... >

• Enter fields into equip_rec, whether submit='More' or submit='Complete'

        IF (    t_stn1 IS NOT NULL
                AND
                t_equip1 IS NOT NULL
                AND
                t_lalpha1 IS NOT NULL
                )     -- we have enough info for a properly-formed
equipment record
        THEN
                i := i + 1;          -- next record array element
                equip_rec.site(i) := t_stn1;
                equip_rec.equip(i) := t_equip1;
                equip_rec.lalpha(i) := t_lalpha1;
                equip_rec.halpha(i) := t_halpha1;
                equip_rec.mux(i) := t_mux1;
                equip_rec.lslot(i) := t_lslot1;
                equip_rec.hslot(i) := t_hslot1;
        END IF;

        IF (    t_stn2 IS NOT NULL
                        AND
                        t_equip2 IS NOT NULL
                        AND
                        t_lalpha2 IS NOT NULL
                ) -- we have enough info for a properly-formed equipment
record
        THEN
                i := i + 1;  -- next record array element
                equip_rec.site(i) := t_stn2;
                equip_rec.equip(i) := t_equip2;
                equip_rec.lalpha(i) := t_lalpha2;
                equip_rec.halpha(i) := t_halpha2;
                equip_rec.mux(i) := t_mux2;
                equip_rec.lslot(i) := t_lslot2;
                equip_rec.hslot(i) := t_hslot2;
        END IF;

        < etc... More elements get populated... >



--      If user requests more equipment, loop back to ENTER_4 again.


--      If user states that the list of equipment is complete, move on


to


--      ENTER_5 procedure.



        IF submit = 'More'
        THEN
                enter_event.enter_4 ( < some parameters > );

        ELSIF submit = 'Complete'
        THEN
                enter_event.enter_5 ( < some parameters > );

        END IF;



----------------------------------------------------------------


----------------------------------------------------------------

PROCEDURE enter_5 ( < some parameters > )

< Declare some variables... >

BEGIN < Print some stuff... >

• Display all equipments specified in equip_rec.

        IF equip_rec.equip.last > 0    -- we have 1 or more records
        THEN

                htp.tableOpen ( '0', 'LEFT' );

                FOR row IN 1 .. enter_event.equip_rec.equip.last
                LOOP
                        htp.tableRowOpen;
                        htp.tableData ( enter_event.equip_rec.site(row)
);
                        htp.tableData ( enter_event.equip_rec.equip(row)
);
                        htp.tableData (
enter_event.equip_rec.lalpha(row) );
                        htp.tableData (
enter_event.equip_rec.halpha(row) );
                        htp.tableData ( enter_event.equip_rec.mux(row)
);
                        htp.tableData ( enter_event.equip_rec.lslot(row)
);
                        htp.tableData ( enter_event.equip_rec.hslot(row)
);
                        htp.tableRowClose;
                END LOOP;

                htp.tableClose;
        ELSE
                htp.p ( 'I HAVE COMPLETELY FORGOTTEN ABOUT EQUIP_REC!'
);
        END IF;



----------------------------------------------------------------


----------------------------------------------------------------

Thanks again for taking a look at my buggy code. Maybe I can return the favor if you have a PL/SQL-specific question in future.

And please, go ahead and help yourself to the M&M jar outside my cube.

:-)

Best regards,

--

Wayne Stewart
MCI - Campbell Creek

tel:    972-918-7009
vnet:   777-7009
fax:    972-918-6097