Oracle SQL insert into table a user ID and a value of a list of strings

Oracle SQL insert into table a user ID and a value of a list of strings.

Asked on May 2, 2019 in C#.
Add Comment

  • 2 Answer(s)

    A user will have a list of strings that strings will be selected regions.These need to be saved into a table that keeps track of a user ID with a single region.

    It save in the front end that can be a user ID with a single region, or multiple.

    Answered on May 2, 2019.
    Add Comment

    Here’s a (PL/)SQL option you might want to consider.

    For testing purposes, I’ve created a TEST table which will contain the ID – VALUE pairs. The procedure accepts two parameters, both are strings:

    • ID will be common for all values
    • VALUE is a comma-separated values list. Although you can pass a collection, I’d suggest you to use VARCHAR2 as it is quite simple to maintain

    SELECT within the procedure uses a hierarchical query with regular expressions; its purpose is to split that comma-separated values string into rows so that you could insert each value into its own row. Doing so, you don’t even need a loop. Besides, that SELECT would work even if you run it standalone, but – you want a procedure.

    OK, here it goes:

    SQL> create table test (id varchar2(10), value varchar2(20));
    Table created.
    SQL> create or replace procedure p_ins (par_id    in varchar2,
      2                                     par_value in varchar2)
      3  is
      4  begin
      5    insert into test (id, value)
      6      select par_id,
      7             trim(regexp_substr(par_value, '[^,]+', 1, level))
      8      from dual
      9      connect by level <= regexp_count(par_value, ',') + 1;
     10  end;
     11  /
    Procedure created.


    SQL> begin
      2    p_ins('A', 'The, quick, brown fox, runs, or, whatever, it does');
      3  end;
      4  /
    PL/SQL procedure successfully completed.
    SQL> select * from test;
    ID         VALUE
    ---------- --------------------
    A          The
    A          quick
    A          brown fox
    A          runs
    A          or
    A          whatever
    A          it does
    7 rows selected.


    Answered on June 10, 2019.
    Add Comment

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.