Need help with stored proc...driving me nuts
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!
Quotes, it's all about the quotes...
Try this, and next time please format your code:
Code:
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;
/
:rolleyes: