Forum Stats

  • 3,827,925 Users
  • 2,260,839 Discussions
  • 7,897,407 Comments

Discussions

Need to detect the column while using sqlldr

3408387
3408387 Member Posts: 40

OS: WIN Database target: Oracle 12c Source is File. Using : sqlldr

I have a text data in this format. I have pasted the sample data in the description (please copy it to notepad++) i have huge data in this way which i would like to load it into the database table using sqlldr.

let me give you a brief of data TRN is where the transaction starts and TRNEND is where it ends.

The problem is- TRN1111111119134 record line where we have one column missing but that is present in transaction TRN1111111117134 column which is 114115, but my requirement here is to insert null into that column corresponding down to 114115 in first transaction when dealing with the second transaction but everything here is separated by white space(can we convert the white space delimiter to fixed one or other delimited one dynamically ?). Any advice would really be helpful. Thanks a lot.

The table definition for this case

    CREATE TABLE "R_F" (

         "COL1"     VARCHAR2(4000 BYTE),

         "COL2"     VARCHAR2(4000 BYTE),

         "COL3"     VARCHAR2(4000 BYTE),

         "COL4"     VARCHAR2(4000 BYTE),

         "COL5"     VARCHAR2(4000 BYTE),

         "COL6"     VARCHAR2(4000 BYTE),

         "COL7"     VARCHAR2(4000 BYTE),

         "COL8"     VARCHAR2(4000 BYTE),

         "COL9"     VARCHAR2(4000 BYTE),

         "COL10"    VARCHAR2(4000 BYTE),

         "COL11"    VARCHAR2(4000 BYTE),

         "COL12"    VARCHAR2(4000 BYTE),

         "COL13"    VARCHAR2(4000 BYTE),

         "COL14"    VARCHAR2(4000 BYTE),

         "COL15"    VARCHAR2(4000 BYTE),

         "COL16"    VARCHAR2(4000 BYTE),

         "COL17"    VARCHAR2(4000 BYTE),

         "COL18"    VARCHAR2(4000 BYTE),

         "COL19"    VARCHAR2(4000 BYTE),

         "COL20"    VARCHAR2(4000 BYTE),

         "COL21"    VARCHAR2(4000 BYTE),

         "COL22"    VARCHAR2(4000 BYTE),

         "COL23"    VARCHAR2(4000 BYTE),

         "COL24"    VARCHAR2(4000 BYTE),

         "COL25"    VARCHAR2(4000 BYTE),

         "COL26"    VARCHAR2(4000 BYTE),

         "COL27"    VARCHAR2(4000 BYTE),

         "COL28"    VARCHAR2(4000 BYTE),

         "COL29"    VARCHAR2(4000 BYTE),

         "COL30"    VARCHAR2(4000 BYTE))

SamplpeData:-

https://ufile.io/td0ty

I am trying to use below sqlldr control file command, do i missing something here ?

control Code :

<span class="pln" style="color: #303336;">infile </span><span class="str" style="color: #7d2727;">'D:\Source_files\LOG_07117_2017.DAT'</span><span class="pln" style="color: #303336;">   <br/></span><span class="kwd" style="color: #101094;">truncate</span><span class="pln" style="color: #303336;"> </span><span class="kwd" style="color: #101094;">into</span><span class="pln" style="color: #303336;"> </span><span class="kwd" style="color: #101094;">table</span><span class="pln" style="color: #303336;"> R_F<br/> fields terminated </span><span class="kwd" style="color: #101094;">by</span><span class="pln" style="color: #303336;"> WHITESPACE optionally enclosed </span><span class="kwd" style="color: #101094;">by</span><span class="pln" style="color: #303336;"> </span><span class="str" style="color: #7d2727;">'#'</span><span class="pln" style="color: #303336;"><br/> TRAILING NULLCOLS<br/> </span><span class="pun" style="color: #303336;">(</span><span class="pln" style="color: #303336;">  col1 </span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"> <br/>  col2 </span><span class="str" style="color: #7d2727;">"nvl(:col2,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col3 </span><span class="str" style="color: #7d2727;">"nvl(:col3,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"> <br/>  col4 </span><span class="str" style="color: #7d2727;">"nvl(:col4,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col5 </span><span class="str" style="color: #7d2727;">"nvl(:col5,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col6  </span><span class="str" style="color: #7d2727;">"nvl(:col6,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col7 </span><span class="str" style="color: #7d2727;">"nvl(:col7,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col8 </span><span class="str" style="color: #7d2727;">"nvl(:col8,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col9 </span><span class="str" style="color: #7d2727;">"nvl(:col9,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col10 </span><span class="str" style="color: #7d2727;">"nvl(:col10,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col11 </span><span class="str" style="color: #7d2727;">"nvl(:col11,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col12 </span><span class="str" style="color: #7d2727;">"nvl(:col12,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col13 </span><span class="str" style="color: #7d2727;">"nvl(:col13,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col14 </span><span class="str" style="color: #7d2727;">"nvl(:col14,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col15 </span><span class="str" style="color: #7d2727;">"nvl(:col15,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col16 </span><span class="str" style="color: #7d2727;">"nvl(:col16,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col17 </span><span class="str" style="color: #7d2727;">"nvl(:col17,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col18 </span><span class="str" style="color: #7d2727;">"nvl(:col18,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col19 </span><span class="str" style="color: #7d2727;">"nvl(:col19,'')"</span><span class="pun" style="color: #303336;">,</span><span class="pln" style="color: #303336;"><br/>  col20 </span><span class="str" style="color: #7d2727;">"nvl(:col20,'')"</span><span class="pun" style="color: #303336;"></span><span class="pun" style="color: #303336;">)</span><span class="pln" style="color: #303336;">  </span>

Message was edited by: 3408387

Tagged:

Best Answer

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 10, 2017 5:03PM

    Re: 2. How do I ask a question on the forums?

    > we have one column missing

    I see the words but don't know what they mean exactly.

    How will sqlldr recognize & understand that 1 column is missing?

    How will sqlldr recognize & understand which column is missing?

    Why is it sqlldr's job to overcome a problem that is NOT of its own making?

    Why don't YOU fix the data before it is accessed by sqllldr?

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
    edited May 10, 2017 4:28PM

    if a column is missing in your source file then this means your pattern in control file become invalid. so a dynamic column structure is not possible. you must re create your source file by using appropriate delimiter.

  • 3408387
    3408387 Member Posts: 40
    edited May 10, 2017 10:19PM

    Hi ,

    Thanks for response, really we do not have a control over the file some external systems would generate it, if lets say ID col has a value on trans1 then it will be available in file ithats first transaction in the file but for the 2nd transaction we dont have that data available so its not present.

  • 3408387
    3408387 Member Posts: 40
    edited May 10, 2017 10:19PM

    Hi ,

    Thanks for response, really we do not have a control over the file some external systems would generate it, if lets say ID col has a value on trans1 then it will be available in file ithats first transaction in the file but for the 2nd transaction we dont have that data available so its not present.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited May 10, 2017 11:01PM

    repeating the same words adds nothing of value.

    You have a mystery & provide us no clues.

    Unless & until you actually SHOW us what have & SHOW us what you desire & expect the results will be, you should not expect any meaningful response from here.

    Re: 2. How do I ask a question on the forums?

  • 3408387
    3408387 Member Posts: 40
    edited May 11, 2017 1:16AM

    Thanks for your help , have a good day.

  • Mustafa_KALAYCI
    Mustafa_KALAYCI Member Posts: 3,383 Bronze Crown
    edited May 11, 2017 2:09AM

    from where I am standing, it seems your data has not a specific format so it is not possible to define a pattern to insert these rows. what can you do? if you think you can create a pattern for your rows then insert whole data into a dummy table with a column varchar2 or clob and then parse it. this is something that I don't advise but if you can not define your data pattern you can not use sqlldr.

    3408387
  • 3408387
    3408387 Member Posts: 40
    edited May 11, 2017 3:56AM

    Hi, Thanks for valuable response,  I keep in mind about the definition of pattern is needed in sqlldr. i have only that much information to do the task nothing really more, my whole development process my view of data was wrong i thought it to be WHITESPACE separated but it looks like it is a fixed width file too. I might have to try this approach to handle in sqlldr.

  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited May 11, 2017 3:57AM

    Sorry, but I'm not opening files posted by unknown people on the internet. You'll get more help if you reformulate your problem into a form that can be posted simply into the forum - you don't need thirty columns and hundreds of rows to do that, just a handful of each would suffice.

    That said, it doesn't seem that your problem can be fixed programatically. Your control file says that each row of the data file contains up to thirty terms separated by spaces. Let's suppose that, in one row, the third term is missing - so you only get twenty-nine values loaded. How do you expect Oracle to know which term was missed out? How does a row with the third term missing differ from on with the fourth, or the ninth, or the thirtieth missing?

    SQL*Loader is a fairly dumb piece of software - it just reads files and loads them into tables, it doesn't have much scope for complicated logic. My suggestion would be that you just read the file into some staging table (or skip the SQL*Loader stage altogether and use an external table instead) and use PL/SQL to validate the incoming data, transferring good records to the destination table(s) and dealing appropriately with the bad ones.

    3408387
  • 3408387
    3408387 Member Posts: 40
    edited May 11, 2017 9:47AM Answer ✓

    Got that, might be another way of approach, I figured it out its surely fixed width file we can handle this in sqlldr only, Thanks for your time everyone.

This discussion has been closed.