Home » RDBMS Server » Server Utilities » SQL Loader error (oracle 10g, windows vista)
SQL Loader error [message #473015] Wed, 25 August 2010 07:44 Go to next message
aj_aaron2002
Messages: 15
Registered: August 2010
Location: London
Junior Member
Hello friends,
I am having problems with sql loader
My control file looks like this


LOAD DATA

INFILE 'c:\users\ajayia\empldr.txt'

BADFILE 'c:\users\ajayia\empldr.bad'

DISCARDFILE 'c:\users\ajayia\empldr.dsc'
TRUNCATE
PRESERVE BLANKS
-- INSERT
INTO TABLE empldr
(empno POSITION(01:05) INTEGER EXTERNAL NULLIF empno=BLANKS,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL NULLIF mgr=BLANKS,
hiredate POSITION(31:40) DATE "DD-MON-YYYY",
sal POSITION(41:48) INTERGER EXTERNAL NULLIF sal=BLANKS,
comm POSITION(50:57) INTEGER EXTERNAL NULLIF comm=BLANKS,
deptno POSITION(60:63) INTEGER EXTERNAL NULLIF deptno=BLANKS)



Am getting error in my log file as follows:

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Aug 25 13:41:12 2010

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

Control File: c:\users\ajayia\empldr1.ctl
Data File: c:\users\ajayia\empldr.txt
Bad File: c:\users\ajayia\empldr.bad
Discard File: c:\users\ajayia\empldr.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table EMPLDR, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:5 5 CHARACTER
NULL if EMPNO = BLANKS
ENAME 6:15 10 CHARACTER
JOB 17:25 9 CHARACTER
MGR 27:30 4 CHARACTER
NULL if MGR = BLANKS
HIREDATE 31:40 10 DATE DD-MON-YYYY
DEPTNO 60:63 4 CHARACTER
NULL if DEPTNO = BLANKS

Record 1: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 2: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 3: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 4: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 5: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 6: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 7: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 8: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 9: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01840: input value not long enough for date format

Record 10: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 11: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 12: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 13: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month

Record 14: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month


Table EMPLDR:
0 Rows successfully loaded.
14 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 3584 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 14
Total logical records rejected: 14
Total logical records discarded: 0

Run began on Wed Aug 25 13:41:12 2010
Run ended on Wed Aug 25 13:41:13 2010

Elapsed time was: 00:00:01.00
CPU time was: 00:00:00.15


Please help what am I doing wrong. Am new to oracle.
Re: SQL Loader error [message #473016 is a reply to message #473015] Wed, 25 August 2010 07:47 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
presumably you've either got the position wrong for hiredate or the data in the file you are trying to load isn't in the format DD-MON-YYYY.
Without a sample of the data from the text file we can't tell though.
Re: SQL Loader error [message #473017 is a reply to message #473016] Wed, 25 August 2010 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also:
Quote:
hiredate POSITION(31:40) DATE "DD-MON-YYYY",

POSITION(31:40) is 10 characters, "DD-MON-YYYY" is 11 characters, something wrong there, isn't it?

Regards
Michel
Re: SQL Loader error [message #473018 is a reply to message #473015] Wed, 25 August 2010 07:52 Go to previous messageGo to next message
ranamirfan
Messages: 529
Registered: January 2006
Location: Pakistan / Saudi Arabia
Senior Member

Quote:


Record 3: Rejected - Error on table EMPLDR, column HIREDATE.
ORA-01847: day of month must be between 1 and last day of month




Check you Txt file hiredate column data.

Re: SQL Loader error [message #473019 is a reply to message #473018] Wed, 25 August 2010 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is what cookiemonster already said, please read the posts before replying and try to not repeat what has been said.

Regards
Michel
Re: SQL Loader error [message #473022 is a reply to message #473018] Wed, 25 August 2010 08:29 Go to previous messageGo to next message
aj_aaron2002
Messages: 15
Registered: August 2010
Location: London
Junior Member
Thanks all for the reply, but am still getting thesame error when I changed the hiredate column to dd-mon-yy.

Here is the textfile I am trying to load unto table empldr.
Any suggestion is welcome.
Thanks in anticipation.

 7369 SMITH      CLERK           7902 17-DEC-80  800        20
 7499 ALLEN      SALESMAN        7698 20-FEB-81  1600  300  30
 7521 WARD       SALESMAN        7698 22-FEB-81  1250  500  30
 7566 JONES      MANAGER         7839 02-APR-81  2975       20
 7654 MARTIN     SALESMAN        7698 28-SEP-81  1250  1400 30
 7698 BLAKE      MANAGER         7839 01-MAY-81  2850       30
 7782 CLARK      MANAGER         7839 09-JUN-81  2450       10
 7788 SCOTT      ANALYST         7566 19-APR-87  3000       20
 7839 KING       PRESIDENT            17-NOV-81  5000       10
 7844 TURNER     SALESMAN        7698 08-SEP-81  1500  0    30
 7876 ADAMS      CLERK           7788 23-MAY-87  1100       20
 7900 JAMES      CLERK           7698 03-DEC-81  950        30
 7902 FORD       ANALYST         7566 03-DEC-81  3000       20
 7934 MILLER     CLERK           7782 23-JAN-82  1300       10




[mod-edit: code tags added by bb; next time please add them yourself; it makes the difference between showing a fixed position file or a delimited file]

[Updated on: Wed, 25 August 2010 10:16] by Moderator

Report message to a moderator

Re: SQL Loader error [message #473026 is a reply to message #473022] Wed, 25 August 2010 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well unless you've managed to lose some spaces somewhere you don't have constant positions you can use to load data.
Re: SQL Loader error [message #473035 is a reply to message #473026] Wed, 25 August 2010 09:31 Go to previous messageGo to next message
aj_aaron2002
Messages: 15
Registered: August 2010
Location: London
Junior Member
when i use load data without the constant position, I get the following error:

SQL*Loader-350: Syntax error at line 14.
Token longer than max allowable length of 258 chars
(empno,ename,job,mgr,HIREDATE,sal,comm,deptno)
^
Re: SQL Loader error [message #473038 is a reply to message #473035] Wed, 25 August 2010 09:35 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
So what method are you using to determine which data goes in which column now?
Re: SQL Loader error [message #473043 is a reply to message #473035] Wed, 25 August 2010 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59290
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't use position you have to specify delimiter.

It should be better you read SQL*Loader manual before trying to use it.

Regards
Michel
Re: SQL Loader error [message #473048 is a reply to message #473022] Wed, 25 August 2010 10:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
You do have a fixed position file. It just didn't look that way because you did not use code tags to preserve the spacing, which is why it is important to use the code tags. You still do not have the right date format. Since your month is in capitals in your data, it must also be capitalized in your format, like dd-MON-yy. Your position starting and ending numbers are all way off. It looks like you have a leading blank space, so your field starts at position 2 and ends at position 5, then there is a space, then your next field starts at position 7. You need to count your positions carefully. You might want to just create and test your control file one field at a time.
Re: SQL Loader error [message #473051 is a reply to message #473048] Wed, 25 August 2010 10:41 Go to previous messageGo to next message
cookiemonster
Messages: 10989
Registered: September 2008
Location: Rainy Manchester
Senior Member
Date format will need to be dd-MON-rr not dd-MON-yy.
Otherwise 80 will end up as 2080 and I assume it's meant to be 1980.
Re: SQL Loader error [message #473073 is a reply to message #473051] Wed, 25 August 2010 12:56 Go to previous messageGo to next message
joy_division
Messages: 4520
Registered: February 2005
Location: East Coast USA
Senior Member
And date is in format dd-MON-rr, but you state DD-MON-YYYY, which will make 17-DEC-80 17-DEC-0080.
Re: SQL Loader error [message #473090 is a reply to message #473038] Wed, 25 August 2010 16:24 Go to previous messageGo to next message
aj_aaron2002
Messages: 15
Registered: August 2010
Location: London
Junior Member
When I tried to load into deptldr table, all data loaded properly using the position keyword.

But for empldr table there has been problem probably because of the null values involved. Can anybody post me a correct contol and datafile for doing this on the empldr table. This table has thesame structure as the emp table.


Thanks all for your input and suggestions.
Re: SQL Loader error [message #473098 is a reply to message #473090] Wed, 25 August 2010 17:27 Go to previous message
Barbara Boehmer
Messages: 7991
Registered: November 2002
Location: California, USA
Senior Member
We could do it for you, but you wouldn't learn anything. You have been told what you need to do. Count the spaces in your text data file that you are trying to load and change the positions to the correct numbers to match the file you are trying to load. It is an extremely simple thing that you can do yourself if you can count.
Previous Topic: handling errors in batch file
Next Topic: SQL Loader
Goto Forum:
  


Current Time: Wed Oct 01 08:03:32 CDT 2014

Total time taken to generate the page: 0.09043 seconds