Re: not a pretty sight - PL/SQL global private elements
Date: 1996/12/06
Message-ID: <32a879ac.9838306_at_dcsun4>
I see all the htp calls in enter5. This leads me to believe the front end is a web browser.
PL/SQL package variables are persistent within a SESSION ( a session defined as the period of time between a logon and a logout). On the web, the sessions last only as long as it takes to generate a page. Every time you submit a URL to the webserver, you logon, execute pl/sql, and logout (the WRB maintains persistent connections to a database but authenticates/deauthenticates for each request).
Therefore, path 1 works for you cause it is all done in one session. Path 2 does not because it uses many sessions and the pl/sql state is reinitialized each time.
So, how to fix?
cookies might be the answer.
hidden fields will definitely work. For example, here is a package that will collect 4 items from a user and let them either enter more or print out what they have done so far. It uses hidden fields to save the state. Hope it helps. You should be able to run this in your web agents schema and then enter:
http://yourhost:yourport/WEBAGENTNAME/owa/example.enter_4
to run the demo...
create or replace package example
as
type array is table of varchar2(2000) index by binary_integer; empty_array array;
procedure enter_4( p_the_array array default empty_array,
p_submit_type varchar2 default NULL );
end example;
/
create or replace package body example
as
procedure print_em( p_the_array array )
is
begin
htp.uListOpen;
for i in 1 .. 1000000 loop
begin
if ( p_the_array(i) is not null ) then
htp.listItem( htf.escape_sc(p_the_array(i)) );
end if;
exception
when no_data_found then exit;
end;
end loop;
htp.uListClose;
end print_em;
procedure get_more( p_the_array in array )
is
begin
htp.formOpen( 'example.enter_4' );
for i in 1 .. 100000 loop
begin
htp.formHidden( 'p_the_array', htf.escape_sc(p_the_array(i)) );
exception
when no_data_found then exit;
end;
end loop;
htp.p( 'Enter up to 4 more things...' ); for i in 1 .. 4 loop
htp.br;
htp.formText( 'p_the_array' );
end loop;
htp.br;
htp.formSubmit( 'p_submit_type', 'More' );
htp.formSubmit( 'p_submit_type', 'Print em' );
htp.formClose;
end get_more;
procedure enter_4( p_the_array array default empty_array,
p_submit_type varchar2 default NULL )
is
begin
if ( nvl( p_submit_type,'More') = 'More' ) then
get_more( p_the_array );
else
print_em( p_the_array );
end if;
end enter_4;
end example;
/
[Quoted] On Fri, 06 Dec 1996 10:55:18 -0600, Wayne Stewart <wayne.stewart_at_mci.com> 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.
>
>> "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
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Dec 06 1996 - 00:00:00 CET