Need help with stored proc...driving me nuts

[LKBrwn_DBA]

Try this, and next time please format your 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;
/