| Sql Loder Issues [message #525383] |
Sat, 01 October 2011 22:14  |
 |
kailas
Messages: 21
Registered: September 2011
Location: pune
|
Junior Member |
|
|
When I am loding the data in person table through sql loder runs sucessfully without errors but when i check the person table it shows me zero records.
Following is the details about what i done.
here are the details of data files.
1 Ahmed Baraka 1000 1.87 1-1-2000
2 John Rice 5000 2.4 10-5-1998
3 Emme Rak 2500 2.34
4 King Size 2700
5 Small Size 3000 31-3-2001
And The control File
OPTIONS ( ERRORS=0)
LOAD DATA
INFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.dat'
BADFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.bad'
DISCARDFILE '/oraeng/app/oracle/product/10.2.0/dbs/persons.dsc'
INTO TABLE "KAILAS"."PERSONS" REPLACE
FIELDS TERMINATED BY X'9' TRAILING NULLCOLS
(ID INTEGER EXTERNAL,
PNAME CHAR,
BALANCE INTEGER EXTERNAL,
RATE FLOAT EXTERNAL,
JOIN_DATE date 'dd-mm-yyyy')
[oracle@localhost dbs]$ sqlldr kailas/kailas control=/oraeng/app/oracle/product/10.2.0/dbs/persons.ctl log=persons.log
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Oct 2 13:55:46 2011
Copyright © 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 5
[oracle@localhost dbs]$ exit
exit
SQL> select * from persons;
no rows selected
SQL> !
then i check the log file. it shows the following details.
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Oct 2 13:55:46 2011
Copyright © 1982, 2005, Oracle. All rights reserved.
Control File: /oraeng/app/oracle/product/10.2.0/dbs/persons.ctl
Data File: /oraeng/app/oracle/product/10.2.0/dbs/persons.dat
Bad File: /oraeng/app/oracle/product/10.2.0/dbs/persons.bad
Discard File: /oraeng/app/oracle/product/10.2.0/dbs/persons.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 0
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table "KAILAS"."PERSONS", loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * WHT CHARACTER
PNAME NEXT * WHT CHARACTER
BALANCE NEXT * WHT CHARACTER
RATE NEXT * WHT CHARACTER
JOIN_DATE NEXT * WHT DATE dd-mm-yyyy
Record 1: Rejected - Error on table "KAILAS"."PERSONS", column ID.
ORA-01722: invalid number
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table "KAILAS"."PERSONS":
0 Rows successfully loaded.
"persons.log" 56L, 2061C
so why it showing invalid number...?
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Sql Loder Issues [message #525388 is a reply to message #525387] |
Sat, 01 October 2011 23:04  |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Integer external is fine. One of your problems appears to be field delimiters. You used X'9' which is the tab character, but your data does not appear to be delimited by tabs, just spaces. However, I don't know if you copied and pasted the actual data or if you typed it or if copying and pasting to the forum altered it. It tries to read the data up until the first tab, which doesn't exist as a number and that fails. Since you used errors=0 it does not try to read the second row. You need to figure out what delimiter is being used in your data and make sure that your control file matches. Unfortunately, you cannot use whitespace due to the space in the middle of the name and a missing field value in one row. I have provided a brief demonstration below using whitespace as a delimiter and loading the name into two separate columns. However, one row does not load due to a missing value.
-- persons.dat:
1 Ahmed Baraka 1000 1.87 1-1-2000
2 John Rice 5000 2.4 10-5-1998
3 Emme Rak 2500 2.34
4 King Size 2700
5 Small Size 3000 31-3-2001
-- persons.ctl:
OPTIONS ( ERRORS=0)
LOAD DATA
INFILE 'persons.dat'
BADFILE 'persons.bad'
DISCARDFILE 'persons.dsc'
INTO TABLE "PERSONS" REPLACE
FIELDS TERMINATED BY WHITESPACE TRAILING NULLCOLS
(ID INTEGER EXTERNAL,
PNAME1 CHAR,
PNAME2 CHAR,
BALANCE INTEGER EXTERNAL,
RATE FLOAT EXTERNAL,
JOIN_DATE date 'dd-mm-yyyy')
-- table creation, load, and results:
SCOTT@orcl_11gR2> create table persons
2 (ID NUMBER,
3 PNAME1 VARCHAR2(10),
4 PNAME2 VARCHAR2(10),
5 BALANCE NUMBER,
6 RATE NUMBER,
7 JOIN_DATE DATE)
8 /
Table created.
SCOTT@orcl_11gR2> $ sqlldr scott/tiger control=persons.ctl log=persons.log
SCOTT@orcl_11gR2> select * from persons
2 /
ID PNAME1 PNAME2 BALANCE RATE JOIN_DATE
---------- ---------- ---------- ---------- ---------- ---------
1 Ahmed Baraka 1000 1.87 01-JAN-00
2 John Rice 5000 2.4 10-MAY-98
3 Emme Rak 2500 2.34
4 King Size 2700
4 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
