linked table to Oracle: Invalid value -127 for parameter scale [90008-71] 90008/90008

[Erez]

Hi,

I've created a linked table to an oracle database, and tried:
create table person as select * from ora_person

This gives me:
Invalid value -127 for parameter scale [90008-71] 90008/90008

Doing a column-by-column select, I get the exception when I try to
create table as select one of the NUMBER fields with unspecified
lengths.

Here's a simpler example:

create table t as select sysadmin from ora_person;
(sysadmin is a NUMBER column)

This works:
create table t as select cast(sysadmin as int) sysadmin from
ora_person

But, it's clunky. Is this something that can be fixed in a future
version?

Erez

[Thomas Mueller]

Hi,

> I've created a linked table to an oracle database, and tried:
> create table person as select * from ora_person
>
> This gives me:
> Invalid value -127 for parameter scale [90008-71] 90008/90008
> Doing a column-by-column select, I get the exception when I try to
> create table as select one of the NUMBER fields with unspecified
> lengths.

Sorry I could not reproduce this problem. However I could reproduce a
problem when using negative values for scale. I didn't know this is
allowed, I will add support for that in H2 in the next version (for
both regular tables and linked tables). Example:

create table t(n1 number, n2 number(10), n3 number(10,0), n4
number(10,2), n5 number(10,-2));

I don't understand why you got this exception however. According to
the Oracle documentation the scale can range from -84 to 127. Could
you try create a reproducible test case? The test case you gave
doesn't work for me, I don't have the 'ora_person' table. Is this a
system table? What Oracle version do you use, and what Oracle driver
version (and jar file name) do you use?

Regards,
Thomas