Re: PLS-00382 error in Trigger

A copy of this was sent to "Kelly Gallagher" <kelly_gallagher_at_unc.edu> (if that email address didn't require changing) On Thu, 9 Sep 1999 13:45:31 -0400, you wrote:

>Hello,
>
>I'm a newbie, trying to create a trigger (actually two triggers since
>I need to avoid the mutating table error). I am working from Oracle8
>PL/SQL Programming, 1997, pages 337 and 338. Any suggestions
>as to why I am getting this damn PLS-00382 error are most welcome;
>I just can't seem to figure it out. Thanks !
>
>
>The SQL sessions:
>
>
> SQL> CREATE OR REPLACE PACKAGE ih_fumehoodinsphist_data AS
> 2
> 3 TYPE t_Hood_Nums IS TABLE OF ih_tb_fumehoodinsphist.hood_num%type
> 4 INDEX BY BINARY_INTEGER;
> 5
> 6 v_FumeHoodInspHistHood_Nums t_Hood_Nums;
> 7
> 8 END ih_fumehoodinsphist_data;
> 9 /
>
>Package created.
>
>
>SQL> CREATE OR REPLACE TRIGGER Rih_fumehoodinsphist_Insert
> 2 BEFORE INSERT ON ih_tb_fumehoodinsphist
> 3 FOR EACH ROW
> 4 BEGIN
> 5
> 6 ih_fumehoodinsphist_data.v_FumeHoodInspHistHood_Nums := :new.hood_num;
> 7
> 8 END Rih_fumehoodinsphist_Insert;
> 9 /
>
>Warning: Trigger created with compilation errors.
>
>SQL> show errors
>Errors for TRIGGER RIH_FUMEHOODINSPHIST_INSERT:
>

It is because ih_fumehoodinsphist_data.v_FumeHoodInspHistHood_Nums (besides being the longest pair of variable names I've ever seen) is an array and :new.hood_num is not.

it should be:

ih_fumehoodinsphist_data.v_FumeHoodInspHistHood_Nums( ih_fumehoodinsphist_data.v_FumeHoodInspHistHood_Nums.count+1 ) := :new.hood_num;

You could see my website (in my signature) for a short paper on avoiding mutating tables with some cut and paste examples.

>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>3/2 PL/SQL: Statement ignored
>3/58 PLS-00382: expression is of wrong type
>SQL>
>
>
>
>

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