-
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
-
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
-
Originally Posted by gandolf989
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.
-
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.
-
.
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|