1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

SQL * Loader

Discussion in 'General' started by kiran.marla, Oct 10, 2010.

  1. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi all,
    my requirement is as follows.
    Datfile
    1,2
    3,4
    5,6
    7,8

    i need to write control file such that data in table should look like this.
    my table has 4 columns. data is splitted into two rows in data file. and in a table it should arrange in this way.
    A B C D
    1 2 3 4
    5 6 7 8

    Can any body help me, how to write control file?

    Thanks
    Kiran Marla
     
  2. M_Anas_O

    M_Anas_O Forum Advisor

    Messages:
    95
    Likes Received:
    9
    Trophy Points:
    160
    Location:
    Austria
    Can you explain a bit more about your data and requirement?
    How many rows and columns are there in your original Data file and what is the required format.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Requirement is simple and clear.
    Legacy Dat file : contains 2 columns i.e 1 and 2 rows belong to 1st record. 3 and 4 belong to 2nd record
    input:
    1,2
    3,4
    5,6
    7,8

    Output:
    table contains 4 columns and n rows.
    data to be arranged in a table
    1 2 3 4
    5 6 7 8

    Clear?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Quite. You'll need to modify your data file somewhat to achieve the desired result but SQL*Loader can, and will, accomodate you. A hint to get you started: use the CONTINUEIF directive in the .ctl file to allow SQL*Loader to concatenate physical records into one logical record and add the 'trigger' character to the first line of data that defines each new logical record.

    If you still have trouble with this I'll be happy to provide a working example for you, but I'll need to see that you've tried to get this to work on your own.
     
    kiran.marla likes this.
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Surely David. I'll try with this.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Presuming you have a working example it should look similar to this:

    Code (SQL):
     
    SQL> CREATE TABLE ldrtest(
      2  col1 NUMBER,
      3  col2 NUMBER,
      4  col3 NUMBER,
      5  col4 NUMBER
      6  );
    TABLE created.
    SQL>
    SQL> -- CTL file contents
    SQL> !cat ldr_test.ctl
    LOAD DATA
    INFILE ldr_test.dat
    continueif (1)='*'
    INTO TABLE ldrtest
    FIELDS TERMINATED BY ','
    (col1, col2, col3, col4)
    SQL> -- DAT file
    SQL> !cat ldr_test.dat
    *1,2,
     3,4
    *5,6,
     7,8

    SQL> !sqlldr bing/bong ldr_test.ctl
    SQL*Loader: Release 10.2.0.4.0 - Production ON Wed Oct 13 00:15:00 2010
    Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
    Commit point reached - logical record COUNT 3
    SQL>
    SQL> SELECT * FROM ldrtest;
          COL1       COL2       COL3       COL4
    ---------- ---------- ---------- ----------
             1          2          3          4
             5          6          7          8
    SQL>

     
     
    kiran.marla likes this.
  7. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi David, I also got this with your guidance. we can also do the same with concatenate. My doubt is that ,
    1. what if we have some thousands of records? In real time we dont get 2 or 3 records.

    Kiran Marla
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is why SQL*Loader provides the CONTINUEIF directive, to create large logical records from piecemeal physical records:

    Code (SQL):
     
     SQL> CREATE TABLE ldrtest(
      2  col1 NUMBER,
      3  col2 NUMBER,
      4  col3 NUMBER,
      5  col4 NUMBER,
      6  col5 NUMBER,
      7  col6 NUMBER,
      8  col7 NUMBER,
      9  col8 NUMBER,
     10  col9 NUMBER,
     11  col10 NUMBER,
     12  col11 NUMBER,
     13  col12 NUMBER,
     14  col13 NUMBER,
     15  col14 NUMBER,
     16  col15 NUMBER,
     17  col16 NUMBER,
     18  col17 NUMBER,
     19  col18 NUMBER,
     20  col19 NUMBER,
     21  col20 NUMBER
     22  );
    TABLE created.
    SQL>
    SQL> -- CTL file contents
    SQL> !cat ldr_test2.ctl
    LOAD DATA
    INFILE ldr_test2.dat
    continueif (1)='*'
    INTO TABLE ldrtest
    FIELDS TERMINATED BY ','
    (col1, col2, col3, col4,col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20)
    SQL> -- DAT file
    SQL> !cat ldr_test2.dat
    *1,2,
    *3,4,
    *5,6,
    *7,8,
    *9,10,
    *11,12,
    *13,14,
    *15,16,
    *17,18,
     19,20
    SQL> !sqlldr bing/bong ldr_test2.ctl
    SQL*Loader: Release 10.2.0.4.0 - Production ON Wed Oct 13 08:43:23 2010
    Copyright (c) 1982, 2007, Oracle.  ALL rights reserved.
    Commit point reached - logical record COUNT 1
    SQL>
    SQL> SELECT * FROM ldrtest;
          COL1       COL2       COL3       COL4       COL5       COL6       COL7
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
          COL8       COL9      COL10      COL11      COL12      COL13      COL14
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
         COL15      COL16      COL17      COL18      COL19      COL20
    ---------- ---------- ---------- ---------- ---------- ----------
             1          2          3          4          5          6          7
             8          9         10         11         12         13         14
            15         16         17         18         19         20

    SQL>

     
     
    kiran.marla likes this.
  9. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi, i am very thankful to you.

    If we get plain data with out '*' prefixed, some thousands of records, record splitted into 2 rows. at this moment we cannot prefix '*' for every line manaully, and it will be a bulk process.
    In the case of outbound interface in Oracle apps, if it done with sql , at this moment , what to do ?
    I hope i am clear.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why is this data provided in such a format? It seems to be such a useless exercise, to provide apparently unusable data. Then, you don 't need to prefix the data with any character if you have in each logical record a recurring character at the same location in the first part of the data. For example:

    Code (SQL):
     
    SQL> -- CTL file contents
    SQL> !cat ldr_test3.ctl
    LOAD DATA
    INFILE ldr_test3.dat
    continueif (50)='Z'
    INTO TABLE ldrtest
    (name POSITION(1:40), emp_id POSITION(41:49), STATUS POSITION(50:50), hiredate POSITION(51:60))
    SQL> -- DAT file
    SQL> !cat ldr_test3.dat
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    BAILEY BOB 111234567ZA
    17-OCT-04
    SQL> !sqlldr bing/bong ldr_test3.ctl
    SQL*Loader: Release 10.2.0.4.0 - Production ON Wed Oct 13 15:41:46 2010
    Copyright (c) 1982, 2007, Oracle. ALL rights reserved.
    Commit point reached - logical record COUNT 16
    SQL>
    SQL> SELECT * FROM ldrtest;
    NAME EMP_ID S HIREDATE
    ---------------------------------------- ---------- - ----------
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    NAME EMP_ID S HIREDATE
    ---------------------------------------- ---------- - ----------
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    BAILEY BOB 111234567 A 10/17/2004
    16 ROWS selected.
    SQL>
     
    Notice the apparently 'useless' Z in the data; someone should be able to introduce such a 'useless' character in your data files which can be used as a CONTINUEIF character to allow the data to be loaded properly.
     
    kiran.marla likes this.
  11. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Thanks David.
    We must figure out some identifiers in order to combine the logical records, provided in the data file.