Problem with CREATE TABLE AS SELECT from linked Oracle tables having NUMBER columns without precision.

[Joel]

Hi, I have stumbled upon an issue when trying copy data from a linked
oracle table. When creating a table with CREATE TABLE AS SELECT from a
linked Oracle table the
values of all NUMBER columns that do not have any precision become 0E
+127.

Steps to reproduce:

-- 1. In oracle, create a table with a single number column (do not
specify any precision).
CREATE TABLE A (
X NUMBER
);

-- 2. Add a few rows to the table.
INSERT INTO A VALUES (0);
INSERT INTO A VALUES (1);
INSERT INTO A VALUES (2);

-- 3. In H2, create a link to the table.
sql> CREATE LINKED TABLE A (
null,
'jdbc:oracle:thin:@localhost:1521:xe',
'test',
'test',
'test',
'A')
READONLY

-- 4. Verify that selecting values from the linked table works.
sql> SELECT X FROM A;
X
0
1
2

-- 5. Copy the linked table A into a new table B using CREATE TABLE AS
SELECT.
sql> CREATE TABLE B AS SELECT * FROM A;

-- Now, all values in the new table B is 0E+127.
sql> SELECT X FROM B;
X
0E+127
0E+127
0E+127

I have reproduced this issue in 1.2.145 and 1.3.149 using oracle jdbc
11.1.0.7.0.

Specifying precision for the column either in oracle or h2 produces
the expected result. Is this the correct place to report this issue?

Thanks,
Joel

[Thomas Mueller]

Hi,

Thanks for reporting the problem! Unfortunately, I can't reproduce the
problem so far, I use

Oracle Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
H2 1.3.149 (2011-01-07)

Regards,
Thomas

[Thomas Mueller]

Hi,

I still can't reproduce it even with a newer version of the Oracle
JDBC driver. I didn't upgrade Oracle yet (I use Oracle 10g). My test
case:

create linked table test(null,
'jdbc:oracle:thin:@192.168.0.135:1521:XE', 'sa', 'sa', 'A');
drop table test2;
create table test2 as select * from test;
@list @meta select * from test;

Column Data
Row # 1
# 1
label X
catalog DB4
schema PUBLIC
table TEST
column X
type 3
typeName DECIMAL
class java.math.BigDecimal
precision 22
scale 0
displaySize 22
autoIncrement false
caseSensitive true
currency false
nullable 1
readOnly false
searchable true
signed true
writable true
definitelyWritable false

@list @meta select * from test2;
Column Data
Row # 1
# 1
label X
catalog DB4
schema PUBLIC
table TEST2
column X
type 3
typeName DECIMAL
class java.math.BigDecimal
precision 22
scale 0
displaySize 22
autoIncrement false
caseSensitive true
currency false
nullable 1
readOnly false
searchable true
signed true
writable true
definitelyWritable false

Could you run the "@list @meta select * from ..." on your database in
the H2 Console and post the meta data for your tables? Unfortunately,
only the trunk of H2 supports listing the metadata in this form, so
the output will be different, but that shouldn't be a problem. Just
copy & paste the result.

Regards,
Thomas

[Thomas Mueller]

Hi,

Could you also get the meta data of the original table (connecting the
H2 Console to Oracle directly)? I get:

@list @meta select * from a;

Column Data
Row # 1
# 1
label X
catalog

schema
table
column X
type 2
typeName NUMBER
class java.math.BigDecimal
precision 0
scale -127
displaySize 22
autoIncrement false
caseSensitive false
currency true

nullable 1
readOnly false
searchable true
signed true
writable true
definitelyWritable false

Regards,
Thomas

[Joel Andersson]

Hi, thank you for quick response!

I tried it once again and it appears the issue only shows up if the originalSchemaString is specified in the CREATE LINKED TABLE statement. 

http://www.h2database.com/html/grammar.html#create_linked_table

If no schema is specified the result is 1, 2, 3 as expected.

I thought it was mandatory to supply the schema when working with oracle, but after seeing your examples and trying it out myself, I found it was not necessary.

The following is the output from @LIST @META SELECT * FROM B.

Column	Data
Row #	1
#	1
label	X

catalog	TEST
schema	PUBLIC
table	B

column	X
type	3
typeName	DECIMAL

class	java.math.BigDecimal
precision	0
scale	-127
displaySize	22
autoIncrement	false

caseSensitive	true
currency	false

nullable	1
readOnly	false
searchable	true
signed	true
writable	true
definitelyWritable	false

(1 row, 20 ms)

Thank you,

Joel

Regards,
Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Joel Andersson]

Sorry, forgot to include the meta data from the original table (linked directly to oracle).

@LIST @META SELECT * FROM A;

Column	Data
Row #	1
#	1
label	X

catalog	TEST
schema	PUBLIC
table	A

column	X
type	3
typeName	DECIMAL

class	java.math.BigDecimal
precision	0
scale	-127
displaySize	22
autoIncrement	false

caseSensitive	true
currency	false

nullable	1
readOnly	false
searchable	true
signed	true
writable	true
definitelyWritable	false

(1 row, 68 ms)

Thanks,

Joel

On Sat, Jan 15, 2011 at 4:12 PM, Thomas Mueller <thomas.to...@gmail.com> wrote:

Regards,
Thomas

[Thomas Mueller]

Hi,

I don't know why I couldn't reproduce the problem so far, because now I can. The problem seems to be that Oracle reports precision 0 and scale -127. I will add a workaround in the linked table support.

Regards,
Thomas

[Joel Andersson]

Hi,

Thank you for your time looking into this problem!

I've just tried the latest version 1.3.150 and unfortunately the problem is still there. Stepping through the code in TableLink I noticed that the type is reported as NUMERIC rather than DECIMAL which means that the workarounds provided in convertPrecision and convertScale are never executed in my case. I've attached a patch to TableLink which resolves the issue for me.

Regards,

Joel

Regards,
Thomas

[Thomas Mueller]

Hi,

I guess the problem could occur for both DECIMAL and NUMERIC, so I leave both.

Thanks for your help!

Regards,
Thomas