Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Error importing data from Oracle View with regexp_like expressions

richard24
Padawan

Hi, we are trying to import data from an Oracle view, which contains regex expressions.

The rows can be imported to exasol but the columns with regex expressions are differing from the source (most of them are null values).

What could be the problem?

Cheers,

Richard

3 REPLIES 3

mwellbro
Xpert

updated: added example

Hi @richard24 ,

when you say import I assume you mean "put it´s DDL on exasol", because an actual IMPORT ( as in IMPORT FROM ORA AT ... SELECT * FROM your_regexp_view ) would probably not yield what you are describing (or did I get you wrong ? ).

As for REGEXP: Exasol uses Perl compatible Regexp (PCRE) while Oracle uses POSIX compatible regexp - I do think they differ syntactically ?
Did not look it up though, just as a thought and to ask if I´m thinking in the right direction 😉

Just to illustrate:

select 'EXA',regexp_substr('give me a string',' [\s\S]*') from dual
union all 
select 'ORA',a.* from (IMPORT FROM ORA AT <your-ora-con-here> STATEMENT 'select regexp_substr(''give me a string'','' [\s\S]*'') from dual') a
union all 
select 'ORA-adjusted-syntax',a.* from (IMPORT FROM ORA AT <your-ora-con-here> STATEMENT 'select regexp_substr(''give me a string'','' .*'') from dual') a;

mwellbro_0-1638307707418.png

In this particular case it´s the different interpretation of "word classes" that leads to different results, but there are
very likely quite a few others where you won´t get the same results.



Cheers,
Malte

richard24
Padawan

Hi,

i am importing data to exasol with the following statement:

select * from
(
import from ORA at ORA_CONNECTION USER 'myUser' IDENTIFIED BY 'myPwd'
statement 'select * from OracleSchema.VIEW_WITH_REGEXP_LIKE_EXPRESSION'
);

mwellbro
Xpert

Thanks for the clarification - could you test if the behavior changes if you use IMPORT FROM JDBC instead ?
You´d have to upload the Oracle JDBC-jar through ExaOperation if that´s not already in place - my reasoning here would be that the Oracle driver (or the receiving end on Exa) sometimes does things you´d not expect, especially regarding data types.

Perhaps even before that - does this persist if you import into a table instead of selecting from the import directly ?