Re: Maintaining Transaction logs on tables in Oracle 8.0.4....

A copy of this was sent to "Ian Sparks" <isparks_at_spam-me-not-wmute.u-net.com> (if that email address didn't require changing) On Fri, 12 Feb 1999 16:02:43 -0000, you wrote:

>Hi,
>
> I have an application which contains a data table, this table has a
>similarly defined transaction table.
>
[snip]
>
>I have ON INSERT, ON UPDATE and ON DELETE triggers on the mydata table which
>maintain the transaction log - inserting new values into the myData_trans
>table.
>
>cChanges is a BLOB field into which users enter text which states what the
>reason for the data changes between records were. It could be quite large
>(though probably < 16k) in length so I can't use VARCHAR2 because of the
>risk of running out of space.
>
>My problem is that I get ORA-22275 Invalid Blob Locator errors in my myData
>table triggers when trying to copy data via INSERT statements into the
>Transaction table :
>

from the application developers guide:

• 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 cannot do what you are attempting, however, a workaround for you might be to defer reading the LOB until an AFTER trigger (not for each row):

SQL> CREATE TABLE employ(name VARCHAR2(80),resume CLOB); Table created.

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

  4          new_rowids rowidArray;
  5          empty      rowidArray;

SQL> create or replace trigger employ_bi   2 before insert on employ
  3 begin
  4 employ_pkg.new_rowids := employ_pkg.empty;   5 end;
  6 /
Trigger created.

SQL> create or replace trigger employ_aifer   2 after insert on employ
  3 for each row
  4 begin
  5 employ_pkg.new_rowids( employ_pkg.new_rowids.count+1 ) := :new.rowid;
  6 end;
  7 /
Trigger created.

SQL> create or replace trigger employ_ai   2 after insert on employ
  3 declare

  4      l_lob   clob;
  5      l_resume VARCHAR2(200);
  6      l_offset INTEGER:=1;
  7      l_amount INTEGER:=200;
  8  begin
  9          for i in 1 .. employ_pkg.new_rowids.count loop
                ... insert into another table select whatever from employ
                       where rowid = employ_pkg.new_rowids(i); .....
 15          end loop;

>INSERT INTO myData_Trans (cID,..,cChanges) VALUES
>(:new.cID,...,:new.cChanges);
>
>I can't be the only person who has ever wanted to build this kind of
>functionality I really would appreciate any and all help anyone can give me
>with this.
>
>- Ian.
>
>
>
 

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