I have the following IF in a Function in a Package:
IF v_Updates_Available > 0 --If Updates Available
THEN
-- Update records in MEDLKPADM.IE_UNMET_NEEDS_LKP
UPDATE MEDLKPADM.TEMP_IE_UNMET_NEEDS_LKP UN
SET ( CID ) =
(
SELECT DISTINCT IC.NEW_CID
FROM MEDLKPADM.IE_INACTIVE_CID_V01 IC
WHERE IC.CID = UN.CID
AND IC.TABLE_NAME = 'IE_UNMET_NEEDS_LKP'
)
WHERE EXISTS
(SELECT 1
FROM MEDLKPADM.IE_INACTIVE_CID_V01 IC
WHERE IC.CID = UN.CID
-- Verify a New CID was Found
AND IC.NEW_CID IS NOT NULL
AND IC.TABLE_NAME = 'IE_UNMET_NEEDS_LKP'
-- Verify the Update would not cause a PK Error
-- PK is PRODUCT, CID
AND NOT EXISTS
(SELECT 1
FROM MEDLKPADM.TEMP_IE_UNMET_NEEDS_LKP sUN
WHERE UN.PRODUCT = sUN.PRODUCT
AND IC.NEW_CID = sUN.CID));
-- COMMIT Changes
COMMIT;
END IF; --If Updates Available
I was hit the Apply Auto Debugger because I am working on a different Function in the Package, but once applied it a series of output statements right before the WHERE in the UPDATE statement as shown below making the statement invalid:
This is easy enough for me to manually delete this block of outputs and get on with what I am doing, but thought I should let you know. Luckily in this Package this is the only code causing an inappropriate debug block to be created.
Let me know if you need any additional info.