Long Raw / LOB in a Trigger

[Kirk Cameron]

I need to create a trigger to perform application specific auditing on a

table containing a BLOB or a Long Raw column. I have tried both with
little sucess.

Base table

t_item
id number(10) not null PK
blobcol blob

I have an insert/update trigger on T_ITEM that tries to do the following

insert into t_item_audit (id, blobcol, mod_dt) values(:new.id,
:new.blobcol,sysdate);

When the trigger fires I get the following error:

ORA-22275: invalid LOB locator specified

I tried a similar approach with a LONG RAW but :new/:old bind variables
are not supported for LONG RAW columns.

Any help would be appreciated.

Kirk

[Thomas Kyte]

A copy of this was sent to Kirk Cameron <cam...@bicnet.net>
(if that email address didn't require changing)

What you are hitting is (from the application developers guide on LOBS):

Triggers are not supported on LOBs. However, you can use a LOB in the
body of a trigger as follows:

- in regular triggers old values may be read but not written and new
values may not be read nor written.

- in 'instead of triggers', the:old and:new values can be read but not
written.
-------------------------------------------------------------------

So, you have a 'regular' trigger and are attempting to READ the :new value.
What you can do is something like this (since you are reading :new values)

drop table demo;
drop table audit_tab;

create table demo ( x int primary key, y clob );
create table audit_tab ( x int, y clob );

create or replace package demo_pkg
as
type pkArray is table of demo.x%type index by binary_integer;

empty pkArray;
newones pkArray;
end;
/

create or replace trigger demo_biu
before insert or update on demo
begin
demo_pkg.newones := demo_pkg.empty;
end;
/
create or replace trigger demo_biufer
before insert or update on demo for each row
begin
demo_pkg.newones(demo_pkg.newones.count+1) := :new.x;
end;
/
create or replace trigger demo_aiu
after insert or update on demo
begin
for i in 1 .. demo_pkg.newones.count loop
insert into audit_tab select * from demo where x = demo_pkg.newones(i);
end loop;
end;
/

that will save all of the INSERTED and UPDATED primary keys and then insert them
into the audit table in an AFTER trigger -- the AFTER (not for each row) trigger
can read these values safely...
h
Hope this helps.

>Any help would be appreciated.
>
>Kirk
>
>

Thomas Kyte
tk...@us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you. Any bounced
email will be treated the same way i treat SPAM-- I delete it.