Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Error PLS-00049 on trigger

MathBobOct 12 2015 — edited Oct 12 2015

Hello,

I'm trying to create a trigger that put data on a table to an other table. I have data that arrives on real-time on the table EV_48H and i have to put them automatically on the tables that are named 'EVV_'+Ref_equip.

I have an error PLS-00049 for lines 17 and 20. I'm not accostumed with PL/SQL.

    

Code:

create or replace

TRIGGER "SIVO"."NEWtrigger3EV_48H"

BEFORE INSERT

ON SIVO.EV_48H

FOR EACH ROW

begin

SELECT clef_var

INTO :new.Clef_Var

FROM sivo.c_variable

WHERE Ref_Var= :new.Ref_Var;

  -- Conversion des formats Date-Heure en DateHeure oracle

  :New.EV_DATEAUTO := to_date(:New.EV_DATE || ' ' || :New.EV_HEURE, 'DD/MM/YY HH24:MI:SS');

 

  IF clef_var is not null then

  SELECT Ref_Equip

  INTO :New.Ref_Equip

  FROM sivo.C_Equip

  WHERE Ref_Var= :new.Ref_Var;

  INSERT INTO SIVO.||'EVV_'|| :New.Ref_Equip (Clef_Var, Date1, Valeur)

  VALUES ( :New.Clef_Var, :New.Ev_DateAuto, :New.Ev_Valeur );

  ELSE

  INSERT INTO SIVO.EV_48H_VAR_INCONNUES (REF_VAR, EV_DATE, EV_HEURE, EV_VALEUR)

  VALUES ( :New.REF_VAR, :New.EV_DATE, :New.EV_HEURE, :New.EV_VALEUR);

   

  end if;

END;

  • Erreur(17,8): PLS-00049: variable attachée (bind variable) erronée 'NEW.REF_EQUIP' (bind variable invalid)
  • Erreur(20,3): PL/SQL: SQL Statement ignored
  • Erreur(20,21): PL/SQL: ORA-00903: nom de table non valide (table name invalid -> because the bind variable is invalid?)
  • Erreur(20,32): PLS-00049: variable attachée (bind variable) erronée 'NEW.REF_EQUIP'  (bind variable invalid)

So the error is in this part of the code:

  IF clef_var is not null then

  SELECT Ref_Equip

  INTO :New.Ref_Equip

  FROM sivo.C_Equip

  WHERE Ref_Var= :new.Ref_Var;

  INSERT INTO SIVO.||'EVV_'|| :New.Ref_Equip (Clef_Var, Date1, Valeur)

  VALUES ( :New.Clef_Var, :New.Ev_DateAuto, :New.Ev_Valeur );

Thank you for the help

Comments

Jagadekara

INTO :new.Clef_Var?

MathBob

I took this line from an old trigger, but yeah i don't think this line is usefull

HuaMin Chen

The error is regarding bad bind variable and you can refer to old and new values of the table, within the trigger, using

:old.column1

:new.column1

MathBob

Ok, i searched but i don't really see where do i have to put the old value.

Chris Hunt

Is there a column in SIVO.EV_48H called REF_EQUIP? If not, it's not going to like :NEW.REF_EQUIP.


Either way, it's not going to like this:

INSERT INTO SIVO.||'EVV_'|| :New.Ref_Equip

If you don't know the name table you're going to be inserting into at compile time, you'll have to use dynamic SQL to build the SQL statement at run time. Look up EXECUTE IMMEDIATE for examples.

MathBob

Ref_equip is not in SIVO.EV_48H

Thank you Chris, i'll try in dynamic SQL

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 9 2015
Added on Oct 12 2015
6 comments
627 views