0

I need to build a PL/SQL procedure that takes data from a source table and inserts it into a target table. The source table has an ITEM1 column, an ITEM2 column, and a SRC_CODE column. The SRC_CODE column contains a string that is a SQL Select Statement, i.e. SELECT KEY FROM SOMETABLE WHERE DAY = V_DAY. So, I somehow need to execute the statements within the SRC_CODE column and populate that V_DAY variable within the select statement. The resulting KEY values, along with ITEM1 and ITEM2 from the source table will go into the TARGET table.

Approaching the process logically, I gather I need to take a row from the source table, execute the SRC_CODE into a collection, then take each KEY from the collection and tie it back to the ITEM1 and ITEM2 and insert KEY, ITEM, and ITEM2 into the target table. I have no clue how to go about this in terms of programming.

The following is my attempt to at least populate the target with key values, but to no avail, as I get a invalid identifier error. If someone could correct/expand on this to get what I need, it would be much appreciated:

CREATE OR REPLACE PROCEDURE POPULATETARGET IS
TYPE KEYS_T IS TABLE OF SOMETABLE.KEY%TYPE;
L_KEYS KEYS_T;
V_DAY NUMBER;
SRC_CODE_FETCH VARCHAR2(200);
V_SRC_CODE VARCHAR2 (4000);
RC SYS_REFCURSOR;

BEGIN
V_DAY := 20150826;
SRC_CODE_FETCH := 'SELECT SRC_CODE FROM SOURCE';

OPEN RC FOR SRC_CODE_FETCH;
    LOOP
        FETCH RC INTO V_SRC_CODE;
        EXIT WHEN RC%NOTFOUND;
            EXECUTE IMMEDIATE V_SRC_CODE BULK COLLECT INTO L_KEYS USING V_DAY;
            FORALL x IN L_KEYS.FIRST..L_KEYS.LAST
                INSERT INTO TARGET VALUES L_KEYS(x);   
    END LOOP;
    CLOSE RC;   
END;
cddau
  • 21
  • 3

2 Answers2

0

The problem is that you are missing parenthesis at insert statement,so your insert line should be:

INSERT INTO TARGET VALUES (L_KEYS(x));

Also i recommend you use COMMIT after this line.

Aramillo
  • 3,176
  • 3
  • 24
  • 49
0

This worked to perfection:

CREATE OR REPLACE PROCEDURE POPULATETARGET IS   
    TYPE KEYS_T IS TABLE OF SOMETABLE.KEY%TYPE;
    L_KEYS KEYS_T;
    V_DAY NUMBER;
    V_SRC_CODE VARCHAR2 (4000);
    RC SYS_REFCURSOR;

BEGIN
    VDAY_ID := 20150826;

    OPEN RC FOR SELECT SRC_CODE FROM SOURCE;
    LOOP
        FETCH RC INTO V_SRC_CODE;
        EXIT WHEN RC%NOTFOUND;
            EXECUTE IMMEDIATE V_SRC_CODE BULK COLLECT INTO L_KEYS USING V_DAY;
            FORALL x IN L_KEYS.FIRST..L_KEYS.LAST
                INSERT INTO TARGET (KEY, ITEM1, ITEM2) 
                VALUES((L_KEYS(x)), (SELECT ITEM1 FROM SOURCETBL WHERE SRC_CODE = V_SRC_CODE), (SELECT ITEM2 FROM SOURCETBL WHERE SRC_CODE = V_SRC_CODE));
                COMMIT;
    END LOOP;
    CLOSE RC;   
END;
cddau
  • 21
  • 3