[prev in list] [next in list] [prev in thread] [next in thread] 

List:       odtug-webdev-l
Subject:    RE: Designer 6i Table API BUG ??? / mutating table handling
From:       "David Wendelken" <davewendelken () earthlink ! net>
Date:       2002-07-17 23:34:12
[Download RAW message or body]



 >John Caputo
 >Sent: Wednesday, July 17, 2002 7:02 PM
 >To: Multiple recipients of list ODTUG-WEBDEV-L
 >Subject: Re: Designer 6i Table API BUG ???
 >
 >
 >Dave,
 >
 >(although this thread was not about mutating tables)
 >
 >Why store and replace the Designer arrays? I always thought 
 >that the structure of how the API triggers was built took 
 >care of the mutating table issue as long as you put your code 
 >in the correct trigger. Thus my comment about it below. The 
 >architecture of the triggers is the same as what Feuerstein 
 >indicates in his books. The problem I was trying to address 
 >was not the mutating table problem, but how to get your code 
 >to execute either from anonymous SQL or from an API call.
 >
 >Is there something here I don't understand?
 >
 >John Caputo
 >johnjc@surewest.net
 >
 >David Wendelken wrote:
 >
 >> There is an easy way to handle the mutating table problem with the 
 >> tapi, if memory serves.
 >>
 >> Each TAPI package spec has two pl/sql tables (which, feeling feisty 
 >> today, I will call arrays).
 >>
 >> CG$YOUR_TABLE.cg$table cg$table_type; CG$YOUR_TABLE.cg$tableind 
 >> cg$tableind_type;
 >>
 >> Here's what you do in your after statement triggers to avoid the 
 >> mutating table problem. (I don't have the time to look up 
 >the syntax 
 >> and double check it, and it's been a long day.
 >> But I think you'll get the idea quick enough!)  Basically, 
 >you store the
 >> TAPI arrays in a locally declared set of arrays, execute 
 >the mutating
 >> code, then restore the TAPI arrays.
 >> (Make darn sure you restore the arrays!)
 >>
 >> Can't remember whether you have to do the same thing with this 
 >> variable or not, and don't have my notes handy, but a bit of 
 >> experimentation will let you know for sure:  
 >> CG$YOUR_TABLE.called_from_package
 >>
 >> ... BUNCHES OF GENERATED CODE...
 >>
 >> -- The place you want to put your code.
 >> DECLARE
 >>   local_cg$table    CG$YOUR_TABLE.cg$table_type    :=
 >> CG$YOUR_TABLE.cg$table;
 >>   local_cg$tableind CG$YOUR_TABLE.cg$tableind_type := 
 >> CG$YOUR_TABLE.cg$tableind; BEGIN
 >>   ...MUTATION-CAUSING STATEMENT HERE.
 >>   CG$YOUR_TABLE.cg$table    := local_cg$table;
 >>   CG$YOUR_TABLE.cg$tableind := local_cg$tableind;
 >> EXCEPTION
 >>   WHEN ... THEN
 >>     ... OTHER ERROR HANDLING CODE.
 >>     CG$YOUR_TABLE.cg$table    := local_cg$table;
 >>     CG$YOUR_TABLE.cg$tableind := local_cg$tableind;
 >>     ... OTHER ERROR HANDLING CODE INCLUDING ANY
 >> RAISE_APPLICATION_ERRORS.
 >> END;
 >>
 >> B
 >> ... THE REST OF THE GENERATED CODE...
 >>
 >> The very simple trick is, just before you issue a statement in your 
 >> after statement trigger that would cause a mutating table error,
 >>
 >> =================================
 >>
 >> David Wendelken
 >> CASEtech, Inc.
 >>
 >> "All Oracle, All the time"      Oracle Business Partner
 >>
 >>
 >>  >-----Original Message-----
 >>  >From: root@fatcity.com [mailto:root@fatcity.com] On 
 >Behalf Of  >John 
 >> Caputo
 >>  >Sent: Wednesday, July 17, 2002 5:47 PM
 >>  >To: Multiple recipients of list ODTUG-WEBDEV-L
 >>  >Subject: Re: Designer 6i Table API BUG ???
 >>  >
 >>  >
 >>  >Binh,
 >>  >

One of the posts in this thread mentioned the mutating table problem,
and I wanted people to know how to handle it.

Here's the code from a before statement trigger:

CREATE OR REPLACE TRIGGER cg$BIS_ACTION
BEFORE INSERT ON ACTION
BEGIN
--  Application_logic Pre-Before-Insert-statement <<Start>>
--  Application_logic Pre-Before-Insert-statement << End >>

    cg$ACTION.cg$table.DELETE;
    cg$ACTION.cg$tableind.DELETE;
    cg$ACTION.idx := 1;

--  Application_logic Post-Before-Insert-statement <<Start>>
--  Application_logic Post-Before-Insert-statement << End >>
END;
/
  
Note that the pl/sql tables (arrays) are flushed as the first step.

Situation: I just did an insert statement that inserted 100 rows at a
time.

The array holds all 100 rows.

Now the after statement trigger fires and processes the first record in
the array.

It issues (with our custom code) an insert, update, or delete statement
on its own table.

The array is emptied in that statement's before statement trigger.

That statement loads up the array for the records it processes.

The results are, to say the least, unpleasant, because we still have the
remaining 99 rows to process and they are no longer in the array! (The
2nd statement's records are still in the array, though.)

Basically, although our processing is recursive, the tapi-generated
array is not.  It is shared by all active statements on that table.
That's why it's important to store the array and restore it afterwards.

Hope that was clear.  Recursion always hurts my head. :)


Thanks to everyone for making ODTUG 2002 a great success!  Plan now
for next year's conference: Loews Miami Beach, Florida, June 22-27, 2003.
-- 
Author: David Wendelken
  INET: davewendelken@earthlink.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-WEBDEV-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
[prev in list] [next in list] [prev in thread] [next in thread]