ORA-24347 when maxRows = number of returned rows and using an aggregate function.

[ajcrites]

I need to be able to run a query that will yield a warning, but I still want to be able to get the results.

SELECT
  c.customer_number,
  COUNT(DISTINCT o.ORDER_NUMBER),
  COUNT(DISTINCT q.QUOTE_NUMBER)
FROM
  Customer c
  LEFT JOIN Orders o on c.customer_number = o.party_number
  LEFT JOIN Quote q on c.customer_number = q.account_number
GROUP BY
  c.customer_number

This query will generate a warning about NULL column in aggregate function.

I really can't find a way to get around this warning and still use a similar query (more details: http://stackoverflow.com/questions/29855456/count-null-columns-as-zeros-with-oracle)

Anyway, the callback function for execute when this query is run will only populate the err and will not return the results even though the query is otherwise valid -- I can run it in other contexts and get the exact data that I need.

There should be a setting that allows you to ignore or suppress warnings or to still get data from the query even if there is a warning.

[cjbj]

There might be something we need to review for SUCCESS_WITH_INFO handling.

@ajcrites I need a testcase. None of my quick tests with Oracle DB 12.10.2 reproduced ORA-24347 in SQL*Plus. Can you post the CREATE TABLE etc statements and tell us what version of Oracle client libraries and database you are using?

[ajcrites]

• DB version 11.1.0.07.0
• Client version 11.2.0.4.0

I don't have direct access to the tables (using views) for legal reasons right now but I will update if I can get them. In the meantime here is someone who has seemingly reproduced this error as well: (create/inserts included) http://blogs.dayneo.co.za/2012/01/work-around-for-ora-24347-warning-of.html

[cjbj]

@ajcrites thanks for the version info. I guess I'll have to install an older DB because that sample runs fine for me with 12.1.0.2

[bchr02]

@cjbj did you run that sample using resultset? I ask because a query I was running using non-resultset that would work gives me this same error:

warning about NULL column in aggregate function

...when I would run it using the resultset method.

[cjbj]

@bchr02 since the SQL is executed on the DB, it's a bit odd to see different ORA errors from RS & non-RS node-oracledb. Have you got a testcase?

[frtrev]

I'am getting the same error either as a resultset or non-resultset, even in the most simplistic query:

500 DB SQL error
[Error: ORA-24347: Warning of a NULL column in an aggregate function]
SQL:
SELECT MAX(HEAT_ID) AS HEAT_ID FROM MIS.INV_TICKET_OUT

Where column HEAT_ID can be null, is there a way to work this out?

[cjbj]

@frtrev does the same query run in other tools to the same DB, e.g. in SQL*Plus?

[frtrev]

@cjbj Thanks for your response, the query runs on every system but this one I set up for node, and what I mean is, my current system is running: windows 10 x64, node v0.12.7, VS 2015, and oracle instant client 12.1 connecting to an Oracle 11g, I do not have SQLPlus or at least was not included in the instant client zip files. Any other computer running oracle client would execute this query without any trouble.

[frtrev]

I just installed sqlplus for instant client and it does work, while my node app does not:

SQL> SELECT MAX(HEAT_ID) AS HEAT_ID FROM MIS.INV_TICKET_OUT;

HEAT_ID

MM15103924

[frtrev]

Update: I changed system to Windows Server 2012 R2 x64, Oracle Client 11g, VS 2015, node 0.12.7.
Still getting the same error.
And SQLPlus runs it just fine.

[cjbj]

@frtrev To triple check, you were using SQL*Plus 11g? We'll need the DDL to create the schema, and the Node testcase. Can you paste the info here?

[frtrev]

I used SQL_Plus 11g for oracle client and SQL_Plus 12 for instant client, they both worked just fine.
Let me see if I can set up a node test case where you can reproduce the error, but I am not sure if I will be able since I could not reproduce it myself yesterday in a different database, maybe the database with the problem has a specific configuration that would allow to return warnings along with the result set, but the fix I submitted yesterday worked just fine for me.