-
Dynamic RETURNING INTO collection
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;
-
I think 9.2.0.4 supports RETURNING CLAUSE in BULK COLLECTION
Tamil
-
Re: Dynamic RETURNING INTO collection
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.
-
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.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|