Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FORALL & INSERT

rbotlaMar 3 2005 — edited Mar 4 2005
I 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


Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 1 2005
Added on Mar 3 2005
6 comments
1,129 views