Dynamic RETURNING INTO collection

[Highlander]

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:
DECLARE
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;
T_CLAIM T_CLAIM_REC;
V_SQL_STM VARCHAR2(32500) := NULL;
BEGIN
V_SQL_STM :=
'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;
----
T_CLAIM.DELETE;
END;

[tamilselvan]

I think 9.2.0.4 supports RETURNING CLAUSE in BULK COLLECTION

Tamil

[Lily_Liu_2004]

Originally posted by Highlander
TYPE T_CLAIM_REC IS TABLE OF STG_F_CLAIM_TRY%ROWTYPE INDEX BY PLS_INTEGER;
;

try replacing the record structure based collection.
using the scalar values based collections.

[Lily_Liu_2004]

even oracle 8i supports bulk collect in returning into.
but the collection got some limitations. eg:
you can not use the row type based collection.

[agoodman]

It works like this (in a simplified schema):

Script:
SET SERVEROUT ON
CREATE TABLE T2
(
X NUMBER
);

CREATE OR REPLACE PACKAGE PK1 AS
TYPE T_TAB_X IS TABLE OF T2%ROWTYPE INDEX BY PLS_INTEGER;
TAB_X T_TAB_X;

PROCEDURE P;
END PK1;
/

CREATE OR REPLACE PACKAGE BODY PK1 AS
PROCEDURE P IS
vCmd VARCHAR2(999);
BEGIN
-- populate the test table
FOR i IN 1 .. 5 LOOP
INSERT INTO T2(X)
VALUES (i);
END LOOP;

vCmd := 'BEGIN ' ||
'DELETE FROM T2 RETURNING X BULK COLLECT INTO PK1.TAB_X; ' ||
'END;';
EXECUTE IMMEDIATE vCmd;

FOR i IN TAB_X.FIRST .. TAB_X.LAST LOOP
DBMS_OUTPUT.PUT_LINE(TAB_X(i).X);
END LOOP;
END P;
END PK1;
/

EXEC PK1.P

Output:
1
2
3
4
5