oracle number to h2 decimal

[henry]

hi there,
i created a linked table to an oracle database. works fine if i select
values of that linked table. if i perform a create statement such as:

create table table_h2 as select * from table_oracle

there is a problem concerning the number values of oracle and the
converted values in h2. if i select the number value of the oracle db,
value=1078. after inserting it into the h2-db, the value inside h2 is
0E+127.

this problem only occurs if on oracle side a number has no length. a
value of the type number(9) e.g. works fine.
any idea to bypass this problem beside altering the oracle db??

kind regards

[Thomas Mueller]

Hi,

Sorry I can not reproduce this problem. I use H2 1.1.112 (2009-05-01)
and Oracle Database 10g Express Edition Release 10.2.0.1.0 using the
Thin JDBC driver. My test case is:

Oracle:
drop table test;
create table test(id number primary key, name varchar(255));
insert into test values(1, 'Hello');
insert into test values(1078, 'World');
select * from test;

H2:
drop table link;
create linked table link('', 'jdbc:oracle:thin:@localhost:1521:XE',
'sa', 'sa', 'TEST');
drop table test_link;
create table test_link as select * from link;
select * from test_link;
ID NAME
1 Hello
1078 World

Regards,
Thomas

[henry]

hmmm, i was using an old version of h2. downloaded the newest version,
did exactly the same things u did and same problem. i will try to
reproduce it on another pc tonight. maybe its a problem of my settings
on this machine.

thx anyway :)

On May 11, 8:14 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> Sorry I can not reproduce this problem. I use H2 1.1.112 (2009-05-01)
> and Oracle Database 10g Express Edition Release 10.2.0.1.0 using the
> Thin JDBC driver. My test case is:
>
> Oracle:
> drop table test;
> create table test(id number primary key, name varchar(255));
> insert into test values(1, 'Hello');
> insert into test values(1078, 'World');
> select * from test;
>
> H2:
> drop table link;
> create linked table link('', 'jdbc:oracle:thin:@localhost:1521:XE',
> 'sa', 'sa', 'TEST');
> drop table test_link;
> create table test_link as select * from link;
> select * from test_link;
> ID      NAME
> 1       Hello
> 1078    World
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

What version of H2 did you use when creating this table?

Regards,
Thomas



On Wed, May 13, 2009 at 10:45 AM, henry <h.st...@googlemail.com> wrote:
>
> Oracle:
> CREATE TABLE TEST
> (
>  ID    NUMBER,
>  ID2   NUMBER(9),
>  TEXT  VARCHAR2(255 BYTE)
> )
>
> converted to H2:
> CREATE CACHED TABLE PUBLIC.TEST_LOKAL(
>    ID DECIMAL(0, -127),
>    ID2 DECIMAL(9, 0),
>    TEXT VARCHAR(255)
> )
>
> on my machine the Oracle type NUMBER is converted to the H2 type
> DECIMAL(0, -127), which can be seen by executing the following
> statement:
>
> select SQL from information_schema.tables where sql like '%TEST_LOKAL
> %'
>
> imo DECIMAL(0, -127) is not capable of storing any values.
> >
>

[henry]

i created that table with version 1.0.79 as well as with 1.1.112. on
oracle side i use Oracle Database 10g Enterprise Edition 10.2.0.1.0.
funny thing, i ran your testcase several times now with the newest
version of H2. sometimes i encounter that problem, sometimes i dont. i
couldnt figure out yet exactly when the problem occurs.

regards,
henry

On May 16, 12:55 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> What version of H2 did you use when creating this table?
>
> Regards,
> Thomas
>

[henry]

btw, similar problem is also described here:

http://groups.google.com/group/h2-database/browse_thread/thread/1d949b888c540a13/13592c85440cbdcb?hl=en&lnk=gst&q=linked+table#13592c85440cbdcb