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):
Expected output:

Working on:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL Developer v21
WIndows 10