Re: DBMS_LOB.COPY in triggers?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 28 Apr 1999 13:27:50 GMT
Message-ID: <372701b1.92870891_at_192.86.155.100>

A copy of this was sent to "Mel Christie" <mel_at_sensory.com> (if that email address didn't require changing) On Tue, 27 Apr 1999 16:39:31 -0400, you wrote:

>Can you use DBMS_LOB.COPY in triggers?
>
>When copying a blob I get an error:
>
>General SQL error.
>ORA-22275: invalid LOB locator specified
>ORA-06512: at "SYS.DBMS_LOB", line 393
>ORA-06512: at "SCIADMIN.ADD_V1_STUDENT_DATA",
>line 22
>ORA-04088: error during execution of trigger 'SCIADMIN.ADD_V1_STUDENT_DATA'.
>
>Any ideas?
>
>- Mel
>

In the application developers guide, the section on LOBS there is a section on lob restrictions. One of which is:

<quote>
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.
</quote>

it sounds like you are accessing the :new.lob_column in a trigger (it doesn't exist to be read yet).

the common workaround is to defer reading the lob until an AFTER trigger. It might look like this. In the following, i've replicated your issue (i believe) and then fix it:

SQL> create table t1 ( x clob );
Table created.

SQL> create table t2 ( y clob );
Table created.

SQL> create or replace trigger t1_trigger   2 before insert on t1
  3 for each row
  4 declare
  5 l_lob clob;
  6 begin

  7          insert into t2 values ( empty_clob() ) returning y into l_lob;
  8          dbms_lob.copy( l_lob, :new.x, dbms_lob.getlength(:new.x) );

  9 end;
 10 /
Trigger created.

SQL> insert into t1 values ( 'How Now Brown Cow' ); insert into t1 values ( 'How Now Brown Cow' )

            *
ERROR at line 1:

ORA-22275: invalid LOB locator specified
ORA-06512: at "SYS.DBMS_LOB", line 399
ORA-06512: at "TKYTE.T1_TRIGGER", line 5
ORA-04088: error during execution of trigger 'TKYTE.T1_TRIGGER'

SQL> drop trigger t1_trigger
  2 /
Trigger dropped.

REM -- dropped the trigger that doesn't work, lets make one that does.

SQL> create or replace package t1_state_pkg   2 as
  3 type myArray is table of rowid index by binary_integer;   4

  4          rids    myArray;
  5          empty   myArray;

  6 end;
  7 /
Package created.

SQL> create or replace trigger t1_trigger_bi   2 before insert on t1
  3 begin
  4 t1_state_pkg.rids := t1_state_pkg.empty;   5 end;
  6 /
Trigger created.

SQL> create or replace trigger t1_trigger_aifer   2 after insert on t1 for each row
  3 begin
  4 t1_state_pkg.rids(t1_state_pkg.rids.count+1) := :new.rowid;   5 end;
  6 /
Trigger created.

SQL> create or replace trigger t1_trigger_ai   2 after insert on t1
  3 declare

  4          l_lob           clob;
  5          l_newLob        clob;
  6  begin
  7       for i in 1 .. t1_state_pkg.rids.count
  8       loop
  9           insert into t2 values ( empty_clob() ) returning y into l_lob;
 10           select x into l_newLob from t1 where rowid = t1_state_pkg.rids(i);
 11           dbms_lob.copy( l_lob, l_newLob, dbms_lob.getlength(l_newLob) );
 12       end loop;

 13 end;
 14 /
Trigger created.

SQL> insert into t1 values ( 'How Now Brown Cow' ); 1 row created.

SQL> select * from t1;

X

---

How Now Brown Cow

SQL> select * from t2;

Y

---

How Now Brown Cow  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Wed Apr 28 1999 - 15:27:50 CEST