Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 14146
  • Last Modified:

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
0
Jean-Luc
Asked:
Jean-Luc
1 Solution
 
asimkovskyCommented:
Take the colons out from in front of the AA variables throughout the code.



Andrew
0
 
Jean-LucAuthor Commented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now