FORALL & INSERT
rbotlaMar 3 2005 — edited Mar 4 2005I am using FORALL to insert all the records in a plsql table to DB table. The order in which the columns are present in the PLSQL table is different from the order in which the columns are present in the DB table.
Is there anyway to specify particular column order??
CODE:
---------------------------------------------------
DECLARE
TYPE claimRec IS RECORD (
col1 NUMBER,
col2 NUMBER,
col3 NUMBER);
TYPE sample_tbl IS TABLE OF claimRec;
l_sample_tbl sample_tbl;
BEGIN
SELECT 1,
2,
3
BULK COLLECT INTO l_sample_tbl
FROM DUAL;
FORALL stg_index IN l_sample_tbl.FIRST .. l_sample_tbl.LAST
INSERT INTO DUMMY (
col4, -- <<<- Not allowing to these specify columns
col5,
col6)
VALUES (
l_sample_tbl(stg_index).col1,
l_sample_tbl(stg_index).col2,
l_sample_tbl(stg_index).col3
);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('EXCEPTION' || SQLERRM);
END;
-----------------------------------------------------
The above program is giving following errors
PLS-00436: implementation restriction; cannot reference fields of BULK In-BIND table of records
PLS-00382: expression is of wrong type