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).
