Error with correct long Query in [Read Database] Operator - ORA-01722:invalid number

thomas_wiedmann

USING ORACLE 12 and RapidMiner 8.0.001 (FREE)

 

Get a ORA-01722:invalid number Error in [Read Database] Operator. This is a long running (20s) and complex Query with result about just 170 rows. The query runs fine in my SQL Console. Rapid Miner told me this error after running 7 Seconds... What going on?

 

EDIT:

Smaller Query run well on this place using [Read Database] Operator.

 

Regards,

Thomas

Edin_Klapic

Hi @thomas_wiedmann,

 

Are you using Macros in your Read Database Operator? Perhaps in combination with prepared statements? If yes you might make sure that these values get correctly inserted in your query. 

This is the only explanation I can come up with since the error message indicates a type conversion error. But as you said the exact same query works in your console.

Other than that I can only guess without having some more information on the statement you execute.

 

Best,

Edin

 

thomas_wiedmann

Hi Edin,

 

I have copied my SQL Query from SQL Console to Read Database Object. So I think, there is no extra character. If there is a SQL Syntax-Error it come up less then 1 Second from Database parser. Now my query run in Rapid Miner about 30 seconds and then fail with ORA-01722:invalid number

 

What do you mean with "Macros" ?

 

Thanks!

Thomas

 

Edin_Klapic

Hi @thomas_wiedmann,

 

The concept of Macros in RapidMiner is explained here: https://docs.rapidminer.com/latest/studio/getting-started/macros.html.

You can think of them as variables which can be changed throughout the process. Sometimes Macros need a special handling when they are used in SQL queries.

 

Browsing the web led me to the information that Oracle sometimes makes an implicit "to_number" conversion in some cases.

Is there a difference if you compare the value types of the Attributes in your database with the value types the Attributes have, when they are read in RapidMiner?

 

Best,

Edin

thomas_wiedmann

Hi Edin,

 

ok, in this case I have some TO_NUMBER() in my query. That's true. And there are some more MIN(), MAX(), AVG() to..

This for example looks like:

 

MAX(TO_NUMBER(REPLACE(COALESCE(d3.myvalue,'0'),'.',',')))

Replace "." to "," to get german decimal values. After that TO_NUMBER() convert the value to decimal number.

 

 

I change my Query and remove every TO_NUMBER() column and try again. After 32 Seconds I get:

 

Database Error occured: Closed Resultset: getMetaData.

 

Is there any timelimit? I will have a closer look on this, a little bit later.

Have I something to change? Any other way to do so?

 

Thanks!

Thomas

 

 

 

Edin_Klapic

Hi @thomas_wiedmann,

 

I suggest you remove the parts where you convert to the German number convention.

Assuming the value type of d3.myvalue is always numeric, I propose to change the query example to

MAX(COALESCE(d3.myvalue,0))

 

I haven't heard about this error before, so I will ask internally. The connection timeout can be changed in the Preferences of RapidMiner Studio.

 

Hope this helps,

Edin

Edin_Klapic

Hi @thomas_wiedmann,

 

for a deeper investigation we would need the log file of your RapidMiner Studio.

Therefore I recommend a fresh start of RapidMiner Studio and the execution of the Read Database Operator with the SQL query.

 

The log file is named rapidminer-studio.log and located in <user-home>/.RapidMiner/

 

Best,

Edin

thomas_wiedmann

Hi Edin,

 

I added my new RapidMiner  Studio Log. On security base, I remove some pieces of my query.

 

Hope this helps..

Thomas

 

Edin_Klapic

Thank you @thomas_wiedmann!

 

Would you please also provide the log for the execution resulting in the Database Error occured: Closed Resultset: getMetaData error?

By the way: Sometimes commented parts of a query may also be a source of errors. Thus, I recommend to avoid them entirely.

 

Best,

Edin

 

thomas_wiedmann

...no problem, here is it...

 

Regards,

Thomas

 

P.S. Maybe you spend me "some more rows" for my free RapidMiner after this race ;-)