Overflow Exception when displaying table data

[Alex]

When I try to list the records in a table, I get an "Overflow
Exception" error - as a popup when I'm viewing ther table in DB
Explorer, or in Messages tab when I'm executing "select * from
TRANSACTION" from an SQL tab. No other information is provided with
this error.

The table definition is as follows:

CREATE TABLE "TRANSACTION"
(
ID NUMBER(22) NOT NULL,
CREATED_BY VARCHAR2(50 Byte) DEFAULT 'ADMIN' NOT NULL,
CREATED_ON DATE DEFAULT SYSDATE NOT NULL,
MODIFIED_BY VARCHAR2(50 Byte) DEFAULT 'ADMIN' NOT NULL,
MODIFIED_ON DATE DEFAULT SYSDATE NOT NULL,
ENTRY_TYPE VARCHAR2(20 Byte) DEFAULT 'MANUAL' NOT NULL,
PERIOD VARCHAR2(20 Byte) NOT NULL,
LOCKED CHAR(1) DEFAULT 'N' NOT NULL,
SECURTIY_NAME VARCHAR2(50 Byte) NOT NULL,
TICKER_SYMBOL VARCHAR2(20 Byte) NULL,
TRANSACTION_DATE DATE NOT NULL,
SETTLEMENT_DATE DATE NULL,
TRANSACTION_TYPE VARCHAR2(20 Byte) NOT NULL,
ACCOUNT_ID NUMBER(22) NULL,
USER_ID NUMBER(22) NULL,
QUANTITY NUMBER(22) NOT NULL,
UNIT_PRICE NUMBER(22) NULL,
CUSIP_NUMBER VARCHAR2(20 Byte) NULL,
IBM_NUMBER VARCHAR2(20 Byte) NULL,
MUTUAL_FUND CHAR(1) DEFAULT 'N' NOT NULL,
QUANTITY_TYPE VARCHAR2(10 Byte) DEFAULT 'UNIT' NOT NULL
);

All other table-related functions (DDL source, indexes, references,
etc.) work correctly.

Has anyone seen this error before? What does it mean? Is there a
workaround?

Thanks,

Alex

[Thomas Kellerer]

Hi Alex,

I have not seen this before. Could you send me the logfile please?
Maybe I can see more details in there. It seems that there is a
problem with the numbers stored in the table. Can you do a SELECT on
all non-NUMBER columns to check this?

If the numbers are indeed the root-cause of the problem, I'd be
interested in the values that are stored in the table. Could you maybe
export them with some other tool, and send me the file? Or simply do a
SELECT in SQL*Plus and check the value in the columns and rows. It
could very well be that it's one specific value that is causing this.
I did some tests, but could not reproduce this problem with my Oracle
installation.

Which Oracle Version and driver version are you using (the detailed
driver version is reported in log file. If you are using the latest
dev build you can display the details by right clicking on the
connection display in the main window and the choose "Connection
Info".

Regards
Thomas

[Alex]

Thomas,

Yes, it does indeed looks like the problem is with the number-typed
data. By including/excluding columns from the SELECT list, I was able
to narrow down the problematic column - it's UNIT_PRICE, which
contains the values showing up in SQL*Plus as "-~" and "~" - not sure
what they mean, maybe positive/negative infinity? Anyway, here's the
SQL*Plus output:

SQL> desc transaction
Name Null? Type
----------------------------------------- --------
----------------------------
ID NOT NULL NUMBER
CREATED_BY NOT NULL VARCHAR2(50)
CREATED_ON NOT NULL DATE
MODIFIED_BY NOT NULL VARCHAR2(50)
MODIFIED_ON NOT NULL DATE
ENTRY_TYPE NOT NULL VARCHAR2(20)
PERIOD NOT NULL VARCHAR2(20)
LOCKED NOT NULL CHAR(1)
SECURTIY_NAME NOT NULL VARCHAR2(50)
TICKER_SYMBOL VARCHAR2(20)
TRANSACTION_DATE NOT NULL DATE
SETTLEMENT_DATE DATE
TRANSACTION_TYPE NOT NULL VARCHAR2(20)
ACCOUNT_ID NUMBER
USER_ID NUMBER
QUANTITY NOT NULL NUMBER
UNIT_PRICE NUMBER
CUSIP_NUMBER VARCHAR2(20)
IBM_NUMBER VARCHAR2(20)
MUTUAL_FUND NOT NULL CHAR(1)
QUANTITY_TYPE NOT NULL VARCHAR2(10)
SQL> select distinct unit_price from transaction order by 1;
UNIT_PRICE
----------
-~
0
.007619977
.01
.010330004
.013829979
.01504003
.022859988
.058518701
.05852088
.16
.3
.395
.996039964
1
1.21
1.63
2.67
2.67701149
3.35
5.16998925
5.38
5.97
5.98
6.02
7.49962502
7.5
7.9774
8.21018062
9.39
9.88
10
10.12
11.67
11.6877045
12
12.3833333
12.5992188
12.599748
12.7404765
12.7406569
14.2938822
15.4
15.7
15.9971561
16
16.0410651
16.0427807
16.2
16.3695889
16.3719712
16.4473684
17.45
17.6000563
17.7502
21.36
21.88
22
22.2772277
22.2816399
23.5960359
23.5972732
24.0608
24.3
24.39
25.65
27
27.67
28.48
28.5225328
28.5351934
28.5388128
28.7433
30
30.3398058
30.3438975
31.35
31.8696884
31.8877551
32
32.1473
34.0367597
34.0393343
34.270048
34.272658
36.036036
37.1471025
37.1593724
37.8214826
40.5318
43.0605
45.5
45.9773
47.05
47.3036897
47.318612
52
52.0504116
52.09
52.3255814
52.3560209
55.7855
56.6251416
59.1016548
72.35
87
91.55
92.52
~
110 rows selected.

I'm using the latest stable SQLW/J build, and Oracle drivers version
9.2.0.1.0, though the DB server is at 10.2.0.3.0

Thanks,

Alex

On Dec 3, 12:57 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Alex]

Oh, and here's the log output (using workbench.log.format={type}
{timestamp} {message} {error} {source} {stacktrace}):

ERROR 03.12.2007 13:14:55 SQL Error during retrieve: Overflow
Exception DataStore.initData() -

java.sql.SQLException: Overflow Exception
at oracle.sql.NUMBER.toBigDecimal(NUMBER.java:629)
at
oracle.jdbc.dbaccess.DBConversion.NumberBytesToBigDecimal(DBConversion.java:
2805)
at
oracle.jdbc.driver.OracleStatement.getBigDecimalValue(OracleStatement.java:
4539)
at
oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:
5666)
at
oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:
5622)
at
oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:
739)
at workbench.storage.RowData.read(RowData.java:153)
at workbench.storage.DataStore.initData(DataStore.java:1023)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:356)
at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:
123)
at
workbench.sql.DefaultStatementRunner.runStatement(DefaultStatementRunner.java:
226)
at workbench.gui.sql.SqlPanel.displayResult(SqlPanel.java:2411)
at workbench.gui.sql.SqlPanel.runStatement(SqlPanel.java:1644)
at workbench.gui.sql.SqlPanel$10.run(SqlPanel.java:1611)

[Thomas Kellerer]

Thanks for all the detailed info.

When looking at the output in the log file, this seems more like a
driver issue as the exception is thrown when the driver tries to
convert the value received from the database to a Java object.

You might want to try a newer driver (in case you are not using the
most recent one) or even take this issues to Oracle's JDBC forum.

I'm curious on how you got the value into that column in the first
place :)

Regards
Thomas

[Thomas Kellerer]

Ah, I have just seen that you posted the driver version :)

Why don't you use a 10.x driver (I have found they are a bit faster
than the 9.x drivers and support BLOBs a lot better...)

[Alex]

Frankly, I have no idea how these values got there - it's a user
testing environment, and one can never predict what these users would
manage to input :)

Using 10g drivers (10.2.0.3.0) yields a similar exception:

ERROR 05.12.2007 10:13:38 SQL Error during retrieve: Numeric Overflow
[DB Errorcode=17026] DataStore.initData() -

java.sql.SQLException: Numeric Overflow
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
145)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
190)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
286)
at
oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:
7465)
at
oracle.jdbc.driver.NumberCommonAccessor.getBigDecimal(NumberCommonAccessor.java:
2514)
at
oracle.jdbc.driver.NumberCommonAccessor.getObject(NumberCommonAccessor.java:
6347)
at
oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:
1257)

at workbench.storage.RowData.read(RowData.java:153)
at workbench.storage.DataStore.initData(DataStore.java:1023)
at workbench.sql.SqlCommand.processResults(SqlCommand.java:356)
at workbench.sql.commands.SelectCommand.execute(SelectCommand.java:
123)
at
workbench.sql.DefaultStatementRunner.runStatement(DefaultStatementRunner.java:
226)

at workbench.gui.sql.DwPanel.runQuery(DwPanel.java:588)
at
workbench.gui.dbobjects.TableDataPanel.doRetrieve(TableDataPanel.java:
552)
at
workbench.gui.dbobjects.TableDataPanel.showData(TableDataPanel.java:
745)
at
workbench.gui.dbobjects.TableListPanel.retrieveCurrentPanel(TableListPanel.java:
1435)
at workbench.gui.dbobjects.TableListPanel$15.run(TableListPanel.java:
1393)

Now, I know this is more of the Oracle driver problem, not converting
the infinities properly, but wouldn't it be possible to trap this
particular condition in SQLW/J anyway? I'm sure it would take forever
to get a fix from Oracle :(

Thanks,

Alex

On Dec 3, 2:23 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Alex]

Now that I look on this more, the BigDecimal class that Oracle is
trying to convert a NUMBER fields to, doesn't even have a way to
represent infinity, so it might not be even possible to do a proper
conversion :(

[Thomas Kellerer]

> Now, I know this is more of the Oracle driver problem, not converting
> the infinities properly, but wouldn't it be possible to trap this
> particular condition in SQLW/J anyway? I'm sure it would take forever
> to get a fix from Oracle :(

I thought about this as well. The only option I see is to use a
NullValue in that case (when the getObject() throws an error). But
that would mean that the displayed data is incorrect (in the sense
that it does not reflect the data stored in the database) and I'm not
sure if that is a good idea.

If at all this should be a optional setting.

Thomas

[Thomas Kellerer]

Btw: I search a bit for these values. In the Oracle concepts manual
(Chapter 26) is the following note:

"Positive and negative infinity (generated only by importing from an
Oracle Version 5 database)"

And then I found this page here:
http://www.ixora.com.au/notes/infinity.htm

Which essentially says the same. I wonder how these values made it
into your database.
Very strange ;)


Thomas

[Alex]

I have no idea myself - it's a simple CRUD app. Maybe it's Hibernate
playing tricks with me :(

BTW, there ARE ways to get infinity into DB...
http://laurentschneider.com/wordpress/2007/08/positive-infinity.html,
for example... not that I was trying something even remotely similar
in my app, mind you :)

And regarding the error trapping - maybe you could simply put the
error message as the field value for the offending field? That way, it
a) allows to quickly identify the source of data screwup, and b)
doesn't prevent from querying the rest of the data.
Just something to think about...

Alex

On Dec 5, 5:37 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Kellerer]

> And regarding the error trapping - maybe you could simply put the
> error message as the field value for the offending field? That way, it
> a) allows to quickly identify the source of data screwup, and b)
> doesn't prevent from querying the rest of the data.
> Just something to think about...

I have uploaded a new DevBuild where you can ignore errors during data
retrieval. The value will simply be replaced with NULL, and the error
will be logged.

Thomas

[Alex]

Thomas,

The fix doesn't seem to work :(

I was using this method to get infinity into the DB (from the link
above):

create table t as select
STATS_F_TEST(cust_gender, 1, 'STATISTIC','F') f
from (
select 'M' cust_gender from dual union all
select 'M' from dual union all
select 'F' from dual union all
select 'F' from dual)
;

and then

SELECT * FROM T;

and the exception is still there.

Regards,

Alex

On Dec 6, 3:16 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Kellerer]

Hi Alex,

> The fix doesn't seem to work :(

Did you change the workbench.settings file as indicated in
history.txt?
And if you did so, did you make sure SQL Workbench was not running
while you were editing the file?
Because the file will be overwritten when you close the application.


Thomas

[Alex]

Ah! Got it :) I was checking the wrong history.txt (the one for stable
release). It's working now - thanks!

Alex

On Dec 10, 5:12 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote: