Re: LOB in Trigger

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/25
Message-ID: <8j5ntb$vpd$1@nnrp1.deja.com>#1/1

In article <8ivvfe$5rg9u$1_at_fu-berlin.de>,   "Christian Hartmann" <c.hartmann_at_tmp-office.de> wrote:
> Hi there,
>
> on oracle 8i using a trigger (update) I want to pass the value of a
> long-field to a stored procedure (:NEW.Long_Field_Name).
 Unfortunately, I
> got the error-message:
>
> ORA-04093 references to columns of type LONG are not allowed in
 triggers
> Cause:
> A trigger attempted to reference a LONG column in the
 triggering
> table.
> Action:
> Do not reference the LONG column.
>
> In this long-field there are only ascii-values. It is used as a remark
> field.
>
> Is there a way , to use the value of this long-field in a trigger? If
 yes,
> how?
>
> Thanx for help,
>
> Christian Hartmann
>
>

If the long is always 32k or less (and if it isn't, you wouldn't be able to pass it to a stored procedure since plsql variables are limited to 32k in size), then the following will work. To understand why I do what I'm doing, see:

http://osi.oracle.com/~tkyte/Mutate/index.html

it explains how to avoid a mutating table and the trick works for getting the value of a long/long raw in a trigger as well.

ops$tkyte_at_8i> create table t ( x int primary key, y long );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package state_pkg   2 as
  3 type ridArray is table of rowid index by binary_integer;   4

  5          newones ridArray;
  6          empty   ridArray;

  7 end;
  8 /

Package created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace trigger t_bi   2 before insert on t
  3 begin
  4 state_pkg.newones := state_pkg.empty;   5 end;
  6 /

Trigger created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace trigger t_aifer   2 after insert on t for each row
  3 begin
  4 state_pkg.newones(state_pkg.newones.count+1) := :new.rowid;   5 end;
  6 /

Trigger created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace trigger t_ai   2 after insert on t
  3 declare
  4 l_long long;
  5 begin

  6          for i in 1 .. state_pkg.newones.count loop
  7                  select y into l_long from t
                         where rowid = state_pkg.newones(i);
  8                  dbms_output.put_line( '**** ' ||
                                 substr( l_long, 1, 200 ) );
  9          end loop;

 10 end;
 11 /

Trigger created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> insert into t values ( 1, 'How now brown cow' ); **** How now brown cow

1 row created.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sun Jun 25 2000 - 00:00:00 CDT