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
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