Home » SQL & PL/SQL » SQL & PL/SQL » Bug in Oracle External Table Not display Decimal Values (Oracle11g, Unix)
Bug in Oracle External Table Not display Decimal Values [message #657102] Thu, 27 October 2016 22:38 Go to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
I have data in a Oracle External file in the attached format. (testmask.txt)
The corresponding column in External Table is defined as DECIMAL as in the below table structure.

The value in the external file position 1:15 is the precision and 16:17 is the scale.
This gives me an error while selecting from the External table with below structure.

CREATE TABLE "TESTMASK"
( "BALANCE" NUMBER(15,2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "EXT_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
READSIZE 512000
FIELDS LRTRIM MISSING FIELD VALUES ARE NULL (
BALANCE position (1:17) DECIMAL(15,2)
) )
LOCATION
( 'testmask.txt'
)
)
REJECT LIMIT UNLIMITED
PARALLEL 10 ;



SELECT * FROM TESTMASK;


Error :-
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-03003: absolute end(17) - start(1) != fieldlength (Cool for field BALANCE_AVAILABLE
29913. 00000 - "error in executing %s callout"
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.



Then I recreated the table with DECIMAL EXTERNAL option.



CREATE TABLE "TESTMASK"
( "BALANCE" NUMBER(15,2)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "EXT_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
READSIZE 512000
FIELDS LRTRIM MISSING FIELD VALUES ARE NULL (
BALANCE position (1:17) DECIMAL EXTERNAL
) )
LOCATION
( 'testmask.txt'
)
)
REJECT LIMIT UNLIMITED
PARALLEL 10 ;


This time selecting from the external table did'nt give any error.

But it displays the value without Decimal point.


select * from testmask;

BALANCE
----------
416745946


Kindly advise How to make the External table display the value with Decimal point.

i.e., the desired output while selecting from the External table would be :-

BALANCE
----------
4167459.46


Thanks,
Ninan
  • Attachment: testmask.txt
    (Size: 0.02KB, Downloaded 46 times)
Re: Bug in Oracle External Table Not display Decimal Values [message #657106 is a reply to message #657102] Fri, 28 October 2016 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The bug is between chair and keyboard. If you have 416745946 then you have not 4167459.46.
If you want 4167459.46 then divide 416745946 by 100 when you select.

And once more, FORMAT your post.

Re: Bug in Oracle External Table Not display Decimal Values [message #657111 is a reply to message #657106] Fri, 28 October 2016 01:45 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
Then what is the point in having the table column declared as

CREATE TABLE "TESTMASK" 
(	"BALANCE" NUMBER(15,2)
) 

And Fields position declared as

FIELDS LRTRIM MISSING FIELD VALUES ARE NULL (
BALANCE position (1:17) DECIMAL(15,2)
) )

or 

BALANCE position (1:17) DECIMAL EXTERNAL

If I need to format using select statement. That is a bug for which no solution so far available from Oracle.

[Updated on: Fri, 28 October 2016 01:47]

Report message to a moderator

Re: Bug in Oracle External Table Not display Decimal Values [message #657112 is a reply to message #657111] Fri, 28 October 2016 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And what is the point of the documentation if you don't read it.
That is the bug.

Re: Bug in Oracle External Table Not display Decimal Values [message #657113 is a reply to message #657112] Fri, 28 October 2016 01:54 Go to previous messageGo to next message
ninan
Messages: 158
Registered: June 2011
Location: bangalore
Senior Member
None of the documents have anything regarding this. Can you share if you think there exists some documentation on this.

I guess you work for Oracle and get enraged when people points out Bugs.

Bugs are bugs and you have to admit it. Oracle is not from Heaven, it is just a product , and you have to admit bugs, not being adamant and angry at it will not help anyone.

[Updated on: Fri, 28 October 2016 01:57]

Report message to a moderator

Re: Bug in Oracle External Table Not display Decimal Values [message #657116 is a reply to message #657113] Fri, 28 October 2016 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
None of the documents have anything regarding this. Can you share if you think there exists some documentation on this.
Ah, I see, you are a genius who don't need read the documentation to know how a product work as it should work as YOU think it does.

Quote:
I guess you work for Oracle and get enraged when people points out Bugs.
And you are wrong but you don't care as the world is as you think it is and not as it actually is.

I think I have found more (real) bugs and asked for more enhancement requests than you will ever do.

Re: Bug in Oracle External Table Not display Decimal Values [message #657117 is a reply to message #657116] Fri, 28 October 2016 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In the end, and this my last post in this topic, say I am wrong and you are tight, then what do you expect?
As this is a bug this is a bug, so ask Oracle.

Re: Bug in Oracle External Table Not display Decimal Values [message #657118 is a reply to message #657113] Fri, 28 October 2016 02:26 Go to previous messageGo to next message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
ninan wrote on Fri, 28 October 2016 07:54
Bugs are bugs and you have to admit it. Oracle is not from Heaven, it is just a product.
You are 100% right there. Problem is, that's the only thing that you are right about.
Why on earth would oracle assume that when you insert a value of 125 that you actually want a value of 1.25 simply because you have stated (in the column definition) that the column is capable of holding 2 decimal places (i.e. NOT that it MUST hold 2 decimal places, just that it CAN? That makes absolutely ZERO logical sense. Please, please understand that your understanding is VERY flawed here. You ask for documentation? Here you go:
https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i16209

Re: Bug in Oracle External Table Not display Decimal Values [message #657119 is a reply to message #657118] Fri, 28 October 2016 02:58 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Reading again what I posted I see I was rude, I sincerely apologize for this.

Previous Topic: Split Blog column to multiple columns within a row
Next Topic: Materialized view creation with complex queries
Goto Forum:
  


Current Time: Tue Jan 16 20:10:46 CST 2018

Total time taken to generate the page: 0.01649 seconds