SQL Loader error [message #473015] |
Wed, 25 August 2010 07:44 |
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 |
cookiemonster
Messages: 13937 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 #473022 is a reply to message #473018] |
Wed, 25 August 2010 08:29 |
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 #473035 is a reply to message #473026] |
Wed, 25 August 2010 09:31 |
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 #473090 is a reply to message #473038] |
Wed, 25 August 2010 16:24 |
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 |
|
Barbara Boehmer
Messages: 9097 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.
|
|
|