Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: SQL Loader Help

  1. #1
    Join Date
    Jul 2004
    Posts
    102

    Unanswered: SQL Loader Help

    Dear all,

    I want to Load a txt file to my Table.....

    My txt file data look like below :

    03F5431S041720080513
    98K5421S041820080513
    99E5312L053120080513
    99H5324L053820080513
    00E5335L053920080513
    07H5725K054520080513
    97K5412S054720080513
    04G5330L055720080513

    My table structure looks like :

    Table name Att01

    ID VARCHAR2(8 BYTE)
    TM NUMBER(5,2)
    WD DATE


    Pls help me by providing the code of the .ctl file
    Working Together...

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    What did you try so far?

    Did you read this chapter in the manual (especially the section labelled "Specifying the Position of a Data Field") which describes the definition of the columns?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If your database version supports it, you might use input file as an external table and use a simple SQL script to manipulate such a record structure.

  4. #4
    Join Date
    Jul 2004
    Posts
    102
    Quote Originally Posted by Littlefoot
    If your database version supports it, you might use input file as an external table and use a simple SQL script to manipulate such a record structure.
    Thanks you for your response....

    I'm using Db 9.2.0 .I have to load this type of data everyday. would you pls explain how to use external table on this issue and what is the SQL script.

    If my DB is not suported then what i have to do ???

    Thanks in advance for your promot response....
    Last edited by Momin; 05-17-08 at 06:38.
    Working Together...

  5. #5
    Join Date
    Jul 2004
    Posts
    102
    Quote Originally Posted by shammat
    What did you try so far?

    I'm trying this.........
    LOAD DATA
    INFILE '/home/oracle/BDT_TXT.txt'
    INTO TABLE Att01
    (ID position(1:8),
    tm position(9:12) "to_number(:tm,'99D99')",
    wd position(13:20) date 'yyyymmdd'
    )
    Last edited by Momin; 05-17-08 at 07:00.
    Working Together...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    External tables are available in 10g so - you can't use them.

    Your control file looks quite well; why don't you like it? Except of unnecessary TO_NUMBER function with the "tm" column, it does the job.
    Code:
    LOAD DATA
    INFILE *
    REPLACE
    INTO TABLE att01
    (
    ID position (1:8),
    tm position (9:12),
    wd position (13:20) date "yyyymmdd"
    )
    
    begindata
    03F5431S041720080513
    98K5421S041820080513
    99E5312L053120080513
    99H5324L053820080513
    00E5335L053920080513
    07H5725K054520080513
    97K5412S054720080513
    04G5330L055720080513
    Code:
    C:\TEMP>sqlldr scott/Tiger control=test.ctl log=test.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on Sub Svi 17 12:27:11 2008
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 7
    Commit point reached - logical record count 8
    Code:
    SQL> select * From att01;
    
    ID               TM WD
    -------- ---------- --------
    03F5431S        417 13.05.08
    98K5421S        418 13.05.08
    99E5312L        531 13.05.08
    99H5324L        538 13.05.08
    00E5335L        539 13.05.08
    07H5725K        545 13.05.08
    97K5412S        547 13.05.08
    04G5330L        557 13.05.08
    
    8 rows selected.
    
    SQL>

  7. #7
    Join Date
    Jul 2004
    Posts
    102
    Thanks again for your fast support.....

    Btw man I need TM field with decimal option.

    Data should be look like the below.....


    SQL> select * From att01;

    ID TM WD
    -------- ---------- --------
    03F5431S 4.17 13.05.08
    98K5421S 4.18 13.05.08
    99E5312L 5.31 13.05.08
    99H5324L 5.38 13.05.08
    00E5335L 5.39 13.05.08
    07H5725K 5.45 13.05.08
    97K5412S 5.47 13.05.08
    04G5330L 5.57 13.05.08

    8 rows selected.

    SQL>
    Hope this is possible......
    Working Together...

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Oh, I see; just divide "TM" with 100, such as
    Code:
    tm position (9:12) ":tm / 100"

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Momin
    I need TM field with decimal option.
    Not sure, but you coud try:
    Code:
    LOAD DATA
    INFILE '/home/oracle/BDT_TXT.txt'
    INTO TABLE Att01
    (ID position(1:8),
    tm position(9:12) "to_number(:tm,'9999')/100",
    wd position(13:20) date 'yyyymmdd'
    )
    Haven't tried this though

  10. #10
    Join Date
    Jul 2004
    Posts
    102
    thank u all...

    I'll chk that and inform u the result.......Thanks for your continous support.
    Working Together...

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Shammat, there's no need for TO_NUMBER; all these values already ARE numbers. Right?

  12. #12
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Littlefoot
    Shammat, there's no need for TO_NUMBER; all these values already ARE numbers. Right?
    From SQL*Loader's point of view I think everything is character data in the first place (I might be wrong with that!).
    It is pretty smart about converting the values to the target datatype and I usually let SQL*Loader handle that (except for dates).

    But when you need to "do something" (e.g. divide by 100) with the values, my experience is, that it's more reliable to first properly cast the character value.

    You might get away without it though.

  13. #13
    Join Date
    Jul 2004
    Posts
    102
    Thanks all for your help....

    Two more thing to know....

    when i generate a txt file from barcode reader, it give me new name each day, so what to do ???

    LOAD DATA
    INFILE '/home/oracle/BDT_TXT.txt'
    INTO TABLE Att01
    (ID position(1:8),
    tm position(9:12) "to_number(:tm,'9999')/100",
    wd position(13:20) date 'yyyymmdd'
    )
    I have to edit the CTL file each time when i want to load data or any easy method ???

    Is it possible to create a form and load data from client end ????? If possibe then, pls describe....

    Sorry for my poor english.....
    Working Together...

  14. #14
    Join Date
    Jul 2004
    Posts
    102
    Pls Help me..............I need this one
    Working Together...

  15. #15
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >when i generate a txt file from barcode reader, it give me new name each day, so what to do ???
    If you don't know what to do, why do you expect us to know?
    What are the requirements need to be met?

    >I have to edit the CTL file each time when i want to load data or any easy method ???
    Edit & change what to what & why?

    >Is it possible to create a form and load data from client end ????? If possibe then, pls describe....
    NO
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •