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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

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