-
I am getting ORA-02291 (integrity constraint on the foriegn key, parent key does not exist) error when running the following MERGE statement from within a PL/SQL Block within a script. I am converting an extensive amount of data from an 8i database to a new datamodel on a 9i database. The error is bogus. The parent key does exist. I've already inserted into the table in question using the same foriegn key. But when I do it with the merge it fails. cPropLst is a cursor.
MERGE INTO Properties.PropListPaidFor pl
USING ( SELECT a.PropertyID PropertyID, tma.IsUtility IsUtility, SUM(tma.Provided) Provided
FROM apartments.Amenities@ag a, Properties.tmp_MapAmenities tma
WHERE a.PropertyID = cPropLst.PropertyID AND
a.Available = 'Y' AND
tma.OldAmenityNumber = a.AmenityNumber AND
tma.IsUtility <> 0 AND
OldAmenityNumber < 32
GROUP BY a.PropertyID, tma.IsUtility ) s
ON ( pl.PropertyID = s.PropertyID AND pl.ListingID = cPropLst.ListingID AND pl.UtilityTypeID = s.IsUtility )
WHEN MATCHED THEN UPDATE SET pl.IsSubsidized = (pl.IsSubsidized + s.Provided)
WHEN NOT MATCHED THEN INSERT ( pl.PropertyID, pl.ListingID, pl.UtilityTypeID, pl.IsSubsidized )
VALUES ( cPropLst.PropertyID, cPropLst.ListingID, s.IsUtility, s.Provided );
I tried commiting all changes before executing this code, added the cursor identifiers (pl) to the INSERT and a few other things to no avail. I don't know what the source of this error is. If I try to execute it manually, replacing the variables with values, it works fine.
Can anyone help?
-
For those who care;
Apparently the INSERT/VALUES portion of a MERGE INTO statement does not like expressions or variables such as the Cursor variable. It must have replaced the values of the two cursor variables I was using with something else (no idea what else)giving the bogus Foreign key error when it tried to insert them. I'd love to know what it's doing if anyone out there knows please let me know.
When I placed the cursor variables into the USING clause and placed the column names into the VALUES area it worked fine.
Just thought that someone out there would want to know.
