Re: people running 9i needed

Your procedure compiles under Oracle9i 9.0.1.2.1. (NT) C:\temp>sqlplus system/manager_at_dev90
SQL*Plus: Release 8.1.7.0.0 - Production on Di Feb 19 09:08:23 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved.

Verbonden met:
Oracle9i Enterprise Edition Release 9.0.1.2.1 - Production With the Partitioning option
JServer Release 9.0.1.2.0 - Production

SQL> ed n
/* n.sql is a copy-'n-paste of your code SQL> @ n
Procedure is aangemaakt.

but also under Oracle9i Enterprise Edition Release 9.0.1.0.0 on Linux. C:\temp>sqlplus system/manager_at_orba9i
SQL*Plus: Release 8.1.7.0.0 - Production on Di Feb 19 09:13:46 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. Verbonden met:
Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production With the Partitioning option
JServer Release 9.0.1.0.0 - Production
SQL> @ n
Procedure is aangemaakt.

Regards,
Andre van Winssen

"John Darrah" <jdarrah_at_veripost.net> wrote in message news:03200e72661b4e01e93faa7f64479d4d.36240_at_mygate.mailgate.org...
> Hello,
>
> I have a development instance running 9.0.1 on Solaris 2.8 and I am
> encountering an error when I run the following plsql procedure. The
> support analyst at Oracle said this ran without error when he tried it
> on his system so I'm guessing its environmental but I would like see if
> other installations experience this as well. If anyone is running 9i
> and feels comfortable doing so, could you run this procedure and report
> your results.
>
> create or replace procedure test_grp(i_grouping IN VARCHAR2,
> i_fmt IN VARCHAR,
> i_start_dt IN VARCHAR2,
> i_end_dt IN VARCHAR2)
> AS
> TYPE o_cur IS REF CURSOR;
> cc o_cur;
> m_trunc VARCHAR2(100);
> m_dt_format VARCHAR2(100);
> m_start_dt DATE := '01-JAN-2000';
> m_end_dt DATE := sysdate;
> BEGIN
> m_trunc := i_grouping;
> m_dt_format := i_fmt;
> OPEN cc FOR
> SELECT
> t1.period,
> SUM(t1.total_rec) "total"
> FROM(
> SELECT
> TO_CHAR(created,m_dt_format) period,
> object_type,
> COUNT(*) total_rec
> FROM user_objects
> WHERE (created >= m_start_dt AND created < m_end_dt)
> GROUP BY TO_CHAR(created,m_dt_format),
> object_type
> UNION ALL
> SELECT
> TO_CHAR(t1.created,m_dt_format) period,
> t1.object_type,
> COUNT(*) total_rec
> FROM user_objects t1
> WHERE (t1.created >= m_start_dt AND t1.created < m_end_dt)
> GROUP by TO_CHAR(t1.created,m_dt_format),
> t1.object_type
> ) t1
> GROUP BY t1.period;
> END;
> /
> sho err
> exec test_grp('DD','YYYY/MM/DD',null,null);
>
> Here is the error I get:
> ERROR at line 1:
> ORA-00979: not a GROUP BY expression
> ORA-06512: at "TEST.TEST_GRP", line 15
> ORA-06512: at line 1
>
> Thanks,
>
> John
>
>
> --
> Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Tue Feb 19 2002 - 02:17:24 CST