-
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;
/
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Thanks!
That did the trick thanks so much for responding!
Sorry about the code formatting - I'll make sure to remember next time
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|