RE: LONG fields within triggers.

From: Morten Primdahl <morten_at_caput.com>
Date: Wed, 21 Mar 2001 11:46:35 -0800
Message-ID: <F001.002D3CC4.20010321114510@fatcity.com>

Thanks for the prompt tip Jack, it works! :) Are there any caveats? How about concurrency? As it is now, I insert into the PL/SQL table using

  v_Index := NVL(UpdatePackage.v_IDs.LAST, -1);   v_Index := v_Index+1;
  UpdatePackage.v_IDs(v_Index) := :new.id;

within the row trigger. In the statement trigger I use

  v_Index := UpdatePackage.v_IDs.FIRST;
  WHILE v_Index IS NOT NULL LOOP     

   BEGIN
    OPEN selectCursor; --Selects LONG field from the updated table

        FETCH selectCursor INTO tempContent; --tempContent is a LONG var     CLOSE selectCursor;

    IF tempContent IS NOT NULL THEN --Insert into target table

      INSERT INTO table_b (id, field) 
      VALUES (UpdatePackage.v_IDs(v_Index),'<data>'||tempContent||'</data>');
      tempContent := NULL;

    END IF;
   END;
   UpdatePackage.v_IDs.DELETE(v_Index); --Delete from the PL/SQL table    v_Index := UpdatePackage.v_IDs.NEXT(v_Index);   END LOOP; Any bets on the thread safety on this procedure? What if eg. 4 users update the PL/SQL table in the row triggers, and all 4 of the subsequent statement triggers read the same variable from the PL/SQL table. Is this an issue at all? I'm not at all experienced in the more complex ways of PL/SQL.

Thanks a ton.

Morten

On Wed, 21 Mar 2001, Jack C. Applewhite wrote:

> Morten,
>
> One solution to this classic "Mutating Table" problem is to
> capture the ID (better yet, RowID) of each inserted row in a
> public PL/SQL table in an After Row Trigger, then loop
> through the PL/SQL table in an After Statement Trigger and
> do your Selects and Inserts. Works great.
>
> Jack
>
> --------------------------------
> Jack C. Applewhite
> Database Administrator/Developer
> OCP Oracle8 DBA
> iNetProfit, Inc.
> Austin, Texas
> www.iNetProfit.com
> japplewhite_at_inetprofit.com
>
>
> -----Original Message-----
> Morten
> Primdahl
> Sent: Wednesday, March 21, 2001 9:16 AM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Hi. I'm indexing the DB with the use of triggers.
> In one table, I have a LONG field, I need to read
> the :new value of this field in the trigger, modify
> it slightly, and insert the value into my context
> indexed table.
>
> I'm not allowed to query the table in the trigger
> because of mutation, so I cannot do stuff like
>
> CREATE TRIGGER ... ON test_table
> FOR EACH ROW
> DECLARE
> tempContent LONG;
>
> CURSOR selectCursor IS
> SELECT long_field FROM test_table WHERE id = :new.id;
>
> BEGIN
> OPEN selectCursor;
> FETCH selectCursor INTO tempContent;
> CLOSE selectCursor;
>
> And I cannot reference the LONG field directly, eg.
> :new.long_field,
> what can be done? I need to do the equivalent of
> BEGIN
> INSERT INTO other_table VALUES
> ('PREFIX'||:new.long_field||'POSTFIX');
> END;
> In the trigger. Any help greatly appreciated.
>
> Thanks
>
> Morten
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jack C. Applewhite
> INET: japplewhite_at_inetprofit.com
>
> 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_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Morten Primdahl
  INET: morten_at_caput.com

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_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-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).

Received on Wed Mar 21 2001 - 13:46:35 CST