Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    11

    Unanswered: having a conditional load using sqlldr in oracle

    I have a txt file as follows. 1st machine_no,2nd emp_no, 3rd
    shift_type (1 for entry,3 for exit), 4th work_date, 5th is time.

    001,0000000021,01,2011/06/21,06:50,
    001,0000000026,01,2011/06/21,14:00,
    001,0000000018,01,2011/06/21,07:00,
    001,0000000021,03,2011/06/21,14:00,
    001,0000000018,03,2011/06/21,16:50,
    001,0000000026,03,2011/06/21,16:55,
    The table as follows:
    MACHIAN VARCHAR2(4)
    YEAR NUMBER(4)
    MONTH VARCHAR2(2)
    WDAY VARCHAR2(2)
    DAY_NO NUMBER(1)
    TIME1 VARCHAR2(5)
    TIME2 VARCHAR2(5)
    TIME3 VARCHAR2(2)
    SHIFT_NO NUMBER(1)
    TIME_TYPE NUMBER(1)
    WORK_DATE DATE
    EMP_NO VARCHAR2(10)

    I want to load the data in the table in the following way. The time1 field to have time if time_type=1 and time2 field to have time if time_type=3 for
    each emp_no.
    Please let me know how I can have this in the control file.
    Thanks in advance...Macky

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What a mess! Your table description differs from your explanation; moreover, explanation is inconsistent (shift_type doesn't exist in a table; then you use time_type that doesn't exist in the input data ...). Can't you pay more attention to what you do? How do you expect some help with such an attitude?

    Anyway: here's an example of how to do that. Adjust it to your real situation, I have no idea how it really looks like.

    A control file:
    Code:
    load data
    infile *
    replace
    into table test
    fields terminated by ","
    (machian,
     emp_no,
     shift_no,
     work_date "to_date(:work_date, 'yyyy/mm/dd')",
     time1 "decode(:shift_no, 1, :time1, null)", 
     time2 "decode(:shift_no, 3, :time1, null)" 
    )
    
    begindata
    001,0000000021,01,2011/06/21,06:50, 
    001,0000000026,01,2011/06/21,14:00, 
    001,0000000018,01,2011/06/21,07:00, 
    001,0000000021,03,2011/06/21,14:00, 
    001,0000000018,03,2011/06/21,16:50, 
    001,0000000026,03,2011/06/21,16:55,
    Execution and the result:
    Code:
    SQL> $sqlldr scott/Tiger@ora10 control=test.ctl log=test.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on ╚et Ruj 22 14:16:31 2011
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 5
    
    SQL> select machian, emp_no, shift_no, work_date, time1, time2
      2  from test;
    
    MACH EMP_NO       SHIFT_NO WORK_DAT TIME1 TIME2
    ---- ---------- ---------- -------- ----- -----
    001  0000000021          1 21.06.11 06:50
    001  0000000026          1 21.06.11 14:00
    001  0000000018          1 21.06.11 07:00
    001  0000000021          3 21.06.11       14:00
    001  0000000018          3 21.06.11       16:50
    
    SQL>

  3. #3
    Join Date
    Jun 2011
    Posts
    11

    having a conditional load using sqlldr in oracle

    hi..Sorry for the confusion.

    Thanks for your advice. The control file as per your adivce returned this.

    MACH EMP_NO SHIFT_NO WORK_DAT TIME1 TIME2
    ---- ---------- ---------- -------- ----- -----
    001 0000000021 1 21.06.11 06:50
    001 0000000026 1 21.06.11 14:00
    001 0000000018 1 21.06.11 07:00
    001 0000000021 3 21.06.11 14:00
    001 0000000018 3 21.06.11 16:50

    Can I get as below?
    MACH EMP_NO WORK_DAT TIME1 TIME2
    ---- ---------- ---------- -------- ----- -----
    001 0000000021 21.06.11 06:50 14:00
    001 0000000026 21.06.11 14:00
    001 0000000018 21.06.11 07:00 16:50

    Any way to do it. I can't use external table coz im doing it in oracle 8i.

    REgards,
    MAC

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't think that SQL*Loader is capable of doing this kind of a job. But, you can always load data "as is" into a stagging table, and then - using your (PL/)SQL skills - load the "real" table.

Posting Permissions

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