DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: sqlldr errors importing txt file with ORA-01841:

  1. #1
    Join Date
    Jun 2001
    Posts
    4

    sqlldr errors importing txt file with ORA-01841:

    I have a script creating a txt file which I want to import into an oracle table using sqlldr
    but it is erroring with a ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    data
    Code:
    201403141218,carlo.lambertini,rdp-tcp#9,2,Active,1,14/03/2014 07:25,eu1ptsw001
    201403141218,horst.saendler,rdp-tcp#1,3,Active,0,14/03/2014 05:45,eu1ptsw001
    ctl file
    Code:
    options  (skip=0)
    Load Data 					
       append into table rfgdba.TS_SESSION_SNAPSHOT
    fields terminated by ',' trailing nullcols
      ( 						
      snapshot_timestamp DATE "YYYYMMDDHH24MI",
      username,			
      session_name,			
      id,				
      state,			
      idle_time,			
      logon_time DATE "DD/MM/YYYY HH24:MI",		
      ts_name				
       )
    table definition
    Code:
    CREATE TABLE RFGDBA.TS_SESSION_SNAPSHOT
    (
      SNAPSHOT_TIMESTAMP  DATE,
      USERNAME            VARCHAR2(30 BYTE),
      SESSION_NAME        VARCHAR2(30 BYTE),
      ID                  NUMBER,
      STATE               VARCHAR2(10 BYTE),
      IDLE_TIME           NUMBER,
      LOGON_TIME          DATE,
      TS_NAME             VARCHAR2(30 BYTE)
    )
    I have tried (via sqlplus) - select to_date('201403141218', 'YYYYMMDDHHMI') from dual;

    which returns a value of - 14/03/2014 12:18:00

    so I cannot fathom why this is erroring

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    It seems like you have some bad data in you file. If you are using linux/unix you can use cut to get the
    first four characters and pipe that to a sort -u. Then you will know which data is bad.

    cut -c-4 my.txt | sort -u

    You specify skip=0. You might want to remove that and specify a file for the rejects.

    http://docs.oracle.com/cd/B28359_01/...s.htm#i1004846

  3. #3
    Join Date
    Jun 2001
    Posts
    4
    Quote Originally Posted by gandolf989 View Post
    It seems like you have some bad data in you file. If you are using linux/unix you can use cut to get the
    first four characters and pipe that to a sort -u. Then you will know which data is bad.

    cut -c-4 my.txt | sort -u

    You specify skip=0. You might want to remove that and specify a file for the rejects.

    http://docs.oracle.com/cd/B28359_01/...s.htm#i1004846

    I have removed the skip=0, a bad file is defined by the calling command - see below

    calling command
    Code:
    sqlldr PHANTOM/m0nit0r@RICSBA1 data=%%i control=c:\ts_users\sqlload_ts_users.ctl log=c:\ts_users\sqlfiles\%%~ni.log 
    bad=c:\ts_users\sqlfiles\%%~ni.bad discard=c:\ts_users\sqlfiles\%%~ni.dis direct=TRUE skip=0 rows=20000 errors=100000
    I have changed the script that is creating/formatting the file to be sqlloaded, so that the first field is a date/time field

    new data
    Code:
    2014/03/19 16:23,tony.engwall,rdp,2,Disc,41,19/03/2014 13:10,eu1ptsw001
    2014/03/19 16:23,pilar.velasco,rdp-tcp#6,5,Active,0,19/03/2014 15:29,eu1ptsw001
    i then changed the ctl file, to not define the field with the problem - snapshot_timestamp

    now the error being returned is
    Code:
    Record 1: Rejected - Error on table RFGDBA.TS_SESSION_SNAPSHOT, column SNAPSHOT_TIMESTAMP.
    ORA-01858: a non-numeric character was found where a numeric was expected
    so this makes me think (as you have suggested that there is something funny with the data)

    nb: I am running this on windows
    Last edited by gandolf989; 03-24-2014 at 09:42 AM.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You can open the file with Excel and sort the first column, Install Cygwin for Windows, put the C:\Cygwin\bin directory in you PATH variable
    and use the Linux commands that I sent you, or you can really take skip=0 out and allow the file to get loaded with a bad data file to track
    all of the bad data coming in. Ultimately, you need to do a better job of figuring out what to do with bad records, that you will probably
    continue to get in the future.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool

    .
    Your new data:
    Code:
    2014/03/19 16:23,tony.engwall,rdp,2,Disc,41,19/03/2014 13:10,eu1ptsw001
    2014/03/19 16:23,pilar.velasco,rdp-tcp#6,5,Active,0,19/03/2014 15:29,eu1ptsw001
    Does not match this: "YYYYMMDDHH24MI"
    Why did you change it?
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Jun 2001
    Posts
    4
    I changed the date into a form I thought would get thru sqlldr .

    didnt work, which led me to think that perhaps there was some hidden conrol characters.

    downloaded a hexeditor and found 1st character !

    changed my script that created the output file by adding '-Encoding ASCII ' to the Out-File command
    and now I can read in the data successfully

    many thanks people for ur help

Posting Permissions

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


Click Here to Expand Forum to Full Width