Ask Anantha

Title GROUPING_ID Author: Markus Jägle (markus.jaegle@trivadis.com) Info Technical background info (April 2002) Source from the Oracle9 i Data Warehousing Guide and the Trivadis courses New Features Oracle9 i and Advanced SQL.   Introduction Oracle8 i introduced the GROUP BY functions ROLLUP and CUBE. With the same version, Oracle also offered the GROUPING function to analyze the aggregates created by the ROLLUP and CUBE functions. But since this function sometimes hogs large amounts of memory, a new function was developed with the version Oracle9 i to solve this problem: the GROUPING_ID function.   ROLLUP, CUBE and GROUPING The ROLLUP function introduced with Oracle8 i allows the creation of sorted subtotals at every level of an aggregation up to the sum total. ROLLUP first calculates the aggregates specified in the GROUP BY clause. Subtotals of the higher levels are then created progressively, navigating from right to left through the grouping attribute...

This tip was found in Oracle forums. With due respect to the author I am replicating the idea of the message below:   Oracle introduced conditional compilation of packages/procedures from 10g.   Conditional compilation allows PL/SQL code to be tailored to specific environments by selectively altering the source code based on compiler directives. It is considered a new feature of Oracle 10g Release 2, but is available in Oracle 10g Release 1 (10.1.0.4.0).   Compiler flags are identified by the "$$" prefix, while conditional control is provided by the $IF-$THEN-$ELSE syntax.   $IF boolean_static_expression $THEN text   [ $ ELSIF boolean_static_expression $THEN text ]   [ $ ELSE text ] $END   Find below a simple example of procedure that uses conditional compilation. The objective of this package is to invalidate the package by not modifying the source/underlying objects.   SQL> create or replace package ...

Collections The online Oracle 9i PL/SQL User Guide introduces collections as; "A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection."   Retrieving Values When you first start using PL/SQL, you generally end up declaring variables, then retrieving a value from a table to go into the variable. If you're going to retrieve a range of values from a table, you declare a cursor, retrieve the values one at a time from the database, and process them sequentially. Sometimes though, you want to retrieve a load of values all in one go, and load them into an array, so that you can carry out some sort of operation on the group of values as a whole.   PL/SQL Table With Oracle 7, you could create an index-by table, or 'PL/SQL Table', that consist of a series of value pairs; an index ...

This tip will enable to create a view even if you do not have an underlying table already present in your database.   In this article you will learn to Create view without a table Creating a table for that view How to make the view to work   Consider the following example:-   CREATE OR REPLACE FORCE VIEW force_view AS SELECT * FROM force_table;   Now check whether the view is created or not:   SELECT object_name, object_type, status, temporary, generated, secondary FROM user_objects WHERE object_name='FORCE_VIEW';   OBJECT_NAME OBJECT_TYPE STATUS       TEMPORARY    GENERATED    SECONDARY ----------- ----------- --------    ---------    ---------   --------- FORCE_VIEW   VIEW         INVALID      N            N    ...