1 Reply Latest reply: Dec 5, 2013 1:37 PM by Barbara Boehmer RSS

    Sql Loader: Load multiple lines from a single line in input file

    user872430

      Hi everybody!

      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:

      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.

       

      Thanks in advance!

        • 1. Re: Sql Loader: Load multiple lines from a single line in input file
          Barbara Boehmer

          There are various methods that you could use.  It might be easiest to load it into a staging table, then parse it from there as demonstrated below.

           

           

          SCOTT@orcl12c> host type source.dat

          #ID; CARD_NUMBER;USERNAME

          1;86730975,86536522,86793501;JOHN SMITH

          2;89734562;MICHAEL ABOT

          3;87903546,87265390;JAMES ALBERT

           

          SCOTT@orcl12c> host type test.ctl

          load data

          infile source.dat

          into table staging

          fields terminated by ';'

          trailing nullcols

          (id, card_number, username)

           

          SCOTT@orcl12c> create table staging

            2    (id            number,

            3     card_number   varchar2(30),

            4     username      varchar2(30))

            5  /

           

          Table created.

           

          SCOTT@orcl12c> host sqlldr scott/tiger control=test.ctl log=test.log

           

          SQL*Loader: Release 12.1.0.1.0 - Production on Thu Dec 5 11:34:04 2013

           

          Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

           

          Path used:      Conventional

          Commit point reached - logical record count 4

           

          Table STAGING:

            3 Rows successfully loaded.

           

          Check the log file:

            test.log

          for more information about the load.

           

          SCOTT@orcl12c> select * from staging

            2  /

           

                  ID CARD_NUMBER                    USERNAME

          ---------- ------------------------------ ------------------------------

                   1 86730975,86536522,86793501     JOHN SMITH

                   2 89734562                       MICHAEL ABOT

                   3 87903546,87265390              JAMES ALBERT

           

          3 rows selected.

           

          SCOTT@orcl12c> create table destination

            2    (id            number,

            3     card_number   varchar2(30),

            4     username      varchar2(30))

            5  /

           

          Table created.

           

          SCOTT@orcl12c> insert into destination

            2  select id,

            3         regexp_substr (card_number || ',', '[^,]+', 1, column_value),

            4         username

            5  from   staging,

            6         table

            7           (cast

            8             (multiset

            9               (select level

          10                from   dual

          11                connect by level <= regexp_count (card_number, ',') + 1)

          12            as sys.odcinumberlist))

          13  /

           

          6 rows created.

           

          SCOTT@orcl12c> select * from destination

            2  /

           

                  ID CARD_NUMBER                    USERNAME

          ---------- ------------------------------ ------------------------------

                   1 86730975                       JOHN SMITH

                   1 86536522                       JOHN SMITH

                   1 86793501                       JOHN SMITH

                   2 89734562                       MICHAEL ABOT

                   3 87903546                       JAMES ALBERT

                   3 87265390                       JAMES ALBERT

           

          6 rows selected.