OCI/ORA-Import: different data types

Lukasz_Obst
Contributor

Hello,

I am wondering why the native ORA-Connections behaves different for IMPORT FROM and IMPORT INTO.

First let's look at the differences for the IMPORT FROM statement (JDBC- corrent and ORA-not correct):

 

 

 

 

select val, ora_dump, TYPEOF(val) from (
  IMPORT FROM ORA AT OCI_ORACLE
  STATEMENT 'SELECT to_date(''16.12.2021 12:40'', ''dd.mm.yyyy hh24:mi'') val, 
             dump(to_date(''16.12.2021 12:30'', ''dd.mm.yyyy hh24:mi'')) ora_dump
             from dual'
)
union all
select val, ora_dump, TYPEOF(val) from (
  IMPORT FROM JDBC AT JDBC_ORACLE
  STATEMENT 'SELECT to_date(''16.12.2021 12:40'', ''dd.mm.yyyy hh24:mi'') val, 
             dump(to_date(''16.12.2021 12:30'', ''dd.mm.yyyy hh24:mi'')) ora_dump
             from dual'
)

 

 

 

 

Result:

Lukasz_Obst_0-1639655186525.png

As you can see the ORA-Connection converts the Oracle Timestamp to Date in Exasol and there is an information loss (this also happens for other types but in most cases there is no information loss).

 

The odd thing:

If I first create a table with the JDBC Connection:

 

 

 

 

CREATE OR REPLACE TABLE TESTS.JDBC_TESTS AS 
SELECT * FROM ( 
  IMPORT FROM JDBC AT JDBC_ORACLE
  STATEMENT 'SELECT to_date(''16.12.2021 12:30'', ''dd.mm.yyyy hh24:mi'') val from dual'
	);

 

 

 

 

then create another table like the previous one:

 

 

 

 

CREATE OR REPLACE TABLE TESTS.OCI_TESTS_JDBC_LIKE_TABLE like TESTS.JDBC_TESTS;

 

 

 

 

and then insert the values from the ORA-Connection into the table I just created:

 

 

 

 

IMPORT INTO TESTS.OCI_TESTS_JDBC_LIKE_TABLE FROM ORA AT OCI_ORACLE
		STATEMENT 'SELECT to_date(''16.12.2021 12:30'', ''dd.mm.yyyy hh24:mi'') val from dual'
		;

 

 

 

 

The result then looks like:

Lukasz_Obst_1-1639655719991.png

 

Is this odd or is there something I am missing? I mean there clearly exists a workaround even though I would prefer it to work the same for IMPORT INTO and IMPORT FROM.

 

PS: This also happens when you select a timestamp from oracle and not a date.

 

1 ACCEPTED SOLUTION

Accepted Solutions

exa-Aleksandr
Team Exasol
Team Exasol

Hi @Lukasz_Obst ,

Thanks for convenient test scripts.

As you can see the mapping between JDBC protocol / OCI driver types and Exasol data types is different.

Therefore, explicit definition of datatypes via INTO clause should do the trick

IMPORT into (c1 timestamp, c2 varchar(100)) FROM ORA AT ORACLE_OCI_CONNECTION
STATEMENT 'SELECT to_date(''16.12.2021 12:30'', ''dd.mm.yyyy hh24:mi'') val, 
           dump(to_date(''16.12.2021 12:30'', ''dd.mm.yyyy hh24:mi'')) ora_dump
           from dual'
;
/*
C1                        |C2                                 |
--------------------------+-----------------------------------+
2021-12-16-12.30.00.000000|Typ=13 Len=8: 229,7,12,16,12,30,0,0|
*/

It has the same effect as your example: data types are given by SQL statement / target table definition.

Improvement in this area is already in our Roadmap, but without ETA so far: EXASOL-2545

View solution in original post

1 REPLY 1

exa-Aleksandr
Team Exasol
Team Exasol

Hi @Lukasz_Obst ,

Thanks for convenient test scripts.

As you can see the mapping between JDBC protocol / OCI driver types and Exasol data types is different.

Therefore, explicit definition of datatypes via INTO clause should do the trick

IMPORT into (c1 timestamp, c2 varchar(100)) FROM ORA AT ORACLE_OCI_CONNECTION
STATEMENT 'SELECT to_date(''16.12.2021 12:30'', ''dd.mm.yyyy hh24:mi'') val, 
           dump(to_date(''16.12.2021 12:30'', ''dd.mm.yyyy hh24:mi'')) ora_dump
           from dual'
;
/*
C1                        |C2                                 |
--------------------------+-----------------------------------+
2021-12-16-12.30.00.000000|Typ=13 Len=8: 229,7,12,16,12,30,0,0|
*/

It has the same effect as your example: data types are given by SQL statement / target table definition.

Improvement in this area is already in our Roadmap, but without ETA so far: EXASOL-2545

View solution in original post