Oracle-Insert Comma separated string values into Table --

Question

Trying to insert an entire row which is accepted by procedure input parameter as comma separated string 'THIS,IS,AN,Example,null,STRING' into table

Here is what I have tried:

DECLARE
  I_NPUT VARCHAR2(4000) := 'THIS,IS,AN,Example,null,STRING# This , is ,an,another,ex,null#'The,list,goes,on,on,null'';
  L_COUNT BINARY_INTEGER;
  L_ARRAY DBMS_UTILITY.LNAME_ARRAY;
BEGIN
  DBMS_UTILITY.COMMA_TO_TABLE(LIST => REGEXP_REPLACE(I_NPUT, '(^|,)', '\1x'), TABLEN => L_COUNT, TAB => L_ARRAY);
  DBMS_OUTPUT.PUT_LINE(L_COUNT);
  FOR I IN 1 .. L_COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('Element ' || TO_CHAR(I) || ' of array contains: ' || SUBSTR(L_ARRAY(I), 2));
    INSERT INTO TEST VALUES
      (SUBSTR(L_ARRAY(I), 2)
      );
    COMMIT;
  END LOOP;
END;

The return needs to be:

COL_1    COL_2   COL_3  COL_4   COL_5  COL_6
-----------------------------------------------
THIS     IS      AN     Example null   STRING

How about you show us what you tried..? We're not here to write software for you, we're here to help you with problems you can't figure out. — Rob
– Rob, Commented Oct 27, 2015 at 11:01
Typically, each element in the list goes into a single varchar2 column. You have written this so that you are attempting to insert each element in its own column (not very workable if you do not know how many elements are in your list). Do you really need all elements to go into one record? Do you really know the number of elements in your list? — Patrick Bacon
– Patrick Bacon, Commented Oct 27, 2015 at 17:32
if my string input is this 'THIS,IS,AN,Example,null,STRING# This , is ,an,another,ex,null#'The,list,goes,on,on,null' the row is distinguished by # it need to insert into table row wise. — Nancy Guruswamy
– Nancy Guruswamy, Commented Oct 28, 2015 at 5:54

Tatiana · Accepted Answer · 2015-10-27 12:16:15Z

1

sss := replace(p_string,',',''',''');
sss := regexp_replace(sss, '''null''','null');

EXECUTE IMMEDIATE 'INSERT INTO your_table (COL_1, COL_2, COL_3, COL_4, COL_5, COL_6) 
VALUES (''' ||sss|| ''')';

Here p_string - your input string

answered Oct 27, 2015 at 12:16

Tatiana

1,50910 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle-Insert Comma separated string values into Table --

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related