LONG fields within triggers.
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 -- Morten Primdahl Caput A/S Tel +45 70 12 24 42 [EMAIL PROTECTED]Nygade 6Fax +45 70 11 24 42 http://www.caput.com/ DK-1164 Kbh K -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Morten Primdahl 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).
RE: LONG fields within triggers.
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),''||tempContent||''); 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 > [EMAIL PROTECTED] > > > -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: [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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Morten Primdahl 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).
LONG vs CLOB (Was: LONG fields within triggers.)
Thanks again Jack. > 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. I'm aware that LONG eventually will be discontinued - anyone know when? We mainly use the large data types for streaming data, eg. pictures - so far we've not been able to get the same throughput (bytes/sec) from (even optimized) LOB fields. In this specific case, I'm indexing text, at most 100k/record I reckon and the CLOB overhead would probably be negligible - I probably spent more time on making the triggers than the entire accumulated overhead will be for the next decade! But the learning experience was great :) Morten -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Morten Primdahl 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).
Intermedia violates own constraint.
Hi. I had some problems with getting Intermedia to work after patching to 8.1.7. I got a ORA-04045: errors during recompilation/revalidation of CTXSYS.CONTEXT ORA-29835: ODCIGETINTERFACES routine does not return required interface(s) When trying to create an index. Searched Metalink, and eventually found document 1327325 where the workaround is stated to be spool recompile1.sql select 'alter package '||owner||'.'||object_name||' compile;' from dba_objects where object_type='PACKAGE' and status='INVALID' and owner in('ORDSYS','CTXSYS'); spool off; @recompile1.sql I applied this, but when I try to create the index now, I get SQL> CREATE INDEX ccs_search_index ON ccs_search(content) INDEXTYPE IS ctxsys.context PARAMETERS('section group ccs_search_group'); 23 CREATE INDEX ccs_search_index * ERROR at line 1: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine ORA-2: interMedia Text error: ORA-1: unique constraint (CTXSYS.DRC$IDX_COLSPEC) violated ORA-06512: at "CTXSYS.DRUE", line 122 ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 34 ORA-06512: at line 1 It seems to me, that intermedia tries to index into an existing table, and failes because it has lost track of it's own sequence? I'm just guessing here and could really use a few tips to get this thing working. Thanks, Morten -- Morten Primdahl Caput A/S Tel +45 70 12 24 42 [EMAIL PROTECTED]Nygade 6Fax +45 70 11 24 42 http://www.caput.com/ DK-1164 Kbh K -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Morten Primdahl 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).