Home » RDBMS Server » Server Utilities » sqlldr ORA-01841
icon5.gif  sqlldr ORA-01841 [message #610594] Fri, 21 March 2014 10:40 Go to next message
rob nye
Messages: 7
Registered: October 1999
Junior Member
this is running on windows

I have a script creating a txt file (in csv file format) which I want to import into an oracle table using sqlldr
but it is erroring with a
Record 1: Rejected - Error on table RFGDBA.TS_SESSION_SNAPSHOT, column SNAPSHOT_TIMESTAMP.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


this happens for every record in the input file

data
2014/03/21 15:12,andrew.huggon,rdp-tcp#1,2,Active,6,21/03/2014 14:53,eu1ptsw001
2014/03/21 15:12,mick.westwood,rdp-tcp#5,3,Active,9,21/03/2014 14:58,eu1ptsw001


ctl file
Load Data 					
   append into table rfgdba.TS_SESSION_SNAPSHOT
fields terminated by ',' trailing nullcols
  ( 						
  snapshot_timestamp DATE "YYYY/MM/DD HH24:MI",
  username,			
  session_name,			
  id,				
  state,			
  idle_time,			
  logon_time DATE "DD/MM/YYYY HH24:MI",		
  ts_name				
   )

table definition
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)
)


command calling sqlldr
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


Confused I cannot fathom why this is erroring
Re: sqlldr ORA-01841 [message #610595 is a reply to message #610594] Fri, 21 March 2014 10:42 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/E16655_01/server.121/e17639/ldr_control_file.htm#SUTIL4219
Re: sqlldr ORA-01841 [message #610598 is a reply to message #610595] Fri, 21 March 2014 11:04 Go to previous messageGo to next message
rob nye
Messages: 7
Registered: October 1999
Junior Member
thanks I checked the link,
the input record has 2 different date formats in different fields

I tried following the example but got an error

new ctl file
Load Data 					
   append into table rfgdba.TS_SESSION_SNAPSHOT
fields terminated by ","
DATE FORMAT "YYYY/MM/DD HH24:MI"
  ( 						
  snapshot_timestamp,
  username,			
  session_name,			
  id,				
  state,			
  idle_time,			
  logon_time DATE "DD/MM/YYYY HH24:MI",		
  ts_name				
   )


gives error
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Mar 21 16:01:57 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-350: Syntax error at line 7.
Expecting "(", found keyword date.
DATE FORMAT "YYYY/MM/DD HH24:MI"
^
c:\ts_users>
Re: sqlldr ORA-01841 [message #610603 is a reply to message #610594] Fri, 21 March 2014 12:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me:
E:\>sqlldr michel/michel control=t.ctl

SQL*Loader: Release 10.2.0.4.0 - Production on Ven. Mars 21 18:39:20 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 2

E:\>sqlplus michel/michel

SQL*Plus: Release 10.2.0.4.0 - Production on Ven. Mars 21 18:39:28 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

SQL> select * from TS_SESSION_SNAPSHOT;
SNAPSHOT_TIMESTAMP  USERNAME                       SESSION_NAME                           ID STATE       IDLE_TIME
------------------- ------------------------------ ------------------------------ ---------- ---------- ----------
LOGON_TIME          TS_NAME
------------------- ------------------------------
21/03/2014 15:12:00 andrew.huggon                  rdp-tcp#1                               2 Active              6
21/03/2014 14:53:00 eu1ptsw001
21/03/2014 15:12:00 mick.westwood                  rdp-tcp#5                               3 Active              9
21/03/2014 14:58:00 eu1ptsw001

2 rows selected.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options

E:\>type t.ctl
Load Data
   INFILE *
   append into table TS_SESSION_SNAPSHOT
fields terminated by ',' trailing nullcols
  (
  snapshot_timestamp DATE "YYYY/MM/DD HH24:MI",
  username,
  session_name,
  id,
  state,
  idle_time,
  logon_time DATE "DD/MM/YYYY HH24:MI",
  ts_name
   )
BEGINDATA
2014/03/21 15:12,andrew.huggon,rdp-tcp#1,2,Active,6,21/03/2014 14:53,eu1ptsw001
2014/03/21 15:12,mick.westwood,rdp-tcp#5,3,Active,9,21/03/2014 14:58,eu1ptsw001

Re: sqlldr ORA-01841 [message #610634 is a reply to message #610603] Sat, 22 March 2014 05:08 Go to previous messageGo to next message
Littlefoot
Messages: 19612
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are those two sample records the ones you (Rob) tried to load?
Re: sqlldr ORA-01841 [message #610637 is a reply to message #610634] Sat, 22 March 2014 05:24 Go to previous messageGo to next message
rob nye
Messages: 7
Registered: October 1999
Junior Member
Yes, they are held in a separate file.
Re: sqlldr ORA-01841 [message #610638 is a reply to message #610637] Sat, 22 March 2014 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Check it does not contain other things (lines, non-printable characters...) than what you posted.
Copy and paste its content in the control (as I did) and try to load.

Re: sqlldr ORA-01841 [message #610670 is a reply to message #610638] Sat, 22 March 2014 12:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7984
Registered: November 2002
Location: California, USA
Senior Member
I tested on Oracle version 11.2.0.1.0, the same as the original poster, with the data in a separate file, and it works for me as well. So, I have to also suspect there is some leading character that is not being copied and pasted.
Re: sqlldr ORA-01841 [message #610798 is a reply to message #610670] Mon, 24 March 2014 07:33 Go to previous messageGo to next message
rob nye
Messages: 7
Registered: October 1999
Junior Member
ok thank you kind people

I admit I was beginning to think it was my data.

problem I have now is finding the hidden control characters Sad

any suggestions?
Re: sqlldr ORA-01841 [message #610801 is a reply to message #610798] Mon, 24 March 2014 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can find a Windows hexa-editor on the web and load your file in it.

Re: sqlldr ORA-01841 [message #610802 is a reply to message #610801] Mon, 24 March 2014 08:02 Go to previous messageGo to next message
rob nye
Messages: 7
Registered: October 1999
Junior Member
thanks

ahha I have 'ÿþ' in position one of the file
Re: sqlldr ORA-01841 [message #610805 is a reply to message #610802] Mon, 24 March 2014 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59141
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks to let us know.

Re: sqlldr ORA-01841 [message #610854 is a reply to message #610805] Tue, 25 March 2014 04:05 Go to previous message
rob nye
Messages: 7
Registered: October 1999
Junior Member
I found the solution (well for new data fileS)

I have added '-Encoding ASCII ' to the Out-File statement in my data file creation script.

so files are now ok - but......

I have a field that is seconds, but the value in the data file can sometimes be larger ie shows as mi:ss, so the records are being rejected, I have changed it to a character field as although I want the data included, I dont need to do anythign with it.

Many thanks all of you for your help
Previous Topic: Log miner
Next Topic: ORA-39070: Unable to open the log file
Goto Forum:
  


Current Time: Fri Sep 19 01:06:13 CDT 2014

Total time taken to generate the page: 0.13857 seconds