Hi All,
I am using NVL function in dynamic sql but getting the error. My sql goes like this:
DECLARE
CURSOR C_PARTITION IS
SELECT TABLE_NAME, PARTITION_NAME, PARTITION_POSITION FROM ALL_TAB_PARTITIONS
WHERE TABLE_OWNER = 'FACTS'
AND TABLE_NAME = 'TABLE1'
ORDER BY PARTITION_NAME;
BEGIN
FOR R IN C_PARTITION
LOOP
EXECUTE IMMEDIATE 'UPDATE FACTS.TABLE1 PARTITION ( ' || R.PARTITION_NAME || ') AGG
SET AGG.COLUMN1 =
(SELECT NVL(COLUMN2,'UNKNOWN') FROM FACTS.TABLE2 EX WHERE AGG.COLUMN3 = EX.COLUMN4)
WHERE EXISTS (SELECT 1 FROM FACTS.TABLE2 EX WHERE AGG.COLUMN3 = EX.COLUMN4)
AND COLUMN1 IS NULL ' ;
COMMIT;
END LOOP;
END;
Error is:
ORA-06550: line 12, column 44:
PLS-00103: Encountered the symbol "UNKNOWN" when expecting one of the following:
* & = - + ; < / > at in is mod remainder not rem return
returning <an exponent (**)> <> or != or ~= >= <= <> and or
like like2 like4 likec between into using || bulk member
submultiset
The symbol "* was inserted before "UNKNOWN" to continue.
Please help.
Edited by: 951472 on Aug 23, 2012 8:24 AM