Forum Stats

  • 3,839,695 Users
  • 2,262,529 Discussions
  • 7,901,042 Comments

Discussions

Error PLS-00049 on trigger

MathBob
MathBob Member Posts: 12
edited Oct 12, 2015 10:17AM in SQL & PL/SQL

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

Tagged:

Answers

This discussion has been closed.