Avatar of Jean-Luc
Jean-Luc
 asked on

PLS-00049: bad bind variable

Hi everyone

I am having this problem:

I created a view that referencing 3 tables:

CREATE VIEW TURBINE_USER AS
SELECT A.RESOURCE_KEY AS USER_ID,
       A.USERNAME AS LOGIN_NAME,
       A.PASSWORD AS PASSWORD_VALUE,
       B.GIVEN_NAME AS FIRST_NAME,
       B.FAMILY_NAME AS LAST_NAME,
       C.MEDIUM_NUMBER AS EMAIL,
       'CONFIRMED' AS CONFIRM_VALUE,
       NULL AS MODIFIED,
       NULL AS CREATED,
       NULL AS LAST_LOGIN,
       NULL AS DISABLED,
       NULL AS OBJECTDATA,
       NULL AS PASSWORD_CHANGED      
FROM JONAS.RESOURCE_TABLE A, JONAS.PERSONAL_INFORMATIONS B, JONAS.COMMUNICATION_MEDIUM C
WHERE A.PERSONAL_INFORMATIONS_KEY = B.PERSONAL_INFORMATIONS_KEY
AND A.RESOURCE_KEY = C.RESOURCE_KEY (+)  
AND C.TYPE (+) = 32
AND C.CATEGORY (+) = 3;

Then after I created a trigger type 'INSTEAD OF UPDATE':

CREATE OR REPLACE TRIGGER JETSPEED.TURBINE_USER_UPDATE_TRIGGER
INSTEAD OF UPDATE
ON TURBINE_USER
REFERENCING NEW AS NEW OLD AS OLD

BEGIN
 DECLARE AA NUMBER;

 BEGIN
  SELECT JONAS.RESOURCE_TABLE.PERSONAL_INFORMATIONS_KEY
  INTO AA
  FROM JONAS.RESOURCE_TABLE
  WHERE JONAS.RESOURCE_TABLE.RESOURCE_KEY = :OLD.USER_ID;

  UPDATE JONAS.COMMUNICATION_MEDIUM SET JONAS.COMMUNICATION_MEDIUM.MEDIUM_NUMBER = :NEW.EMAIL WHERE COMMUNICATION_MEDIUM_KEY = :OLD.USER_ID;

  UPDATE JONAS.PERSONAL_INFORMATIONS SET JONAS.PERSONAL_INFORMATIONS.GIVEN_NAME = :NEW.FIRST_NAME WHERE PERSONAL_INFORMATIONS_KEY = :AA;

  UPDATE JONAS.PERSONAL_INFORMATIONS SET JONAS.PERSONAL_INFORMATIONS.FAMILY_NAME = :NEW.LAST_NAME WHERE PERSONAL_INFORMATIONS_KEY = :AA;

  UPDATE JONAS.RESOURCE_TABLE SET JONAS.RESOURCE_TABLE.RESOURCE_KEY = :NEW.USER_ID WHERE RESOURCE_KEY = :OLD.USER_ID;

  UPDATE JONAS.RESOURCE_TABLE SET JONAS.RESOURCE_TABLE.USERNAME = :NEW.LOGIN_NAME WHERE RESOURCE_KEY = :OLD.USER_ID;

  UPDATE JONAS.RESOURCE_TABLE SET JONAS.RESOURCE_TABLE.PASSWORD = :NEW.PASSWORD_VALUE WHERE RESOURCE_KEY = :OLD.USER_ID;
 END;
END;

When I compile it I got the following message:

Numiro de ligne = 11 Numiro de colonne = 133 Texte d'erreur = PLS-00049: bad bind variable 'AA'

Numiro de ligne = 12 Numiro de colonne = 133 Texte d'erreur = PLS-00049: bad bind variable 'AA'

I really don't understand why?

PERSONAL_INFORMATIONS_KEY type is NUMBER

Need help please

Thanks in advance!

JL
Oracle Database

Avatar of undefined
Last Comment
Jean-Luc

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
asimkovsky

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jean-Luc

ASKER
Thank you Andrew

I am fairly new with Oracle. Right after I posted the question I found the problem with one of my colleague at work.

Thanks again!!

JL
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23