Home » RDBMS Server » Server Utilities » Sql Loader (windows 7)
Sql Loader [message #597286] |
Wed, 02 October 2013 13:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/177954.jpg) |
thelearner
Messages: 133 Registered: April 2013 Location: INDIA
|
Senior Member |
|
|
Hi, I am trying to load data into normal emp table, column names and datatypes are correct but it showing below errors.
ORA-01722: invalid number
SQL*Loader: Release 10.2.0.1.0 - Production on Thu Oct 3 00:13:08 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: C:\Users\Anblicks\Downloads\New folder\practice.ctl
Data File: C:\Users\Anblicks\Downloads\New folder\practice.csv
Bad File: C:\Users\Anblicks\Downloads\New folder\practice.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table EMP2, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:4 4 , O(") CHARACTER
ENAME 5:14 10 , O(") CHARACTER
JOB 15:23 9 , O(") CHARACTER
MGR 24:27 4 , O(") CHARACTER
HIREDATE 28:45 18 , O(") CHARACTER
SAL 46:53 8 , O(") CHARACTER
COMM 54:59 6 , O(") CHARACTER
DEPTNO 60:64 5 , O(") CHARACTER
Record 1: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 2: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 3: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 4: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 5: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 6: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 7: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 8: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 9: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 10: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 11: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 12: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 13: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Record 14: Rejected - Error on table EMP2, column MGR.
ORA-01722: invalid number
Table EMP2:
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: 5248 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 14
Total logical records rejected: 14
Total logical records discarded: 0
Run began on Thu Oct 03 00:13:08 2013
Run ended on Thu Oct 03 00:13:22 2013
Elapsed time was: 00:00:13.67
CPU time was: 00:00:00.13
Thanks & Regards,
The Learner.
[Updated on: Wed, 02 October 2013 13:53] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Sql Loader [message #597298 is a reply to message #597294] |
Wed, 02 October 2013 14:38 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/177954.jpg) |
thelearner
Messages: 133 Registered: April 2013 Location: INDIA
|
Senior Member |
|
|
in BAD file I am getting all these records...
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,2-Apr-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-Sep-81,1250,1400,30
7698,BLAKE,MANAGER,7839,1-May-81,2850,,30
7782,CLARK,MANAGER,7839,9-Jun-81,2450,,10
7788,SCOTT,ANALYST,7566,9-Dec-82,3000,,20
7839,KING,PRESIDENT,,17-Nov-81,5000,,10
7844,TURNER,SALESMAN,7698,8-Sep-81,1500,0,30
7876,ADAMS,CLERK,7788,12-Jan-83,1100,,20
7900,JAMES,CLERK,7698,3-Dec-81,950,,30
7902,FORD,ANALYST,7566,3-Dec-81,3000,,20
7934,MILLER,CLERK,7782,23-Jan-82,1300,,10
|
|
|
|
Re: Sql Loader [message #597301 is a reply to message #597298] |
Wed, 02 October 2013 14:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/177954.jpg) |
thelearner
Messages: 133 Registered: April 2013 Location: INDIA
|
Senior Member |
|
|
I changed according to your suggestions but I am getting same issue...
options (skip=1)
load data
infile 'C:\Users\Anblicks\Downloads\New folder\practice.csv'
insert into table emp2
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
( EMPNO POSITION (1:4),
ename POSITION (5:14),
job POSITION (15:23),
mgr POSITION (24:27),
hiredate POSITION (28:36),
sal POSITION (37:44),
comm POSITION (45:52),
deptno POSITION (53:54)
)
If there is any modifications please tell me....
Thanks & Regards,
The learner.
|
|
|
|
|
Re: Sql Loader [message #600919 is a reply to message #597303] |
Wed, 13 November 2013 18:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/da93b8d9f57f4be8145750e5748ac9cd?s=64&d=mm&r=g) |
alan.kendall@nfl.com
Messages: 163 Registered: June 2012 Location: Culver City, California
|
Senior Member |
|
|
UNRECOVERABLE Load Data
Append
INTO TABLE ECDAP1.EMP
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO decimal external nullif EMPNO=blanks
,ENAME char(10) nullif ENAME=blanks
,JOB char(9) nullif JOB=blanks
,MGR decimal external nullif MGR=blanks
,HIREDATE date (11) 'DD-MON-YYYY' nullif HIREDATE=blanks
,SAL decimal external nullif SAL=blanks
,COMM decimal external nullif COMM=blanks
,DEPTNO decimal external nullif DEPTNO=blanks
)
sqlldr userid=test/test control=emp.ctl errors=99999999 log=emp.log bad=emp.bad rows=50000 DIRECT=TRUE
|
|
|
Goto Forum:
Current Time: Sat Sep 07 20:04:31 CDT 2024
|