Home » RDBMS Server » Server Utilities » Sql Loder Issues (Oracle 10g)
Sql Loder Issues [message #525383] Sat, 01 October 2011 22:14 Go to next message
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 #525384 is a reply to message #525383] Sat, 01 October 2011 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

post results from below
SQL> DESC KAILAS.PERSONS

>(ID INTEGER EXTERNAL,

not as above change to be as below

(ID NUMBER,
Re: Sql Loder Issues [message #525385 is a reply to message #525384] Sat, 01 October 2011 22:25 Go to previous messageGo to next message
kailas
Messages: 21
Registered: September 2011
Location: pune
Junior Member
here is description of persons table

SQL> desc kailas.persons
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
PNAME VARCHAR2(100)
BALANCE NUMBER
RATE NUMBER
JOIN_DATE DATE

so you mean to say that i should change the 'integer external' to
number in control file.
Re: Sql Loder Issues [message #525386 is a reply to message #525385] Sat, 01 October 2011 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>so you mean to say that i should change the 'integer external' to
>number in control file.

Yes, it should match the datatype of the table
Re: Sql Loder Issues [message #525387 is a reply to message #525386] Sat, 01 October 2011 22:49 Go to previous messageGo to next message
kailas
Messages: 21
Registered: September 2011
Location: pune
Junior Member
I changed the integer external to integer but it gives me following error

[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 14:30:22 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-350: Syntax error at line 8.
Expecting "," or ")", found "NUMBER".
(ID NUMBER,
^

Then i changed it to smallint then it shows me the all records but Id column contains the wrong values.

SQL> select * from persons;

ID PNAME BALANCE RATE JOIN_DATE
---------- -------- ---------- ---------- ---------
8241 Ahmed Baraka 1000 1.87 1-1-2000
8242 John Rice 5000 2.4 10-5-1998
8243 Emme Rak 2500 2.34
8244 King Size 2700
8245 Small Size 3000 31-3-2001

SQL> !

so the problem is only for id column i also changed to the integer but problem remains the same
Re: Sql Loder Issues [message #525388 is a reply to message #525387] Sat, 01 October 2011 23:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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> 


Re: Sql Loder Issues [message #525389 is a reply to message #525388] Sun, 02 October 2011 01:52 Go to previous message
kailas
Messages: 21
Registered: September 2011
Location: pune
Junior Member
Thanks it's working.......
Previous Topic: Issue with data pump export
Next Topic: import of user's table
Goto Forum:
  


Current Time: Fri Dec 04 00:00:33 CST 2020