Dynamic RETURNING INTO collection
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Dynamic RETURNING INTO collection

  1. #1
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105

    Question 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;

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I think 9.2.0.4 supports RETURNING CLAUSE in BULK COLLECTION

    Tamil

  3. #3
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257

    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.

  4. #4
    Join Date
    Mar 2004
    Location
    IA USA
    Posts
    257
    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.

  5. #5
    Join Date
    May 2004
    Posts
    4
    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
  •  


Click Here to Expand Forum to Full Width