Storing comma separated value into a table

Question

I have an application wherein the user enters the inputs in comma separated format from UI and i have to capture those values and insert into a database tables in the form of records. For example the user enters ('p1,p2,p3,p4') and it will be stored in table as

ID   Value
1    p1
2    p2
3    p3
4    p4

I need to implement this using associative array?

If the ID column is just a sequence, you don't need an associative array to accomplish this. — EkoostikMartin, May 15 '14 at 19:27
possible duplicate of [How to best split csv strings in oracle 9i](http://stackoverflow.com/questions/1089508/how-to-best-split-csv-strings-in-oracle-9i) — abhi, May 15 '14 at 19:50
Are you **asking us** if you *should* use an associative array, or **telling us** that you *must* use an associative array? — Bob Jarvis - Слава Україні, May 15 '14 at 20:27

score 0 · Answer 1 · answered Aug 02 '16 at 06:45

You can create a type of array of varchar2(100):

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);

Then create a function returning this type from an inputted p_list string 'valsepvalsepvalsep' (c2t for char to table):

CREATE OR REPLACE
FUNCTION c2t(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_list
AS
  l_string VARCHAR2(32767) := p_list || p_sep;
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_my_list     := t_my_list();
BEGIN
  LOOP
    l_sep_index := INSTR(l_string, p_sep, l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + 1;
  END LOOP;
  RETURN l_tab;
END c2t;
/

Here is how you call the above:

select cto_table(',', 'p1,p2,p3') from dual;

This gives you a "collection". You make a table with it with the built-in table(..) function like this:

select rownum, column_value from table(c2t(',', 'p1,p2,p3')) ;

Storing comma separated value into a table

1 Answers1