RE: ORA-24347 Error
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Jun 2004 09:53:17 -0400
no specific knowledge on this one, BUT -- *often* the problem with
interactive working versus anything else not working is the environment,
especially including the search path for commands.
The other possibility that occurs to me (since it dies) is that via the
toolset they have a wrapper in there somewhere that traps warnings and dies
whereas the other pathways may be warning tolerant. Maybe they have a switch
for tolerance level, although I'm not sure you *want* a report that counts
(or discounts) the undefined value in an aggregate as anything but a NULL
(undefined) result. What is, after all, the average of 1, 2, 3, and "I don't
know"?
Still, I suppose there are uses for getting aggregate functions on the known
values of a set of rows as long as you know that is what you're doing.
I'm also wierded out by the error message -- do they mean a nullable column,
or a NULL value found in a column, or an entire column has NULL values for
the rows selected? (Don't respond to this musing, I know I can just look it
up, but I find the expression "NULL column" to be annoying.)
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Niall Litchfield
Sent: Wednesday, June 16, 2004 9:41 AM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
