I am unable to find proper syntax on usage of VARRAYs.
I want to cycle I1 through I8 column names for the command below.
This thing is now full of errors due to my ignorance of VARRAYs.
First I try to clean up errors in defining then I'll modify the references to I1 in the MERGE command.
I have just noticed that ARRAY may suit me better, but I don't know enough to figure which is best for me to use.
Bottom line is to merge 8 different columns into one data column in an Oracle table. Source is comma delimited flat file that has the colums #13 thru 19.
Rich

DECLARE
CREATE TYPE colX IS VARRAY(8) OF VARCHAR2(2);
colm colx := 'I1','I2','I3','I4', 'I5','I6','I7','I8';
BEGIN
FOR j IN 1..8 LOOP
MERGE INTO interests m4
using (select distinct I1 from flataim
where I1 IS NOT NULL ) m2
on (m4.interest = m2.I1 )
when not matched then insert (m4.iid, m4.interest )
values (aimintsq.nextval, m2.I1) ;
END LOOP;
END;
/