[42636] ETL-3003 String data right truncation: Can it be ignored by the Exasol session?

tama02
Contributor

Dear Exasol Experts,

we import Oracle data into Exasol. The Oracle developers sometimes increase a column size, without letting us know.

(Example: Column1 is char(18) in Oracle, and also char(18) in Exasol. Then the Oracle devs increase it to char(30).)

At the IMPORT, I get this message:

[42636] ETL-3003: [Column=15 Row=376624364] [String data right truncation. String length exceeds limit of 18 characters] (Session: 1703975150583808001)

But it is actually NO PROBLEM for me that a string data is truncated. I want to turn this string length check OFF in Exasol (for the session that does the Oracle import). This error should be IGNORED by Exasol.

Is there a way to turn this string length check/error off for an Exasol session? Is there a session parameter probably?

Thank you,

Tibor

 

1 ACCEPTED SOLUTION

exa-Chris
Community Manager
Community Manager

Hi @tama02 

thank for the feedback, as this is currently not in the Product. Would you be abled to further explain on the case in our Ideas Section?
 https://community.exasol.com/t5/ideas/idb-p/Idea
This way we can directly interact with our Product Management Team. Thanks for your help
Christian

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

View solution in original post

3 REPLIES 3

exa-Chris
Community Manager
Community Manager

Hi @tama02 

thank for the feedback, as this is currently not in the Product. Would you be abled to further explain on the case in our Ideas Section?
 https://community.exasol.com/t5/ideas/idb-p/Idea
This way we can directly interact with our Product Management Team. Thanks for your help
Christian

Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

View solution in original post

jens_areto
SQL-Fighter

Hello Tibor,

the easiest solution for you problem is to use the error clause and the reject limit operators inside an import statement. As result all rows which fit into the columns and do not exceed the length of 18 characters will be inserted. All other rows will not be imported, but the row will be stored inside the error table with the failure message why the import was not working. I will show you an example below:

Thats what I did inside the OracleDB:

create table DWH_OUT.JSC_TEST(COL1 VARCHAR2(20)); 

insert into DWH_OUT.JSC_TEST values ('AAAAAAAAAA'); 
insert into DWH_OUT.JSC_TEST values ('AAAAAAAA'); 
insert into DWH_OUT.JSC_TEST values ('AAAAAAAAAAAAAAA');

What I did inside the ExasolDB:

create or replace table DWH_OUT.JSC_TEST1(COL1 VARCHAR(10)); 

import into DWH_OUT.JSC_TEST1 
from ora at ORA_TEST 
statement 'select * from DWH_OUT.JSC_TEST' 
ERRORS INTO DWH_OUT.ERROR_TBL (CURRENT_TIMESTAMP) 
reject limit unlimited; 

select * from DWH_OUT.ERROR_TBL;

After the execution of the import the failure message will no longer appear.

screen1.png

You can see that there were only 2 rows inserted into the target table. The third row which exceeds the char limit is inserted into the error table. Here you can see how the error is stored here:

screen2.png

Best regards

Jens

 

 

 

 

 

 

tama02
Contributor

Hello Jens,

thank you for your detailed answer and your example.

With your solution, I should have to rewrite all IMPORT statements to use an error table, and write additional statements which would insert the records from the error to the target table. (I need the data in the target table!)

I'd need a simple solution, for example a session parameter "IGNORE STRING TRUNCATION". With this, I wouldn't need to change the code at all, and my problem would be solved.
By the way, FLOAT datatypes are AUTOMATICALLY TRUNCATED by Exasol, if the source FLOAT type is longer than the target in Exasol.
So why is it not possible for STRING types?
An example from SYBASE: there the FLOATs are not automatically truncated, I get an error. But if I add the session parameter "SET ARITHABORT NUMERIC_TRUNCATION OFF", then there is no error on FLOAT truncation, it is automatically truncated. So, in SYBASE, there are session parameters which regulate how the session works.
In Exasol, why are FLOATs automatically truncated, and why STRINGs cannot be automatically truncated? Why can I not regulate how Exasol should handle the data?
Best regards,

Tibor