| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: LONG fields within triggers.
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
-----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
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: 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).Received on Wed Mar 21 2001 - 14:57:45 CST
 |
 |