ORA-24347: Warning of a NULL column in an aggregate function解决方法

╰半橙微兮° 2022-06-09 08:15 200阅读 0赞

前段时间support印度客户升级系统（从AIX到Linux6.3上），其中碰到一个ORA-2437: Warning of a NULL column in an aggregate function这个错误，[Oracle][]帮助解释：  
    ORA-24347 Warning of a NULL column in an aggregate function  
    Cause: A null column was processed by an aggregate function.   
    Action: An OCI\_SUCCESS\_WITH\_INFO is returned

所以解决办法就是对于NULL column的field进行处理一下，一般用NVL。

可以参考下面的文章：[http://knowledgebase.progress.com/articles/Article/000038909][http_knowledgebase.progress.com_articles_Article_000038909]

SQL QUERY USING AGGREGATE FUNCTION FAILS WITH ERROR 12403 WHEN EXECUTED VIA DATASERVER FOR ORACLE

可打印视图

信息

Article Number    000038909

Environment    OpenEdge

10.x, 11.x

All Supported Operating Systems

Question/Problem Description

SQL query using aggregate function fails with error 12403 when executed via DataServer for Oracle.

SQL query is executed using RUN STORED-PROCEDURE SEND-SQL-STATEMENT ...

Column referenced by the aggregate function contains NULL values.

Dataserv.lg reports ORA-24347: Warning of a NULL column in an aggregate function.

Executing the same SQL query using Oracle SQLPlus or via an ODBC connection works as expected and returns results.

Steps to Reproduce

Clarifying Information

Example code:

DEFINE VARIABLE cQuery AS CHARACTER NO-UNDO.

DEFINE VARIABLE hTT AS HANDLE NO-UNDO.

cQuery = "SELECT SEQUENCE\_NAME, LAST\_NUMBER, (SELECT MAX(PROGRESS\_RECID) FROM invoice) AS MAXTAB FROM user\_sequences WHERE UPPER(sequence\_name) = UPPER('NextInvNum')".

CREATE TEMP-TABLE hTT.

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO hTT ( cQuery ).

DELETE OBJECT hTT.

Error Message    Unable to execute SQL / stored-procedure, result-set \#1 error 31189 (12403)

ORA-24347: Warning of a NULL column in an aggregate function

Defect/Enhancement Number    Defect OE00181807

Cause

The DataServer for Oracle normally treats warnings received from Oracle as error conditions and handles them as such. Due to the NULL values in the column referenced in the aggregate function, ORA-24347 is generated causing the query to fail.

Resolution

Upgrade to OpenEdge 10.2B or later.

OpenEdge 10.2B introduces the PRGRS\_WARNING\_SUPPRESSIONS switch, allowing users to suppress Oracle warning message(s). For example,

\-Dsrv PRGRS\_WARNING\_SUPPRESSIONS,24347

Please refer to the Notes section for more information about how to use the PRGRS\_WARNING\_SUPPRESSIONS switch.

Workaround

Customers unable to upgrade to 10.2B could use one of the following options:

**Option \#1**

**Ensure that the column referenced in the aggregate function does not contain NULL values.**

**Option \#2**

**Ensure that the query excludes the possibility of referencing NULL values. For example,**

**cQuery = "SELECT SEQUENCE\_NAME, LAST\_NUMBER, (SELECT MAX(PROGRESS\_RECID) FROM invoice WHERE PROGRESS\_RECID IS NOT NULL) AS MAXTAB FROM user\_sequences WHERE UPPER(sequence\_name) = UPPER('NextInvNum')".**

**Notes   **

**Note: OE00181807    Type: Workaround**

**A new -Dsrv switch has been introduced**

\---------------------------------

The Oracle DataServer normally treats warnings received from Oracle as error conditions and handles them as such.  Some Oracle DataServer applications may either anticipate or want to overlook certain warning conditions returned from Oracle.

Available in 10.2B, a new -Dsrv switch PRGRS\_WARNING\_SUPPRESSION has been added to the Oracle DataServer.  The objective of this switch is to suppress the Oracle warning codes specified by the user at connection time. Users can provide a maximum of 25 Oracle warning codes per connection with this new -Dsrv switch.  The feature is not available by default so that backward compatibility is maintained. Warnings continue to be treated as errors by default.

Usage:

\-Dsrv PRGRS\_WARNING\_SUPPRESSION,XXXXX,YYYYY;

where

XXXXX,YYYYY are Oracle error codes(warnings). When supplying multiple error codes use commas to separate them. Semicolon(;) at the end is optional.

For example:

\-Dsrv PRGRS\_WARNING\_SUPPRESSION,24347

This will suppress the Oracle error code ORA-23437(which is treated as warning by Oracle).

Notes:

1. The error codes will be suppressed ONLY IF Oracle treats them as warnings. Non-warning errors cannot be suppressed.

2. Error codes added to the PRGRS\_SUPPRESS\_WARNINGS list are unconditional. This means that wherever the warning occurs and by whatever means the Oracle database produced it, it will be supressed by the DataServer.

3. Not all OCI calls are testing for warning conditions.  If you enter a warning error code for the PRGRS\_SUPPRESS\_WARNINGS switch that is not being suppressed, contact technical support to have suppression expanded to the OCI call that caused your warning condition.

Attachment

Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.

转载至http://blog.csdn.net/huluedeai/article/details/52215395

[Oracle]: http://lib.csdn.net/base/oracle
[http_knowledgebase.progress.com_articles_Article_000038909]: http://knowledgebase.progress.com/articles/Article/000038909