RE: ORA-24347 Error

My guess is that you are hitting some excel or msexcel limitation. Perhaps there is a formatting character that got into the database that msexcel doesn't like.

Do you have any columns that could return a value more than 255 characters in length. I remember an issue that Excel couldn't have any columns with a length longer then 255. The error didn't occur until you actually hit a value that was longer than 255 characters in length. And we got an weird error like this where is was totally unrelated and of course worked in sqlplus, etc.

Perhaps you have also reached the max limit of data that the excel or msexcel can handle, we have done that here as well.

Try to limit the amount of data with rownum < 100 (or whatever) to see if you can get any data through at all. Then increase the rownum amount until you get the problem.

My 2 cents worth.

Kathy

-----Original Message-----
From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Wednesday, June 16, 2004 8:41 AM
To: oracle-l_at_freelists.org
Subject: ORA-24347 Error

First up my hopes of getting to the bottom of this are fairly slim (because I can't get at the code), but I figure that there are enough smart folk here that someone may have seen similar behaviour before.

Our ERP supplier sells a (really quite good) MsExcel add-in that can be used to query the database and return purty reports. This can be run interactively (by report designers) or via ole automation (by a report scheduler).

Our report designers have updated one of the suite of management reports and it runs just fine when run interactively. When scheduled however the log that the add-in produces shows this

 select v.dim4 AS workord, decode(v.dim5,' ','NA',v.dim5) AS activity, dBF.description AS

      workord_text, NVL(v.chrg_stat, 'UNASSIGNED') AS chg_stat, jct.description AS job_code_text,

       act.description AS activity_text, v.period AS period, SUM(v.value_1) AS value_1

      ,MIN(icp.icp_1) AS icp_1,MIN(icp.icp_2) AS icp_2,MIN(icp.icp_3) AS icp_3,MIN(icp.icp_4) AS

      icp_4,MIN(icp.icp_5) AS icp_5,MIN(icp.icp_6) AS icp_6,MIN(icp.icp_7) AS icp_7,MIN(icp.icp_8) AS

      icp_8,MIN(icp.icp_9) AS icp_9,MIN(icp.icp_10) AS icp_10,MIN(icp.icp_11) AS icp_11,MIN(icp.icp_12)

      AS icp_12,MIN(icp.icp_13) AS icp_13,MIN(icp.icp_14) AS icp_14,MIN(icp.icp_15) AS

      icp_15,MIN(icp.icp_16) AS icp_16,MIN(icp.icp_17) AS icp_17,MIN(icp.icp_18) AS

      icp_18,MIN(icp.icp_19) AS icp_19,MIN(icp.icp_20) AS icp_20,MIN(icp.icp_21) AS

      icp_21,MIN(icp.icp_22) AS icp_22,MIN(icp.icp_23) AS icp_23,MIN(icp.icp_24) AS icp_24

      ,MIN(icp.icp_1) AS icp_curper FROM agldescription dBF, agldescription act, aglrelvalue jc,

      agldescription jct ,uhsicpdetail icp, uvidaenq3_chgstat v WHERE v.client = 'AC' AND v.period

>= 200501 AND v.period <= 200501 AND v.dim2 IN ('1010', '1011',
'3010', '3110') AND (dBF.client

• v.client AND dBF.attribute_id = 'BF' AND dBF.language = 'EN' AND dBF.dim_value = v.dim4) AND (jc.client = v.client AND jc.attribute_id = 'B1' AND jc.rel_attr_id = 'RA' AND decode(v.dim5,' ','NA',v.dim5) BETWEEN jc.att_val_from AND jc.att_val_to) AND (jct.client = jc.client AND jct.attribute_id = jc.rel_attr_id AND jct.language = 'EN' AND jct.dim_value = jc.rel_value) AND (act.client = v.client AND act.attribute_id = 'B1' AND act.language = 'EN' AND act.dim_value = decode(v.dim5,' ','NA',v.dim5)) AND v.dim6 = '000225' AND (icp.client(+) = v.client AND icp.resource_id(+) = v.dim6 AND icp.chrg_stat(+) = v.chrg_stat AND icp.fiscal_year(+) = v.fiscal_year) GROUP BY v.chrg_stat, jct.description, v.dim4,decode(v.dim5,' ','NA',v.dim5), dBF.description, act.description, v.period ORDER BY v.chrg_stat, jct.description, v.dim4, decode(v.dim5,' ','NA',v.dim5), dBF.description, act.description, v.period 0.16: Cursor opened. 14:15:56 > ORA-24347: Warning of a NULL column in an aggregate function

and the process dies.

If I run the offending sql through sqlplus it runs successfully. If we run the report interactively it completes successfully. If I try to capture the error either through an event or my trigger :( I get nothing. I shall of course be logging support calls with both the vendor and Oracle,. but if anyone else has encountered this before I'd be grateful for any pointers. I am aware that this is a 'warning' rather than an 'error'.

Environment DataServer 9.2.0.3, Client 9.2.0.3 ODBC Driver 9.2.0.1 All on Win2k sp3.

Cheers

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------



This transmission contains information solely for intended recipient and may
be privileged, confidential and/or otherwise protect from disclosure.  If
you are not the intended recipient, please contact the sender and delete all
copies of this transmission.  This message and/or the materials contained
herein are not an offer to sell, or a solicitation of an offer to buy, any
securities or other instruments.  The information has been obtained or
derived from sources believed by us to be reliable, but we do not represent
that it is accurate or complete.  Any opinions or estimates contained in
this information constitute our judgment as of this date and are subject to
change without notice.  Any information you share with us will be used in
the operation of our business, and we do not request and do not want any
material, nonpublic information. Absent an express prior written agreement,
we are not agreeing to treat any information confidentially and will use any
and all information and reserve the right to publish or disclose any
information you share with us.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------