Home » RDBMS Server » Server Utilities » Error in Loading Data with SQLLDR in Oracle 10G (WIN XP)
Error in Loading Data with SQLLDR in Oracle 10G [message #393153] Fri, 20 March 2009 09:50 Go to next message
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 141 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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393155 is a reply to message #393153] Fri, 20 March 2009 09:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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/
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393157 is a reply to message #393155] Fri, 20 March 2009 10:11 Go to previous messageGo to next message
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
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393159 is a reply to message #393157] Fri, 20 March 2009 10:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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=
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393160 is a reply to message #393159] Fri, 20 March 2009 10:43 Go to previous messageGo to next message
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.
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393162 is a reply to message #393159] Fri, 20 March 2009 10:58 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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/
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393165 is a reply to message #393162] Fri, 20 March 2009 11:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous messageGo to next message
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 220 times)
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393172 is a reply to message #393153] Fri, 20 March 2009 11:33 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
you need to modify the control file to inform SQL*Loader the values are separated by commas
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393179 is a reply to message #393172] Fri, 20 March 2009 11:45 Go to previous messageGo to next message
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 '"'
^
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393183 is a reply to message #393153] Fri, 20 March 2009 11:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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!
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393185 is a reply to message #393183] Fri, 20 March 2009 12:03 Go to previous messageGo to next message
megee
Messages: 7
Registered: March 2009
Location: London
Junior Member
thank you, will let you the result..
Re: Error in Loading Data with SQLLDR in Oracle 10G [message #393187 is a reply to message #393179] Fri, 20 March 2009 12:06 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You need FIELDS with an S, not FIELD. There may be other problems as well.

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


Current Time: Fri Dec 09 17:45:19 CST 2016

Total time taken to generate the page: 0.43656 seconds