Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem building a trigger using a BLOB field in Oracle 10

522588Jul 11 2006 — edited Jul 12 2006
My case is the following,

I'm trying to implement an AFTER trigger which uses the :new.blob_field to insert the new value in a table. Because of the documented AFTER restriction and :new use for blob fields, I've though to add other BEFORE trigger that calls a procedure in which I can store the :new value and later insert the temporary value in the table when the AFTER trigger is executed.

The Idea is the following:

BEFORE trigger :new -> TEMP

and then

AFTER trigger uses TEMP to store in the new table

So, the final questions are the following:
-Any other idea to do the same, that is, to use the ":new" value in an AFTER trigger?

-How can I do this with the temp variable, is this correct?:

PROCEDURE store_blob ( new_val BLOB ) IS


BEGIN

DBMS_LOB.CREATETEMPORARY(new_blob_temp,TRUE, DBMS_LOB.SESSION);
-- fill with data
DBMS_LOB.COPY (new_blob_temp,new_val,DBMS_LOB.GETLENGTH(new_val),1,1);

END store_blob ;


Because of the design of the application I have to use the AFTER trigger to store the new value so I must find a solution for this type of fields.

Sorry for the size of the text and also for my english.

Thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 9 2006
Added on Jul 11 2006
4 comments
547 views