Home » RDBMS Server » Server Utilities » Number (10,5)
Number (10,5) [message #291441] Fri, 04 January 2008 04:09 Go to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Hi,

I declared a column with Number(10,5).
While loading data from csv to oracle, the sql loader rejects
the data 16089.38.

Please advice.
Re: Number (10,5) [message #291443 is a reply to message #291441] Fri, 04 January 2008 04:16 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Pleas post your code to prove it .

Thumbs Up
Rajuvan.
Re: Number (10,5) [message #291444 is a reply to message #291441] Fri, 04 January 2008 04:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create table t (col number(10,5));

Table created.

SQL> insert into t values(16089.38);

1 row created.

Your analysis is wrong.

Regards
Michel
Re: Number (10,5) [message #291598 is a reply to message #291444] Sat, 05 January 2008 05:53 Go to previous messageGo to next message
kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India
Senior Member

Check your log file and you may get the reason for rejections.

Regards,
Kiran.

[Updated on: Sat, 05 January 2008 05:53]

Report message to a moderator

Re: Number (10,5) [message #291599 is a reply to message #291598] Sat, 05 January 2008 05:54 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
"Record 23922: Rejected - Error on table PERFCORE3, column RETURNAFTERFEEIRR.
ORA-01438: value larger than specified precision allowed for this column"

This the log file error.
Re: Number (10,5) [message #291601 is a reply to message #291599] Sat, 05 January 2008 06:45 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
And record # 23922 looks like ... what?
Re: Number (10,5) [message #291602 is a reply to message #291441] Sat, 05 January 2008 06:48 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
It is the number of record which is rejected.

I am loading data from csv to oracle using sql loader.
Re: Number (10,5) [message #291603 is a reply to message #291602] Sat, 05 January 2008 06:53 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I know that it is the 23922th record. I'd just like to know how it looks like, especially the part of it which is supposed to fit into the RETURNAFTERFEEIRR column.
Re: Number (10,5) [message #291604 is a reply to message #291603] Sat, 05 January 2008 07:00 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Sample rejected data

en017048,N7C006103,e,02282002,01022008,220849.73,12.49,220849.73,12.49,11302007,11302007


the control file is attached.
  • Attachment: ctl.txt
    (Size: 0.72KB, Downloaded 1264 times)
Re: Number (10,5) [message #291605 is a reply to message #291441] Sat, 05 January 2008 07:01 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
the log file is attached.

Re: Number (10,5) [message #291607 is a reply to message #291605] Sat, 05 January 2008 07:25 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So it is quite obvious; you are inserting a value larger than column's precision allows. Check this:
SQL> create table test (col number(10, 5));

Table created.

SQL> -- A value taken from your example:
SQL>
SQL> insert into test values (220849.73);
insert into test values (220849.73)
                         *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> -- Now, a value which is a correct one:
SQL> insert into test values (12345.67899);

1 row created.

SQL>
It seems that you don't really understand what NUMBER(10, 5) means. Read it like this: "This column will be 10 digits long, and 5 of them will be after the decimal point, which allows max 5 digits BEFORE decimal point". Yet another example:
SQL> insert into test values (1.234566789);

1 row created.

SQL> insert into test values (12345.1234567);

1 row created.

SQL> insert into test values (12345.12);

1 row created.

SQL> insert into test values (123456.12);
insert into test values (123456.12)
                         *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select * From test;

       COL
----------
   1.23457
12345.1235
  12345.12

SQL>

In other words, you'll have to alter the table and enlarge (all?) NUMBER datatype columns in order to successfully load those records.
Re: Number (10,5) [message #291609 is a reply to message #291444] Sat, 05 January 2008 07:28 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Yes. I understood.

Michel Cadot wrote on Fri, 04 January 2008 15:58

SQL> create table t (col number(10,5));

Table created.

SQL> insert into t values(16089.38);

1 row created.

Your analysis is wrong.

Regards
Michel



But I can't understand this.
Re: Number (10,5) [message #291611 is a reply to message #291609] Sat, 05 January 2008 07:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

But I can't understand this.

You said:
Quote:

While loading data from csv to oracle, the sql loader rejects the data 16089.38.

And I showed you this is wrong and actually it was wrong as the bad data was 220849.73 and not 16089.38 (which is a correct one).

Regards
Michel

[Updated on: Sat, 05 January 2008 07:32]

Report message to a moderator

Re: Number (10,5) [message #291612 is a reply to message #291611] Sat, 05 January 2008 07:33 Go to previous message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you friends.
Smile
Previous Topic: Rows missing in the tables during import
Next Topic: oracle export
Goto Forum:
  


Current Time: Tue Dec 24 23:21:01 CST 2024