Re: LONGs usage in Triggers

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 10 Sep 1999 13:21:17 -0400
Message-ID: <TjzZNwY9Xa=2yXH9Ur0b5P3=PU5s@4ax.com>

A copy of this was sent to "Jeremy Ovenden" <jovenden_at_hazelweb.co.uk> (if that email address didn't require changing) On Fri, 10 Sep 1999 17:13:44 +0100, you wrote:

>I realise that (certainly up to 8.0.5), you cannot reference :old or
>:new.longcolumn
>
>I need to transfer information from TABLEA , including the content of a LONG
>column, into another table TABLEB whenever a a row in TABLEA is inserted or
>updated. A trigger seemed the obvious choice originally.
>
>How can I implement this - is it possible in real-time or will I have to
>achieve this via some deferred transaction mechanism?
>
>Thanks......
>
>Jeremy Ovenden
>HazelWeb Systems
>UK
>

If your longs/long raws are limited to 32k or less, we can do this in plsql with triggers (example below).

if they are not -- and you are using 8.0, we can move the LONG to CLOB with plsql .

if you are using 8.1, we can use SQL to convert the LONG into a CLOB or a LONG RAW into BLOB (example below).

if they are not and you are using 7.x -- you will find you must use C and some defered mechanism.

See http://govt.us.oracle.com/~tkyte/Mutate/index.html. This goes over a general purpose way to avoid mutating tables. What this will do (you are interested in case 1 -- you only need to access the :new values) is to use a row trigger to capture the rowids of the newly inserted/updated rows. An AFTER trigger can then loop over these rowids -- processing the rows after the insert.

The after insert trigger will look something like:

  for i in 1 .. pkg.rowid_array.count loop

      for x in ( select * 
                   from the_table_with_a_long 
                  where rowid = pkg.rowid_array(i) ) loop
          insert into another_table values ( x.the_long_column );
      end loop;

  end loop;

if the long column is 32k or less. For longer columns, you'll need to use dynamic sql (dbms_sql) to piecewise read the long column and then dbms_lob.write to piecewise write it to a CLOB elsewhere.

If you are using 8.1 the trigger would look like:

  for i in 1 .. pkg.rowid_array.count loop

      execute immediate 'insert into another_table
                         select to_lob(the_long_column)
                           from the_table_with_a_long
                          where rowid = :x' using pkg.rowid_array(i);

  end loop;

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 10 1999 - 12:21:17 CDT