Duplicating rows in a table

From: James Cummings <jc1635_at_home.com>
Date: Sat, 20 Oct 2001 00:57:58 GMT
Message-ID: <q_3A7.10092$pb4.4670018_at_news2.rdc2.tx.home.com>

There is something I'm probably just missing and admittably, I'm not DBA caliber either so here goes....

I've got a table of some number of fields. I had thought that I might

1. Use a collection to store the contents of one row. 2, Create a duplicate of that row in the collection.
2. Change the primary key in the 2nd row to the next item on the sequence.
3. Finally, insert the 2nd row of the collection with the new primary key back into the original table.

Something along the lines of the following PL/SQL code:

• snip === cut ===

DECLARE

    v_ORIG_ROW_IDX BINARY_INTEGER := 100000001;
    v_NEW_ROW_IDX BINARY_INTEGER := 100000010;
    v_NEXT_FEATURE_ID FEATURE_MAIN.FEATURE_ID%TYPE;

    TYPE t_FEATURE_TABLE IS TABLE OF FEATURE_MAIN%ROWTYPE
               INDEX BY BINARY_INTEGER;

    v_FEATURE t_FEATURE_TABLE;

BEGIN
    SELECT * INTO v_FEATURE(v_ORIG_ROW_IDX)     FROM FEATURE_MAIN
    WHERE FEATURE_ID = 6;     SELECT SEQ_FEATURE_ID.NEXTVAL INTO v_NEXT_FEATURE_ID     FROM DUAL;     v_FEATURE(v_NEW_ROW_IDX ) := v_FEATURE(v_ORIG_ROW_IDX);     v_FEATURE(v_NEW_ROW_IDX).FEATURE_ID := v_NEXT_FEATURE_ID;

    INSERT INTO FEATURE_MAIN VALUES(v_FEATURE(v_NEW_ROW_IDX));

    COMMIT;
END;

• snip === cut ===

Naturally it wasn't that easy. Everything works as I expected until I get to the INSERT at that point
I get an error:

    ERROR at line 16:

    ORA-06550: line 16, column 37:
    PLS-00382: expression is of wrong type
    ORA-06550: line 16, column 5:

basically telling me that v_FEATURE(v_NEW_ROW_IDX) is the wrong data type.

I think I'm either very close and just missing something or so far out in left field there's little or no help.
Can anyone point me in the right direction?

---

James Cummings
jc1635_at_home.com Received on Sat Oct 20 2001 - 02:57:58 CEST