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

thomas_wiedmannthomas_wiedmann Member Posts: 60 Guru
edited October 2019 in Help

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

Answers

  • Edin_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    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_wiedmannthomas_wiedmann Member Posts: 60 Guru

    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_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    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_wiedmannthomas_wiedmann Member Posts: 60 Guru

    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_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    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_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    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_wiedmannthomas_wiedmann Member Posts: 60 Guru

    Hi Edin,

     

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

     

    Hope this helps..

    Thomas

     

  • Edin_KlapicEdin_Klapic Moderator, Employee, RMResearcher, Member Posts: 299 RM Data Scientist

    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_wiedmannthomas_wiedmann Member Posts: 60 Guru

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

     

    Regards,

    Thomas

     

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

     

     

Sign In or Register to comment.