Issues with DB GroupBy and ORA-01722 Error in KNIME with Oracle DB

[lsandinoIQ]

Hello,

I am attempting to execute a DB GroupBy operation on an Oracle database containing approximately 700 million records. However, the operation consistently fails. To address this, we are running the GroupBy in iterations. Each iteration is supposed to retrieve one record, but when I try to fetch the result using a DB Reader, it takes an excessively long time (over 5 minutes) and eventually throws the error:

ORA-01722: invalid number.

My main question is whether it is possible to directly write the output of the DB GroupBy to a table in the database without loading the data into KNIME memory via a DB Reader. I have not found a DB node that allows writing back to the database directly from the GroupBy output without first passing through KNIME’s memory.

Is there a way to achieve this? Any advice or alternative suggestions would be greatly appreciated.

Thank you!

[takbb]

Hi @lsandinoIQ , the error that you’re seeing is being thrown is by Oracle in response to the SQL that KNIME is sending, and so there will not be any output available even if it could be directed to an Oracle table ( because Oracle has encountered a problem before the output can be generated.)

Are you able to share any details of the query or tables involved so that might be able to gives some ideas?

Oracle (and other databases) are a real pain for giving an error but not giving any clues about the columns involved! The database must know, but sometimes I think it just enjoys the “adventure game”.

The most common time I see this error is when a character (varchar2 or char) column on the database is being compared with a numeric column or a numeric literal.

e.g. the following query would work fine:

 with myview as (
 select '1' as myval from dual
 union 
 select 'a' as myval from dual
  )
 select * from myview where myval='1'

because the condition involves a character literal
and so would return the ‘1’ row.

The following would also work…

 with myview as (
 select '1' as myval from dual
 union 
 select '2' as myval from dual
  )
 select * from myview where myval=2

because although the character column “myval” is being compared with a numeric literal (2), the values can both be cast as numbers and so all is good.

However the following would fail with ORA-01722:invalid number

 with myview as (
 select '2' as myval from dual
 union 
 select 'a' as myval from dual
  )
 select * from myview where myval=2

because although the ‘2’ can be cast to numeric 2, when oracle attempts to cast ‘a’ as a number it fails.

So I would check your query for an errant comparison of a numeric with a varchar2 or char column to see if you can get to the bottom of the issue.

[Add94]

I agree with @takbb the issue lies with the query & column datatypes.

As far as writing the table directly within the database, the following node should achieve this:

DB Connection Table Writer

[lsandinoIQ]

Hi @Add94
How is it that I didn’t know about this node or even see it before? Thank you sooo much! I’ll review what @takbb takbb suggested and see if I can adjust the error.

[lsandinoIQ]

Hi @takbb
Thank you so much for your response. It’s clear now that I won’t be able to write this table into the database either. However, I’m still unsure why, in the GroupBy node, the columns being processed (I’m calculating averages, deviations, and correlations) appear as numeric, and the GroupBy seems to execute successfully. Then, when I use the DB Reader, I get the wonderfully “clear” (sarcasm intended) error message from the database.

Let me know what kind of information you’d need me to share—node configurations, preliminary results, or something else?

It’s strange because, when I run the same operation on a small subset of data (e.g., only data from the last month; the full dataset spans 10 years), the DB Reader executes successfully. Could it be possible that, even if the query for the table indicates numeric data types and the GroupBy executes without errors, there are textual values within those columns causing the issue?

Thank you so much for your great help!

[takbb]

Hi @lsandinoIQ , the reason why the error only occurs at the DB Reader and not at the GroupBy is because of the way the database nodes function.

The “in database” nodes (those that return brown database query connectors) don’t actually execute anything in the database. They work together to build the sql query. It is when you execute the Reader node, that the query that has been built over all the previous nodes such as DB GroupBy, is actually executed in the database.

What you can do though is make use of the DB Query Extractor node. This allows you to see the actual query that would be passed to the DB Reader.

This is the sequence of events in a simple DB Group By query…

Note that the query can be multi-line so don’t be fooled (as I was initially) in thinking it doesn’t contain a “group by” because only the first line is visible without expanding the table row.

The DB Query Extractor will generate the SQL statement, and if you have another Oracle client tool available such as SQL Developer or DBeaver, you can copy and paste that query into the tool. It will (should) still give you the same error, as it is exactly the same query being executed, but debugging it there may gives some clues as to what is wrong.

If you are able to extract the query from the DB Query Extractor and paste it here on the forum (assuming there is nothing sensitive about the query), then I’d be happy to look and see if there are any clues, or give an idea of where to look.