RE: PLS-00225

From: Haskins, Ed <Ed.Haskins_at_VerizonWireless.com>
Date: Tue, 24 Apr 2001 13:45:08 -0700
Message-ID: <F001.002F1546.20010424132900@fatcity.com>

Ron,

Yes, there is a KEYWORD called ROLLUP which is used in Dimensions typically found in a DW environment. I'm surprised that Oracle has not added this to there list of "Oracle Reserved Words". Hopefully the following will help:

Ed Haskins
Oracle DBA
Verizon Wireless

ROLLUP
ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

Syntax
ROLLUP appears in the GROUP BY clause in a SELECT statement. Its form is:

SELECT ... GROUP BY ROLLUP(grouping_column_reference_list)

Details
ROLLUP's action is straightforward: it creates subtotals which roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies ROLLUP on grouping columns of Time, Region, and Department (n=3), the result set will include rows at four aggregation levels.

-----Original Message-----
Sent: Tuesday, April 24, 2001 4:47 PM
To: Multiple recipients of list ORACLE-L

Hi,

I just ran into a problem with v8.1.6 on Linux whereby my stored procedure won't compile because it claims that a table reference is out of scope. It complains about the definition of the arguments to the stored procedure which are as follows:

CREATE OR REPLACE PROCEDURE rollup_insert (

    vv_rollup_id                OUT     rollup.rollup_id%TYPE,
    vv_warrant_id               IN      rollup.warrant_id%TYPE,
    vv_rollup_limit_id          IN      limit.limit_id%TYPE,
    vv_rollup_time              IN      VARCHAR2,
    vv_rollup_emp_badge IN      rollup.rollup_emp_badge%TYPE,
    vv_exec_id                  OUT     INT,
    vv_rc                       OUT     INT

)
AS
...

The specific error message complains that "rollup" is out of scope. If I substitute base level types for these, the procedure will compile. I have included the "rollup" table definition and ownership below for reference. It is the TWS user trying to execute this procedure.

desc rollup

ROLLUP_ID                       NOT NULL        NUMBER
WARRANT_ID              NOT NULL        NUMBER
ROLLUP_TIME             NOT NULL        DATE
ROLLUP_TIME_TZ          NOT NULL        VARCHAR2(3)
ROLLUP_EMP_BADGE        NOT NULL        VARCHAR2(4)
ROLLUP_LIMIT_ID         NOT NULL        NUMBER
DATESTAMP                               DATE

select owner, object_name, object_type from dba_objects where object_name like '%ROLLUP%';

OWNER                      OBJECT_NAME                      OBJECT_TYPE
----------------------------------------------------------------------------
--------------------
TWS                     ROLLUP                  TABLE
TWS                     ROLLUP_INSERT           PROCEDURE

As you can see, the table and the procedure are owned by the same user. The table exists and the columns referenced in the arguments to the procedure also exist.

What am I missing here? Is there a new keyword called ROLLUP somewhere?

Any help would be very much appreciated.

TIA Ron Morton
Database Architect / Administrator
Union Switch & Signal Inc
rdmorton_at_switch.com
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Morton, Ronald D
INET: rdmorton_at_switch.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Haskins, Ed
INET: Ed.Haskins_at_VerizonWireless.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------