Home » RDBMS Server » Server Utilities » Sql Loader (windows 7)
Sql Loader [message #597286] Wed, 02 October 2013 13:53 Go to next message
thelearner
Messages: 121
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 #597289 is a reply to message #597286] Wed, 02 October 2013 14:00 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You "forgot" to mention some information that might help us detect the cause of your problems. These include:
- control file
- input (csv) file
Re: Sql Loader [message #597291 is a reply to message #597289] Wed, 02 October 2013 14:05 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
is below a reasonable description for your EMP2 table?

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
Re: Sql Loader [message #597292 is a reply to message #597289] Wed, 02 October 2013 14:08 Go to previous messageGo to next message
thelearner
Messages: 121
Registered: April 2013
Location: INDIA
Senior Member
EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-80	800		20
7499	ALLEN	SALESMAN7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN7698	22-Feb-81	1250	500	30
7566	JONES	MANAGER	7839	2-Apr-81	2975		20
7654	MARTIN	SALESMAN7698	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	SALESMAN7698	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


CTL file
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:45), 
sal POSITION (46:53),
comm POSITION (54:59),
deptno POSITION (60:64)         
)
 
	
Re: Sql Loader [message #597294 is a reply to message #597292] Wed, 02 October 2013 14:21 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If the above information shows input file you'd like to load, then control file is wrong as it doesn't reflect its format. Review column positions. Fields aren't terminated by comma but tab.
Re: Sql Loader [message #597296 is a reply to message #597294] Wed, 02 October 2013 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59295
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... or, if "SALESMAN7698" is correct, are fixed-length.
Re: Sql Loader [message #597298 is a reply to message #597294] Wed, 02 October 2013 14:38 Go to previous messageGo to next message
thelearner
Messages: 121
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 #597299 is a reply to message #597298] Wed, 02 October 2013 14:40 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are not telling the truth. Either your previous message doesn't contain input file, or this is not the right bad file.
Re: Sql Loader [message #597301 is a reply to message #597298] Wed, 02 October 2013 14:47 Go to previous messageGo to next message
thelearner
Messages: 121
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 #597302 is a reply to message #597301] Wed, 02 October 2013 14:50 Go to previous messageGo to next message
Littlefoot
Messages: 19653
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Stop right here.

First of all, what is your input file? Is it comma-delimited (so you don't use POSITION) or are records fixed in length (so you use POSITION)?
Re: Sql Loader [message #597303 is a reply to message #597302] Wed, 02 October 2013 14:53 Go to previous messageGo to next message
thelearner
Messages: 121
Registered: April 2013
Location: INDIA
Senior Member
It is a comma-delimited


not a fixed length.


Ok.... I got it now....

Re: Sql Loader [message #600919 is a reply to message #597303] Wed, 13 November 2013 18:43 Go to previous message
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
Previous Topic: error while impdb
Next Topic: Transforming Data During a Load (SQL*Loader) with SQL function
Goto Forum:
  


Current Time: Thu Oct 02 06:49:15 CDT 2014

Total time taken to generate the page: 0.10159 seconds