Re: How to audit LONG RAWs?

From: Kristian Myllymäki <kristian_at_kmja.com>
Date: Wed, 7 Apr 2004 15:03:08 +0200
Message-ID: <x_Scc.7820$EV2.67906@amstwist00>

Hi,

One way to manipulate LONG RAWs is to combine your trigger with pl/sql. Below I only show an example of one way to accomplish this but you'll have to modify it for your own demands.

Sorry, couldn't test this on 8.0.5 but I hope that pl/sql under 8.0.5 supports LONG RAWs.

SQL> create table t (a number, b long raw);

Table created.

SQL>
SQL> insert into t values (1, 'a0a0a0');

1 row created.

SQL>
SQL> create global temporary table temp_t (a number, length_of_b number) on commit delete rows;

Table created.

SQL>
SQL> create or replace trigger tu_t1 before update on t   2 declare
  3 cursor c is select a, b from t;   4 begin

  5        for rc in c loop
  6         insert into temp_t (a, length_of_b) values (rc.a, length(rc.b));
  7        end loop;

  8 end;
  9 /

Trigger created.

SQL>
SQL> create or replace trigger tu_t2 after update on t   2 declare
  3 cursor c is select t.a, t.b, temp_t.length_of_b from t, temp_t where t.a = temp_t.a;
  4 begin

  5        for rc in c loop
  6         if rc.length_of_b - length(rc.b) > 0 then
  7          dbms_output.put_line('Row with a = ' || rc.a || ' has decreased
it''s size');
  8         else
  9          dbms_output.put_line('Row with a = ' || rc.a || ' is OK after
update. Size is equal or larger.');
 10         end if;
 11        end loop;

 12 end;
 13 /

Trigger created.

SQL>
SQL> update t set b = 'a0a0a0a0' where a = 1; Row with a = 1 is OK after update. Size is equal or larger.

1 row updated.

SQL>
SQL> update t set b = 'a0a0' where a = 1; Row with a = 1 has decreased it's size
Row with a = 1 has decreased it's size

1 row updated.

SQL>
SQL> commit;

Commit complete.

/Kristian

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1081317216.655392_at_yasure...
> Karen Sundquist wrote:
>
> > Hi there
> > I have an Oracle 8.0.5 db as backend to a number of applications.
> > These apps stores ASCII plain text data in a LONG RAW field as
> > follows:
> >
> > CREATE TABLE Info (
> > InfoID INT NOT NULL PRIMARY KEY,
> > MyData LONG RAW NULL)
> >
> > Another table stores the length of data in this field
> > CREATE TABLE Length (
> > InfoID INT NOT NULL PRIMARY KEY,
> > MyLength INT NULL)
> >
> > Now these apps are old and partially documented. Every now and then
> > the contents of this field for any one record just disappear! This
> > data is some of the most important in this application. The field is
> > used to store a journal and is regulary updated. The client
> > applications update the field by extracting the entire contents,
> > appending the new info on the end and then loading the lot back into
> > the field.
> >
> > I would like to stop this data from diappearing, or at least be able
> > to retrieve it easily when it does happen.
> >
> > I thought at first I would put a trigger on the Info table and just
> > check that when the field is UPDATEd that the new data is longer than
> > the old data. However, manipulating LONG RAWs appears to be next to
> > impossible in Oracle SQL and not allowed in triggers. (This field is
> > often >32K)
> >
> > Changing the data to a CLOB is not an option as it would require
> > rewriting the applications.
> >
> > Do any of you have any suggestions?
> >
> > Karen
>
> If it is really important data:
>
> 1. Upgrade to 9.2.0.4
> 2. Move it to CLOBs
>
> If your employer won't spend the money ... it isn't THAT important.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Wed Apr 07 2004 - 08:03:08 CDT