people running 9i needed

From: John Darrah <jdarrah_at_veripost.net>
Date: Tue, 19 Feb 2002 01:27:23 +0000 (UTC)
Message-ID: <03200e72661b4e01e93faa7f64479d4d.36240@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 Mon Feb 18 2002 - 19:27:23 CST