It's been a while since I've had to write any pl/sql code. I've been banging my head against this problem for hours and can't figure it out.

Basically what I'm trying to do is copy the records in a table with a bunch of columns into a key-value pair format. The key-value formatted table is defined as:

SQL> desc contactskvp;
Name Null? Type
----------------------------------------- -------- ----------------------------
CONTACT VARCHAR2(100)
KEY VARCHAR2(20)
VALUE VARCHAR2(4000)


So I tried starting with a simple stored procedure:

create or replace procedure ConvertToKvp as

TYPE loc_array_type IS TABLE OF VARCHAR2(40);
dml_str1 varchar2(200);
loc_array loc_array_type;
my_rec user_tab_columns.column_name%type;


BEGIN
-- bulk fetch emtsearchablecontact column names
SELECT column_name BULK COLLECT INTO loc_array
FROM user_tab_columns where table_name='EMTSEARCHABLECONTACTS' and column_name not in ('ID');
FOR i in loc_array.first..loc_array.last LOOP
select column_name into my_rec from user_tab_columns where table_name='CONTACTTABLE' and column_name = loc_array(i);
dml_str1 := 'INSERT INTO CONTACTSKVP(contact,key,value) select id,'||my_rec||','||loc_array(i)
||' FROM CONTACTTABLE WHERE '||loc_array(i)
||' IS NOT NULL';
execute immediate dml_str1;
commit;
END LOOP;
END;
/

The procedure compiles fine and executes ok but when I look at the data I expect to see something like

CONTACT,KEY,VALUE

1,TEXT121,Jim
1,TEXT122,Taylor
1,TEXT141,93 E. Sycamore St.

but what I'm getting instead is:

1,Jim,Jim
1,Taylor,Taylor
1,93 E. Sycamore St., 93 E. Sycamore St.

Any ideas what is happening here? I'm not even sure how my_rec could be getting the column value from contacttable when it's query is selecting from user_tab_columns.

Also if you could give me an idea of what I'd have to do to get the expected behavior it would be greatly appreciated.

Thanks!