Oracle Trigger, initialize a variable

Question

when i'm executing this script in SQLPLUS, i had an error that told me to declare "ENSI" (line 11) and to declare "ENIT" (line 12),but i just need to initialize my variable to those String any idea why?

create or replace trigger ETUDIANT_TRIGGUER
Before update on ETUDIANT
for each row 
declare
  c number;
  id_edu ETUDIANT.id_etu%TYPE := :new.id_etu;
  NOM ETUDIANT.NOM%TYPE := :new.NOM;
  adresse ETUDIANT.adresse%TYPE := :new.adresse;
  cursus ETUDIANT.cursus%TYPE := :new.cursus;
  UNIVERSITE ETUDIANT.UNIVERSITE%TYPE := :new.UNIVERSITE;
  SITE_ENSI ETUDIANT.UNIVERSITE%TYPE := "ENSI";
  SITE_ENIT ETUDIANT.UNIVERSITE%TYPE := "ENIT";
begin
if :new.ID_ETU is null then
raise_application_error(-20001,'ID_ETU est null');    
end if;
select count(*) into c from ETUDIANT_ensi@site_ensi_link where ID_ETU= :new.ID_ETU;
if c > 0 then
raise_application_error(-20002, 'ID_ETU existe déjà dans le site _ensi');
end if;
select count(*) into c from ETUDIANT_enit@site_enit_link where ID_ETU= :new.ID_ETU;
if c > 0 then
raise_application_error(-20002, 'ID_ETU existe déjà dans le site _enit');
end if;  
if UPPER(UNIVERSITE)=SITE_ENSI then
insert into ETUDIANT_ensi@site_ensi_link values(id_edu,NOM,adresse,cursus,NB_EMPRUNTS,UNIVERSITE);
end if;    
if UPPER(UNIVERSITE)=SITE_ENIT then
insert into ETUDIANT_enit@site_enit_link values(id_edu,NOM,adresse,cursus,NB_EMPRUNTS,UNIVERSITE);
end if;  
end;
/

Mureinik · Accepted Answer · 2014-03-30 21:30:30Z

3

String literals in [pl]SQL are enclosed by single quotes, not double quotes. So, you should replace the following two lines:

SITE_ENSI ETUDIANT.UNIVERSITE%TYPE := "ENSI";
SITE_ENIT ETUDIANT.UNIVERSITE%TYPE := "ENIT";

With these two:

SITE_ENSI ETUDIANT.UNIVERSITE%TYPE := 'ENSI';
SITE_ENIT ETUDIANT.UNIVERSITE%TYPE := 'ENIT';

answered Mar 30, 2014 at 21:30

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mohammed Ali Kachbouri Over a year ago

i tested but it returned: ORA-00984: Column Not Allowed

Alex Poole Over a year ago

@MohammedAliKachbouri - not from the same line(s) though, from the insert, right? So this is a new question really; but it's because you have a scope issue. When you refer to id_edu etc. in the values clause they're being interpreted as columns in the table, not variables - it's confusing because they have the same names. Either change your local variable names, or - since I'm not sure why you have them anyway - just refer to :new.id_edu there instead.

Collectives™ on Stack Overflow

Oracle Trigger, initialize a variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related