Mutating table solution ???

From: Clifford Buetikofer <clifford_buetikofer_at_merck.com>
Date: Tue, 03 Aug 1999 11:33:39 -0400
Message-ID: <37A70BD3.4FF9B115_at_merck.com>

[Quoted] I'm trying to check a record insertion to see if a particular date

column is null. If it's null, I want to insert the sysdate if the

sysdate is greater than 01-AUG-1999.

[Quoted] I tried using the mutating table solution in the Oracle Press PL/SQL

book on page 259. The book advises creating a package which contains a

PL/SQL table of the inserted data. Then a a row-level trigger runs

which stores the data in the PL/SQL table. Then a statement_level

trigger unloads the PL/SQL table and checks whether to update the date

column with the sysdate or not.

I basically have one script which creates the package and then creates

the 2 triggers. However I keep getting a compiler error:

PLS-00103:Encountered the symbol "CREATE"

Listed below is the PL code...

At my wits end,

Cliff

{----------------CUT HERE--------------------}

package datefix AS

type t_origdate is table of cab.origdate%type

    index by binary_integer;

    v_origdate t_origdate;

    v_numentries BINARY_INTEGER :=0;

end datefix;

CREATE OR REPLACE trigger date1

AFTER INSERT ON cab

for each row

begin

    datefix.v_numentries := datefix.v_numentries + 1;

    datefix.v_origdate(datefix.v_numentries):= :new.origdate;

end date1;

CREATE OR REPLACE trigger date2

after insert on cab

DECLARE     v_loopindex number;

    v_neworigdate date;

BEGIN for v_loopindex in 1..datefix.v_numentries LOOP

    v_neworigdate := datefix.v_origdate(v_loopindex);

    IF v_neworigdate is null THEN

    update CAB

    set origdate = sysdate

    where to_char(sysdate,'DD-MON-YYYY') >= '01-AUG-1999';

    END IF;     end loop;

    datefix.v_numentries := 0;

END date2;

       The contents of this message express only the sender's opinion.
       This message does not necessarily reflect the policy or views of
       my employer, Merck & Co., Inc.  All responsibility for the statements
       made in this Usenet posting resides solely and completely with the
       sender.

Received on Tue Aug 03 1999 - 17:33:39 CEST