CODEWITHSUNDEEP
regexoracle-databasestored-proceduresplsql
Laxmikant
Laxmikant

Reputation: 1611

Split delimiter separated string and insert into a table in oracle 11

I have delimiter separated input string which can have around 40 tokens (the number may grow), I want to insert these value into a table using stored procedure in oracle 11; What is the best way of doing it

  1. create an SP with 40 IN parameters and use it to insert.
  2. Create an SP with 1 IN parameter which will take that string and split the delimiter separated tokens and insert them into a table

If 2nd approach looks good then please suggest how to achieve it??

For example if a string is like "abc,123,xyz,pqr,12" (here delimiter is comma) so after running the SP my table table1(A varchar2, B Number, C varchar2, D varchar2, E number ) should have entry like

A  | B | C | D | E
abc|123|xys|pqr |12

I came up with below solution be not sure about performance , is there any better way to do the same ?

declare
  string_to_parse varchar2(2000) := 'abc,123,xyz,pqr,12';
  A varchar2(4);
  B number;
  C varchar2(4);
  D varchar2(4);
  E number;
begin

  string_to_parse := string_to_parse||',';

   A  := REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 1);
   B  := TO_NUMBER(REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 2));
   C  := REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 3);
   D  := REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 4);
   E  := TO_NUMBER(REGEXP_SUBSTR(string_to_parse,'[^,]+', 1, 5));
   dbms_output.put_line('A ' || A || ' B ' || B || ' c ' || c || ' D ' || D || ' E ' || E);
--insert into table
end;

Upvotes: 0

Views: 1306

Answers (2)

diziaq
diziaq

Reputation: 7785

In this particular case the splitting is a long way to the goal. Considering that a target table could have a lot of columns (yes, 5 is to much to handle each of them in a distinct variable), I would suggest using schema dictionary to throw in a bit of flexibility.

Let's look at a procedure that takes two parameters: a table name and a string containing list of comma separated values. Here it's supposed that table has only string, numeric and time columns. To implement a complete version add handling of all required data types in the beginning of procedure.

Pay attention that in the middle we split a string into a table of substrings using standard SQL approach:

select level as column_id, 
       REGEXP_SUBSTR(pi_values_list, '[^,]+', 1, level) as column_val 
  from dual connect by REGEXP_SUBSTR(pi_values_list, '[^,]+', 1, level) is not null;

Here is the whole procedure:

  create or replace procedure myInsertInto(pi_table_name  char,
                                           pi_values_list char)
  is
    v_statement     varchar2(30000) := 'INSERT INTO %TABLE_NAME% (%COLUMNS_LIST%) VALUES (%VALUES_LIST%)';
    v_columns_list  varchar2(10000);
    v_values_list   varchar2(10000);
  begin

    SELECT LISTAGG(T.column_name, ',') within group (order by T.column_id) ,
           LISTAGG( -- implement specific types handling here
                    CASE
                    WHEN S.column_val IS NULL
                      THEN 'NULL'
                    WHEN T.data_type = 'NUMBER'
                      THEN S.column_val
                    WHEN T.data_type IN ('DATE', 'TIMESTAMP') 
                      THEN 'TIMESTAMP ''' || S.column_val || ''''
                    WHEN T.data_type like '%CHAR%' 
                      THEN '''' || S.column_val || ''''                    
                    ELSE 'NULL'
                    END, 
           ',') within group (order by T.column_id)
    into v_columns_list,
         v_values_list
    from user_tab_cols T,
         (select level as column_id, REGEXP_SUBSTR(pi_values_list, '[^,]+', 1, level) as column_val 
            from dual connect by REGEXP_SUBSTR(pi_values_list, '[^,]+', 1, level) is not null) S
   where T.table_name = pi_table_name
     and T.column_id = S.column_id;

    if v_columns_list IS NULL then
      raise_application_error(-20000, 'Not found columns for table ' || pi_table_name);
    end if;

    -- finalizing the statement
    v_statement := replace(v_statement, '%TABLE_NAME%', pi_table_name);      
    v_statement := replace(v_statement, '%COLUMNS_LIST%', v_columns_list);      
    v_statement := replace(v_statement, '%VALUES_LIST%', v_values_list);

    execute immediate v_statement;
  end;
  /

Then use it like this

create table MY_TABLE (
  col_a VARCHAR2(10),
  col_b NUMBER,
  col_c VARCHAR2(10),
  col_d DATE,
  col_E VARCHAR2(10) default 'DEFAULT'
);



begin
  myInsertInto('MY_TABLE', 'abc,123,xyz,2018-01-02 23:01:10,pqr' );
  myInsertInto('MY_TABLE', 'def,345,mkr' );
  myInsertInto('MY_TABLE', 'fgh' );
end;
/

Upvotes: 0

piezol
piezol

Reputation: 963

The first approach is a no-no.

The second may work. Simply:

  1. Assign your input string to variable s.

Now, in loop:

  1. Exit the loop, if length of the s is 0
  2. Find the first occurence of your delimiter (',') with instr. Assign it to X
  3. If X = 0, then X := len(string) + 1
  4. X := X - 1
  5. If X > 0, then insert the substr(s, 1, X) into your table
  6. If X > 0, then s := substr(s, X+1, len(s))

I didn't test it and there are obvious ways to optimize it (for example - instead of assigning substring back to s, you could store 'left end index' of currently parsed part.

But there is better approach - to do in pure sql. Unfortunately, I don't know, whether your oracle version supports all the features, but give a try to this select:

with 
my_input_string as (
   select 'my,delimited,,,,,,input,string' s from dual
),
string_to_rows as (
   select trim(regexp_substr(s, '[^,]+', 1, LEVEL)) col 
    from my_input_string
 connect by instr(my_input_string.s, ',', 1, LEVEL - 1) > 0
)
select *
  from string_to_rows
 where col is not null

If it works (and by 'works' i mean - returns four rows), just use this in your insert. Replace hardcoded string with procedure's parameter and that's it.

Upvotes: -1

Related Questions