This discussion is archived
10 Replies Latest reply: Nov 4, 2012 10:11 PM by hemu RSS

sqlldr

hemu Explorer
Currently Being Moderated
hi
here is awhat i am trying to do
--alm_test.ctl
load data
INFILE 'G:\hrk\30SEPT2012\300912LN.002'
append
INTO TABLE alm_test
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
 BAL_DATE1  date 'dd/mm/yyyy'     ,
 BRCODE         ,
 HOCODE         ,
 ACNO           ,
 F1             ,
 SANC_DATE  date 'dd/mm/yyyy'    ,
 EFF_DATE   date 'dd/mm/yyyy'    ,
 INST_DATE  "decode(to_char(:INST_DATE,'dd/mm/yyyy'),'00/00/0000',to_date('01/01/1900','dd/mm/yyyy'),:INST_DATE)"  ,
 EXP_DATE   date 'dd/mm/yyyy'    ,
 RATE           ,
 RMODE          ,
 INST           ,
 LIMIT          ,
 EQUTEDYN       ,
 BORR_CODE    )
data i am trying to insert is as follows
bal_date1|brcode|hocode|acno  |f1           |sanc_date   |eff_date    |inst_date    |exp_date    | rate |...
30/09/2012|BD      |BD      |       1|00000000|04/11/2011|04/11/2011|00/00/0000|31/10/2012|13.00 | |+000000000000.00|+000004000000.00|N|7
30/09/2012|CC      |CC      |      33|00000000|10/11/2011|10/11/2011|00/00/0000|31/10/2012|17.50 | |+000000000000.00|+000000300000.00|N|2
30/09/2012|CC      |CC      |      64|00000000|07/01/2012|07/01/2012|07/01/2012|31/12/2012|16.00 | |+000000000000.00|+000000250000.00|N|2
30/09/2012|CC      |CC      |     150|00000000|27/07/2012|19/08/2012|00/00/0000|31/07/2013|16.00 | |+000000000000.00|+000003500000.00|N|7
30/09/2012|CC      |CC      |     154|00000000|00/00/0000|00/00/0000|00/00/0000|00/00/0000|00.00 | |+000000000000.00|+000000000000.00|N|2
30/09/2012|CC      |CC      |     156|00000000|00/00/0000|00/00/0000|00/00/0000|00/00/0000|00.00 | |+000000000000.00|+000000000000.00|N|7
30/09/2012|CC      |CC      |     171|00000000|27/07/2012|19/08/2012|00/00/0000|31/07/2013|16.00 | |+000000000000.00|+000005000000.00|N|7
30/09/2012|CC      |CC      |     172|00000000|12/01/2012|14/01/2012|00/00/0000|31/12/2012|18.50 | |+000000000000.00|+000000175000.00|N|2
sqlldr is giving me error as follows
value used for ROWS parameter changed from 64 to 20
Record 1: Rejected - Error on table ALM_TEST, column INST_DATE.
ORA-01722: invalid number
please help

Edited by: hemu on 03-Nov-2012 23:17
  • 1. Re: sqlldr
    586006 Newbie
    Currently Being Moderated
    Hi Hemu,

    Please try this at INST_DATE.

    decode(to_char(to_date(:INST_DATE,'dd/mm/yyyy')),'00/00/0000',to_date('01/01/1900','dd/mm/yyyy'),(to_date(:INST_DATE,'dd/mm/yyyy')))

    However, as I can see you are not getting proper data for INST_DATE. You are getting 00/00/0000. Please reconsider changing it to some proper valid date.

    Edited by: 583003 on Nov 4, 2012 11:59 AM
  • 2. Re: sqlldr
    hemu Explorer
    Currently Being Moderated
    hi
    i changed my ctl as suggested
    and now i get error as follows
    Record 1: Rejected - Error on table ALM_TEST, column INST_DATE.
    ORA-01847: day of month must be between 1 and last day of month
    however if i run query such as
    select decode(to_char(to_date(sysdate,'dd/mm/yyyy')),'00/00/0000',to_date('01/01/1900','dd/mm/yyyy'),(to_date(sysdate,'dd/mm/yyyy'))) from dual
    it works fine
  • 3. Re: sqlldr
    586006 Newbie
    Currently Being Moderated
    Hi Hemu,

    italics However, as I can see you are not getting proper data for INST_DATE. You are getting 00/00/0000. Please reconsider changing it to some proper valid date.

    That is why I requested you to reconsider changing the INST_DATE in data file to some valid date. If you replace SYSDATE with 00/00/0000 you will get the same error from query also.

    Change your INST_DATE in file to 01/01/1900 and it should work fine.
  • 4. Re: sqlldr
    hemu Explorer
    Currently Being Moderated
    ok
    but the problem is we will be receiving data like this only and will have to other wise import in excel and ue loader
    i was trying to avoid that ....

    thanxxxx
  • 5. Re: sqlldr
    586006 Newbie
    Currently Being Moderated
    OK, so you would be getting INST_DATE as 00/00/0000 only. It looks like a default value is being passed and decision of passing this default value is taken by someone who does not understand the technology.

    Anyways try this and in that case you might not need to change the data in the data file.

    select decode(to_char(to_date(decode(:INST_DATE,'00/00/0000','01/01/1900','01/01/1900'),'dd/mm/yyyy')),'00/00/0000',to_date('01/01/1900','dd/mm/yyyy'),(to_date(decode(:INST_DATE,'00/00/0000','01/01/1900','01/01/1900'),'dd/mm/yyyy'))) from dual;
  • 6. Re: sqlldr
    hemu Explorer
    Currently Being Moderated
    hiii
    thanx
    can i ask one favour??
    is it possible to loop through a folder containing such files ..there may be more than 20
    and also want to to store the extension of a file in a table ..is it possible?
  • 7. Re: sqlldr
    Etbin Guru
    Currently Being Moderated
    <tt>INST_DATE "decode(to_char(:INST_DATE,'dd/mm/yyyy'),'00/00/0000',to_date('01/01/1900','dd/mm/yyyy'),:INST_DATE)"</tt>
    Using external tables only, I'm not very sure whether <tt> INST_DATE nullif(INST_DATE = '00/00/0000') </tt> is syntactically correct or not, but at least in my case it turned out to be much better off loading null dates instead of some made up ones as "missing dates" have less chance to be overlooked or misinterpreted as 01/01/1900 or something alike.

    Regards

    Etbin
  • 8. Re: sqlldr
    hemu Explorer
    Currently Being Moderated
    sir
    in our database null date is represented as '01-jan-1900'
    i do not know how and why .
    therefore trying to keep up with ....
  • 9. Re: sqlldr
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi hemu,

    I have done the following:
    CREATE TABLE alm_test
    (
       bal_date1       DATE
    ,  brcode          VARCHAR2(8)
    ,  hocode          VARCHAR2(8)
    ,  acno            NUMBER
    ,  f1              VARCHAR2(8)
    ,  sanc_date       DATE
    ,  eff_date        DATE
    ,  inst_date       DATE
    ,  exp_date        DATE
    ,  rate            NUMBER
    ,  rmode           VARCHAR2(1)
    ,  inst            NUMBER
    ,  limit           NUMBER
    ,  equtedyn        VARCHAR2(1)
    ,  borr_code       NUMBER
    );
    
    
    Loading file with NULL when date is = '00/00/0000' which I recommend:
    
    --alm_test.ctl
    load data
    INFILE '300912LN.002'
    append
    INTO TABLE alm_test
    FIELDS TERMINATED BY '|'
    TRAILING NULLCOLS
    (
     BAL_DATE1  date 'dd/mm/yyyy'     ,
     BRCODE         ,
     HOCODE         ,
     ACNO           ,
     F1             ,
     SANC_DATE  DATE 'dd/mm/yyyy' "NULLIF(:SANC_DATE, '00/00/0000')", 
     EFF_DATE   DATE 'dd/mm/yyyy' "NULLIF(:EFF_DATE,  '00/00/0000')", 
     INST_DATE  DATE 'dd/mm/yyyy' "NULLIF(:INST_DATE, '00/00/0000')", 
     EXP_DATE   DATE 'dd/mm/yyyy' "NULLIF(:EXP_DATE,  '00/00/0000')", 
     RATE           ,
     RMODE          ,
     INST           ,
     LIMIT          ,
     EQUTEDYN       ,
     BORR_CODE    )
    
    If you want to keep 01/01/1900 in Oracle, which I would not recommend though:
    
    --alm_test.ctl
    load data
    INFILE '300912LN.002'
    append
    INTO TABLE alm_test
    FIELDS TERMINATED BY '|'
    TRAILING NULLCOLS
    (
     BAL_DATE1  date 'dd/mm/yyyy'     ,
     BRCODE         ,
     HOCODE         ,
     ACNO           ,
     F1             ,
     SANC_DATE  DATE 'dd/mm/yyyy' "DECODE(:SANC_DATE, '00/00/0000', '01/01/1900', :SANC_DATE )",
     EFF_DATE   DATE 'dd/mm/yyyy' "DECODE(:EFF_DATE,  '00/00/0000', '01/01/1900', :EFF_DATE  )",
     INST_DATE  DATE 'dd/mm/yyyy' "DECODE(:INST_DATE, '00/00/0000', '01/01/1900', :INST_DATE )",
     EXP_DATE   DATE 'dd/mm/yyyy' "DECODE(:EXP_DATE,  '00/00/0000', '01/01/1900', :EXP_DATE  )",
     RATE           ,
     RMODE          ,
     INST           ,
     LIMIT          ,
     EQUTEDYN       ,
     BORR_CODE    )
    Additionally to load more files at one you have 2 alternatives:

    a) modify your control file to load more than one file:
    --alm_test.ctl
    load data
    INFILE '300912LN.001'
    INFILE '300912LN.002'
    INFILE '300912LN.003'
    ...
    ...
    append
    INTO TABLE alm_test
    b) make a loop in your environment running this command:
    --alm_test.ctl
    load data
    append
    INTO TABLE alm_test
    
    execution:
    sqlldr control=alm_test.ctl data=300912LN.001 userid=test/test@mydb
    sqlldr control=alm_test.ctl data=300912LN.002 userid=test/test@mydb
    ...
    If you are working in Unix the loop can easily be done with a shell script, something like:
    for fl in `ls -1`; do sqlldr control=alm_test.ctl data=$fl userid=test/test@mydb; done
    Regards.
    Al
  • 10. Re: sqlldr
    hemu Explorer
    Currently Being Moderated
    sir
    mighty thanxxxx


    hemu

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points