| while loading data using SQL*Loader--error--ORA-01722: invalid number [message #74751] |
Fri, 11 February 2005 15:18  |
Laks
Messages: 2
Registered: October 2002
|
Junior Member |
|
|
i am trying to insert data into multiple tables using SQL*Loader.
My tables:
SQL> desc dept
DEPTNO nUMBER(2)
DNAME VARCHAR2(14)
LOC vARCHAR2(13)
SQL> desc temp;
EMP NUMBER(5)
PAY nUMBER(5)
This is my CONTROL file
load data
infile 'temp.txt'
append
into table temp
(
emp position(1:5) integer external,
pay position(7:12) integer external
)
into table dept
(
deptno position(1:2) integer external,
dname position(6:20) char,
loc position(22:35) char
)
This is my DATA file
7369, 5000,
7499, 3000,
16,'Accounts','New York'
I get these errors:
Record 1: Rejected - Error on table TEMP, column EMP.
ORA-01722: invalid number
Record 2: Rejected - Error on table TEMP, column EMP.
ORA-01722: invalid number
Record 3: Rejected - Error on table TEMP, column EMP.
ORA-01722: invalid number
Can someone please help me how i can solve this.
Thanks,
Laks
|
|
|
|
| Re: while loading data using SQL*Loader--error--ORA-01722: invalid number [message #74752 is a reply to message #74751] |
Fri, 11 February 2005 18:12  |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Oracle has to have some way of knowing which row to put in which table, such as using a when clause as shown below, where I have just checked for what column the first comma is in. I modified the column positions to match the data file that you posted, but I suspect that some of the spaces may have been automatically removed when you posted, so they might actually be different.
load data
infile 'temp.txt'
append
into table temp
when (5) = ','
(emp position(1:4) integer external,
pay position(7:10) integer external)
into table dept2
when (3) = ','
(deptno position(1:2) integer external,
dname position(5:12) char,
loc position(16:23) char)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
