RE: PLS-00225

Thanks to all who replied. I knew it had to be something simple - should have checked my docs on CD first.

Thanks again.

Ron Morton

> -----Original Message-----
> From: Haskins, Ed [SMTP:Ed.Haskins_at_VerizonWireless.com]
> Sent: Tuesday, April 24, 2001 5:29 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: PLS-00225
>
> 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
> --------------------------------------------------------------------
> 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: 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).