troubleshooting Question

PLS-00049: bad bind variable

Avatar of Jean-Luc
Jean-Luc asked on
Oracle Database
2 Comments1 Solution14185 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
asimkovsky

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros