Need help with stored proc...driving me nuts
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Need help with stored proc...driving me nuts

  1. #1
    Join Date
    Nov 2010
    Posts
    2

    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!

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,443

    Wink 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

  3. #3
    Join Date
    Nov 2010
    Posts
    2

    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
  •  



Click Here to Expand Forum to Full Width