sql loader load multiple lines from one line

Question

I'm facing an issue loading a file with sql loader.

This is what my input file looks like:

#ID; CARD_NUMBER;USERNAME  
1;86730975,86536522,86793501;JOHN SMITH  
2;89734562;MICHAEL ABOT  
3;87903546,87265390;JAMES ALBERT  

And what I want to acheive after loading is this:

#Table data
1;86730975;JOHN SMITH  
1;86536522;JOHN SMITH  
1;86793501;JOHN SMITH  
2;89734562;MICHAEL ABOT  
3;87903546;JAMES ALBERT  
3;87265390;JAMES ALBERT  

I did something like this before using Sql Loader, but I can't find the source code. So if someone can help that'll be very nice for me.

I'm new to this forum and I don't know how to find all the posts related to my username, I'll appreciate if someone can help on that to.

Answer 1

You can go with external tables. To me personally it'll be a more convenient way to get it done:

1. Create a directory in Oracle that points to the exact location of the file on the server.

   SQL> create or replace directory ff1 as 'c:\';
   
   directory created
   

2. Create external table similar to this one:

   SQL> create table t1(
     2    t1_id      number,
     3    card_no    varchar2(4000),
     4    user_name  varchar2(4000)
     5  )
     6  organization external(
     7    type oracle_loader
     8    default directory ff1
     9    access parameters(
    10      records delimited by newline
    11      fields terminated by ';'
    12    )
    13    location('input.dat') --<-- file name
    14  ) reject limit unlimited
    15  ;
   
    Table created.
   

After that, you can query that external table, and use any (there are many of them) method to split comma separated string. In this case it would be values of card_number. For example:

This is the actual data:

SQL> column card_no format a40;
SQL> column user_name format a17;

SQL> select * from t1;

     T1_ID CARD_NO                                  USER_NAME
---------- ---------------------------------------- -----------------
         1 86730975,86536522,86793501               JOHN SMITH
         2 89734562                                 MICHAEL ABOT
         3 87903546,87265390                        JAMES ALBERT

3 rows selected.

Transformed version of actual data:

SQL> column card_no format 9999999999;

SQL> with ocrs(ocr) as(
  2    select level
  3      from ( select max(regexp_count(card_no, '[^,]+')) as mx
  4               from t1 ) s
  5     connect by level <= s.mx
  6  )
  7  select *
  8    from (select t.t1_id
  9               , to_number(regexp_substr(t.card_no, '[^,]+', 
 10                                         1, o.ocr)) as card_no
 11               , t.user_name
 12            from t1 t
 13           cross join ocrs o ) w
 14    where w.card_no is not null
 15   order by w.t1_id;

Result:

      T1_ID     CARD_NO USER_NAME
----------- ----------- -----------------
          1    86730975 JOHN SMITH
          1    86793501 JOHN SMITH
          1    86536522 JOHN SMITH
          2    89734562 MICHAEL ABOT
          3    87265390 JAMES ALBERT
          3    87903546 JAMES ALBERT

6 rows selected.

Having done that, you can use simple insert statement combined with the above query to insert new, transformed data into another table.

 insert into another_table(id, card_number, user_name)
    with ocrs(ocr) as(
      select level
    .....  
    /* the above query */