Morten,
You're safe. Since each User Session runs its own
instantiation of each trigger and gets its own instantiation
of the PL/SQL table, they're totally isolated from one
another and don't interfere with each other at all.
That's why a PL/SQL table is better than a DB table for
storing After Row Trigger results, unless you can use an 8i
Global Temporary Table - but that would cause unnecessary
I/O. The PL/SQL table solution works so much more
efficiently.
It's unfortunate that you're using a LONG column, that
prevents you from using the syntax:
Insert Into Table2 (LongCol) Select LongCol From Table1
Where ... ;
It would be better in the long run (maybe even the long raw
run!) if you used a BLOB. That way the features of the
DBMS_LOB package would be available to you, not to mention
out-of-line storage options galore.
Anyway, glad it helped.
Jack
--------------------------------
Jack C. Applewhite
Database Administrator/Developer
OCP Oracle8 DBA
iNetProfit, Inc.
Austin, Texas
www.iNetProfit.com
[EMAIL PROTECTED]
-----Original Message-----
Morten
Primdahl
Sent: Wednesday, March 21, 2001 1:45 PM
To: Multiple recipients of list ORACLE-L
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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack C. Applewhite
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
