OraFAQ Forum: Server Utilities » Error in Loading Data with SQLLDR in Oracle 10G

Home » RDBMS Server » Server Utilities » Error in Loading Data with SQLLDR in Oracle 10G (WIN XP)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error in Loading Data with SQLLDR in Oracle 10G [message #393153]

Fri, 20 March 2009 09:50

megee
Messages: 7
Registered: March 2009
Location: London

Junior Member

Hello,

Can any one suggest whats the problem in the below mentioned Control file used for loading data through SQL*LOADER

LOAD DATA 
INFILE 'D:\test\temt.txt' 
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'

INTO TABLE "TEST"
INSERT
(SRNO  INTEGER(7), 
PROD_ID  INTEGER(10), 
PROMO_ID  INTEGER(10), 
CHANNEL_ID  INTEGER(10), 
UNIT_COST  INTEGER(10), 
UNIT_PRICE  INTEGER(10)
)

I am trying to load data in SCOTT schema as user scott.

But i get an error "Commit point reached - logical record count 64"

Why do i get such an error, please refer the attach Log file.

If you need Bad file also please do let me know.

Edit: Mahesh Rajendran
Added CODE tags

Attachment: temt.log
(Size: 7.37KB, Downloaded 1375 times)

[Updated on: Fri, 20 March 2009 09:56] by Moderator

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393154 is a reply to message #393153]

Fri, 20 March 2009 09:52

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>But i get an error "Commit point reached - logical record count 64"
It is not the error.
Your error ( from the log file) is

Record 51: Rejected - Error on table "TEST".
ORA-01460: unimplemented or unreasonable conversion requested

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393155 is a reply to message #393153]

Fri, 20 March 2009 09:55

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Post your ddl, some sample data and your command line arguments to invoke sqlldr.
And please use CODE tags to format the code/data/errors.

[Updated on: Fri, 20 March 2009 09:56]

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393156 is a reply to message #393153]

Fri, 20 March 2009 09:58

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>SQL*Loader: Release 10.2.0.1.0
This is/was somewhat a buggy version so you should upgrade "As Soon As Possible" (ASAP)!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393157 is a reply to message #393155]

Fri, 20 March 2009 10:11

megee
Messages: 7
Registered: March 2009
Location: London

Junior Member

Hello Mahesh,

I am totally new to this.. so can you please explain me in detail which files do you need.

Thanks

Mehul

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393159 is a reply to message #393157]

Fri, 20 March 2009 10:17

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I already said that.
>>Post your ddl, some sample data and your command line arguments to invoke sqlldr.
DDL is your table definition. The definition of the table you are trying to load.
And the command line sqlldr arguments you used.
And google/search docs/ this forum before posting.
http://www.google.com/search?hl=en&q=ORA-01460%3A+unimplemented+or+unreasonable+conversion+requested&btnG=Search&aq=f& ;oq=

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393160 is a reply to message #393159]

Fri, 20 March 2009 10:43

megee
Messages: 7
Registered: March 2009
Location: London

Junior Member

Thankyou, but just to inform you i am getting the same problem after invoking some DDL in TEST table. But i could not understand why am i getting that error, as i havent used any conversion function.

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393162 is a reply to message #393159]

Fri, 20 March 2009 10:58

megee
Messages: 7
Registered: March 2009
Location: London

Junior Member

there might by some reason why i am not able to load data in table.Any point which i might not be considering.

[Updated on: Fri, 20 March 2009 11:00]

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393163 is a reply to message #393153]

Fri, 20 March 2009 11:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>there might by some reason why i am not able to load data in table.
This is true.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393165 is a reply to message #393162]

Fri, 20 March 2009 11:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>there might by some reason why i am not able to load data in table.Any point which i might not be considering.
Yes. There must be.
But we cannot help you unless you can post ***atleast*** some relevant information.
You have not given the table details or data details or the method you used to invoke sqlldr.
We cannot read your mind.
Please read my previous post.
Post the information that was asked here.

[Updated on: Fri, 20 March 2009 11:04]

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393170 is a reply to message #393165]

Fri, 20 March 2009 11:30

megee
Messages: 7
Registered: March 2009
Location: London

Junior Member

Apologies below are the table details,


SQL> desc test
 Name                    Null?    Type
 ----------------------- -------- ----------------
 SRNO                             NUMBER(7)
 PROD_ID                          NUMBER(10)
 PROMO_ID                         NUMBER(10)
 CHANNEL_ID                       NUMBER(10)
 UNIT_COST                        NUMBER(10)
 UNIT_PRICE                       NUMBER(10)

Method for using sqlldr is :

cmd promt,


d:\> sqlldr scott/tiger

control = D:\test\temt.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 20 15:55:50 2009

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

Commit point reached - logical record count 64

Please refer to the attached .dat file and bad file

Attachment: temt.txt
(Size: 2.37KB, Downloaded 1427 times)

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393172 is a reply to message #393153]

Fri, 20 March 2009 11:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

you need to modify the control file to inform SQL*Loader the values are separated by commas

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393179 is a reply to message #393172]

Fri, 20 March 2009 11:45

megee
Messages: 7
Registered: March 2009
Location: London

Junior Member

Does the below mentioned Control file make sense,

 --1 

LOAD DATA 
INFILE 'D:\test\temt.txt' 
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'

INTO TABLE "TEST"
INSERT
FIELD TERMINATED BY (,) 

(SRNO  INTEGER(7),
PROD_ID  INTEGER(10),
PROMO_ID  INTEGER(10),
CHANNEL_ID  INTEGER(10),
UNIT_COST  INTEGER(10),
UNIT_PRICE  INTEGER(10)
)

 --2

LOAD DATA 
INFILE 'D:\test\temt.txt' 
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'

INTO TABLE "TEST"
INSERT
FIELD TERMINATED BY (,) optionally enclosed by '"'

(SRNO  INTEGER(7),
PROD_ID  INTEGER(10),
PROMO_ID  INTEGER(10),
CHANNEL_ID  INTEGER(10),
UNIT_COST  INTEGER(10),
UNIT_PRICE  INTEGER(10)
)

For code--1 i get below mentioned error..


D:\>sqlldr scott/tiger

control = D:\test\temt.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 20 16:36:00 2009

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

SQL*Loader-350: Syntax error at line 8.
Expecting "(", found "FIELD".
FIELD TERMINATED BY (,)
^

And for code--2 i get the below error,


D:\>sqlldr scott/tiger

control = D:\test\temt.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Fri Mar 20 16:39:22 2009

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

SQL*Loader-350: Syntax error at line 8.
Expecting "(", found "FIELD".
FIELD TERMINATED BY (,) optionally enclosed by '"'
^

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393183 is a reply to message #393153]

Fri, 20 March 2009 11:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL*Loader documentation can be found in the Utilities Manual at
http://tahiti.oracle.com

When all else fails, RTFM!

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393185 is a reply to message #393183]

Fri, 20 March 2009 12:03

megee
Messages: 7
Registered: March 2009
Location: London

Junior Member

thank you, will let you the result..

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393187 is a reply to message #393179]

Fri, 20 March 2009 12:06

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Try this

LOAD DATA 
INFILE 'D:\test\temt.txt' 
INTO TABLE TEST
INSERT
FIELDS TERMINATED BY ','  trailing nullcols
(SRNO,  
PROD_ID,  
PROMO_ID,  
CHANNEL_ID,  
UNIT_COST , 
UNIT_PRICE 
)

Edit:
Changed the control file to reflect the correct path as in original post from OP.

[Updated on: Fri, 20 March 2009 12:15]

Report message to a moderator

Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393188 is a reply to message #393179]

Fri, 20 March 2009 12:07

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You need FIELDS with an S, not FIELD. There may be other problems as well.

Report message to a moderator

Previous Topic:	wrap utility causing problems
Next Topic:	Schema Refresh

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 19:26:24 CST 2024