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!

Collections: FORALL insert: Order of columns

v5178Jan 10 2022 — edited Jan 10 2022

I'm trying to insert few records from a staging table. But the data is getting inserted into incorrect columns.
Irrespective of the order of columns in the OPEN CURSOR, how can I make sure data is inserted into the right columns?
Please use below code to replicate the issue.

CREATE TABLE test_address (city_name VARCHAR2(100), state_name VARCHAR2(100), zipcode VARCHAR2(100));
INSERT INTO test_address (city_name, state_name, zipcode) VALUES ('Richmond', 'VA', '32784');
INSERT INTO test_address (city_name, state_name, zipcode) VALUES ('Nashville', 'TN', '96587');
INSERT INTO test_address (city_name, state_name, zipcode) VALUES ('Huntsvill', 'AL', '85758');
INSERT INTO test_address (city_name, state_name, zipcode) VALUES ('Altanta', 'GA', '36854');
INSERT INTO test_address (city_name, state_name, zipcode) VALUES ('Troy', 'MI', '45786');
INSERT INTO test_address (city_name, state_name, zipcode) VALUES ('Kansas', 'MO', '64487');

CREATE TABLE address_info (zipcode VARCHAR2(100), city_name VARCHAR2(100), state_name VARCHAR2(100));

SET SERVEROUTPUT ON;
DECLARE
  TYPE address_typ IS TABLE OF test_address%rowtype INDEX BY BINARY_INTEGER;
  address_tbl address_typ; 
  TYPE address_c IS REF CURSOR;
  address_cur address_c;
BEGIN
  OPEN address_cur FOR 'SELECT zipcode, city_name, state_name from test_address';
  LOOP
    FETCH address_cur BULK COLLECT INTO address_tbl LIMIT 2;
    FORALL I IN 1..address_tbl.COUNT
      INSERT INTO address_info(zipcode, city_name, state_name) 
      VALUES (address_tbl(I).zipcode, address_tbl(I).city_name, address_tbl(I).state_name);
      COMMIT;
      EXIT WHEN address_cur%notfound;
  END LOOP;
END;
/

Output I see (select * from address_info):
image.pngExpected output:
image.png
Working on:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL Developer v21
WIndows 10

Comments
Post Details
Added on Jan 10 2022
8 comments
1,924 views