For an audit, I need to keep all deleted records from a table.
I'm trying to run the delete and return all the deleted records into a collectio (RETURNING INTO). The delete have to be dynamic, the table is created by the procedure and the procedure will not compile with a static query.
I'm getting ORA-06550: line 18, column 33:
PLS-00429: unsupported feature with RETURNING clause
ORA-06550: line 18, column 5:
PL/SQL: Statement ignored
This is the idea:
P_ABBR VARCHAR2(100) := 'TRY';
P_FEED_KEHY NUMBER := 1;
TYPE T_CLAIM_REC IS TABLE OF STG_F_CLAIM_TRY%ROWTYPE INDEX BY PLS_INTEGER;
V_SQL_STM VARCHAR2(32500) := NULL;
'DELETE FROM STG_F_CLAIM_TRY A '||
'WHERE CLAIM_NUM IN (SELECT CLAIM_NUM '||
' FROM STG_CLAIM_TRY) '||
'RETURNING CLAIM_KEY, CLAIM_NUM, D_CODE_MOD_KEY, PLACE_OF_SVC INTO :T_CLAIM';
EXECUTE IMMEDIATE V_SQL_STM RETURNING BULK COLLECT INTO T_CLAIM;