DISCOVERER ADMINISTRATOR CONCEPTS

Discoverer Definitions:

What is Discoverer:

• Oracle Discoverer(like Oracle Reports) is part of Oracle's Business Intelligence set of tools.
• Oracle Discoverer is an adhoc query tool enabling users to develop reports to meet business needs.

Why Discoverer?

• Oracle Discoverer is intended to provide end users access to data and allow them to do data analysis.
• Oracle Discoverer provides business users with data analysis capabilities,regardless of whether the RDBMS was designed for an OLTP system or as a data warehouse.
• Discoverer is an ad hoc reporting tool,it allows end users to quickly create reports without the need of knowledge of SQL.

Discoverer Architecture:

Prerequisites of using Discoverer:

 Before using Discoverer Administrator the following are the Prerequisites:

• A suitable database must be installed and available.An Oracle Enterprise Edition database will support the use of materialized views to improve the performance of the summary folders
• Discoverer Administrator must have been installed on a PC, typically as part of a full Oracle Developer Suite installation.

Before end users can use Discoverer, the following are the Prerequisites:

       Either one or both of the following must have been installed

• Discoverer plus and/or Discoverer Viewer must have been installed on an application server machine and configured correctly as part of an Oracle Business Intelligence installation
• Discoverer Desktop must have been installed on the end users'PCs

Oracle Discoverer Administration:

• Oracle Discoverer 10g Administrator is one of the components of Oracle Discoverer.
• Discoverer Administrator is a tool to hide the complexity of the database from business users, so they can answer business questions quickly and accurately using Oracle Discoverer.
• Discoverer Administrator's wizard-style interfaces enable you to: 

1.  set up and maintain the End User Layer (EUL)
2. control access to information
3. create conditions and calculations for Discoverer end users to include in their worksheets

• Users of Discoverer Administrator are called Discoverer managers.

Implementation Flow Chart:

End User layer (EUL):

• What is End User Layer?

 The End User Layer (EUL) is the metadata (i.e. data about the actual data in a database) that is simple and easy for Discoverer end users to understand. We use Discoverer Administrator to create, customize, and maintain this view for our users so they can easily access data in Discoverer. We must have access to at least one EUL in order to use Discoverer. 

• Why End User Layer?

The EUL insulates Discoverer end users from the complexity usually associated with databases. It provides an intuitive, business-focused view of the database using terms that Discoverer end users are familiar with and can easily understand. This enables Discoverer end users to focus on business issues instead of data access issues.

• What does the EUL contain?

The EUL contains the metadata that defines one or more business areas.Business areas can be set up to reflect the needs of the user or group of users accessing the EUL.

What are EUL tables?

• Discoverer creates the EUL tables.
• The EUL Manager dialog enables you to create or delete the set of tables that make up an EUL.
• When a Discoverer manager defines folders and items in a business area using Discoverer Administrator, Discoverer generates the appropriate SQL statements (that define the selections from a table, view, or column) and stores them in the EUL tables. 
• When a Discoverer end user executes a query (in Discoverer Plus or Discoverer Viewer), Discoverer generates the corresponding SQL statement and sends it to the database, which in turn returns the results to display in Discoverer.
• The Discoverer end user does not have to understand any SQL to access, analyze and retrieve data. It is all handled by Discoverer.

Privileges to create an End User Layer:

•  To Create an End user Layer in an oracle database, the database user that the EUL is being created in must have the following database privileges:

                              CREATE SESSION

                              CREATE TABLE

                              CREATE VIEW
                              CREATE SEQUENCE

                              CREATE PROCEDURE

• The Database user must also have the following specified:  

               A default tablespace (this must not be a temporary tablespace)

               A quota set in the default tablespace (recommended minimum is 3MB) 

How to create End User Layer (EUL):

  Use the EUL Manager to create and maintain EULs for different users(user ids) in the database

Navigate:   Discoverer Administrator->Tools->EUL Manager

The create EUL wizard walks you through the steps of creating a new EUL. The first step is to choose the user who will own the new EUL. 'APPS' database user has access to all the schema used for Oracle Apps.

Navigate:   Discoverer Administrator->Tools->EUL Manager->Create EUL

The user creating EUL for a schema should know the password of schema's database user id

select the default temporary tablespace for the new user by clicking your choice in the list.These settings are database settings.Default table space must be at least Mb

  

How to know the default End User Layer (EUL):

 Discoverer Administrator->Tools->options

Business Area:

Business areas are conceptual groupings of tables and/or views designed to match Discoverer end users specific data requirements.

Example :

    An accounting department needs a business area that represents data about budgets         and finance.

    Alternatively, managers in a human resources department need a business area                 specifically for departments and employee information.

• Discoverer Administrator displays a business area as a file cabinet on the Data tab of the Work area.
• A business area can be opened  to display its folders and items

Business Area Concepts:

Business Area creation:

  Create a Business Area and load data into it (mandatory)

• Business areas are conceptual groupings of tables and/or views
• Discoverer Administrator displays a business area as a file cabinet on the Data tab of the Work area.
• You create a business area in Discoverer Administrator using the Load Wizard (User-friendly Interface)
• Identify the data source and have a clear understanding of its design.
• Identify which tables, views, and columns are required. Identify those that are likely to be included in multiple business areas.
• Map out the necessary joins and determine whether they exist in the database or will have to be created by you using Discoverer Administrator.
• Identify security issues and access privileges.

Business Area - Load Wizard:

 The load Wizard is where you open existing business areas or create a new one .

 It is the first step to working in the Administration Edition.

Navigate: Discoverer Administrator->File->New

Discoverer allows you to load metadata that is present in the database,but in non-oracle format.

Define the objects that will be loaded into the new business area.you choices depend or where you chose to get your meta data.

'ONT' schema is the owner of two tables in the report.

Select the specific objects to load into the business area.

Each table and view that you select will be a folder in the business area.

Two tables to be used in the report are selected.

These options will let you make your business area and its folders easy to find and access

finally ,you must name your new business area

Business Area-Other features:

 Features provided for Business Area:

• Opening an Existing Business Area (File> Open)
• Exporting a Business Area to a File (File> Export)
• Importing EUL elements from a File (e.g.. Business Area,Folders,  Functions etc) (File> Export)
• Editing Business Area Properties (Double click on Business Area Icon)
• Deleting a Business Area (Right Click > Delete Business Area)
• Synchronizing the Business Area with the Database(File> Refresh)
• Data migration issues (Analytic Functions)

Access privileges:

• Use Discoverer access permissions to control who can see and use the data in business areas
• Use Discoverer task privileges to control the tasks each user is allowed to perform
• You can grant Discoverer access permissions and task privileges to database roles as well as to database users.
• Data access rights to the database tables remain under the control of the database administrator.

Granting Access Permission for Business Area:

 This section describes how to grant (or deny) access permission for business areas to specific users or roles

 Navigate: Tools->security

 Apps and Scott users will be able to access the business area.

Granting Task Privileges:

•  Use this tab to grant privileges to a specific user or role/responsibility*.
• Oracle Applications users will see the term Responsibility displayed here instead of Role.
• Grant the head privilege (Administration, User Edition)  before you grant the privileges under it.
• If you wish to grant (or deny) Administration privileges to a user or role, you must also grant (or deny) that user Administration access to the business area.

Navigate: Tools->Privileges

  

Administration Edition Tasks:

• Create/Edit Business Area
• Create Summaries
• Set Privilege
• Manage Scheduled Workbooks

Discoverer plus Tasks:

• Create/Edit Query
• Collect Query Statistics
•  Item Drill
• Drill Out
• Grant Workbook
• Schedule Workbooks
• Save Workbooks to database

Query Retrieval Limits:

  Use this tab to view and edit the query retrieval limits for a specified user or responsibility

Navigate : Tools->Privileges->Query Governor

Specifying Schedule Workbench Limits:

 To set the parameters for scheduling workbooks to a user or role that has that Privilege

Navigate: Tools->Privileges->Scheduled Workbooks

Folders:

 Analogous to a directory in Windows where folders are the containers and items are the files held in the folders.

•  Folders can include items, calculated items, joins, conditions, item classes, and hierarchies.
• You can assign a folder to one or more business areas.
• A folder has a single definition, regardless of the number of business areas to which you assign it.

Three Types of Folders:

• Simple Folders: which contain items based on columns in a single database table or view
• Complex Folders: which can contain items based on columns from multiple database tables or views. This is analogous to a view in the database.
• Custom Folders: which are based on SQL statements

Adding Simple Folders from Database:

This section describes how to add Simple folders from the database to an existing business area. 

Follow the same process you use when creating a new business area.

Navigate: Insert> Folder> From Database> On-line Dictionary

 

Creating Custom Folder:

  Creating new custom folder                         Enter the SQL query to this custom folder

This section describes how to create a custom folder 

TIP:

• You can add comments to your SQL statements by beginning the comment line with -
• Like other folders,custom folders requires joins in order for its data to relate to other data in the business area

Navigate: Insert->folder->Custom

Complex Folder:

• Complex folders consist of items from one or more other folders. 
• Complex folders enable you to create a combined view of data from multiple folders.
• This can simplify the business area without creating a new database view.
• Complex folder’s result set could also be produced by a database view. 

But:

• Complex folders can be created without the database privileges required to create a database view.
• Security is handled through the Folder’s Business Area.
• Complex Folders has no effect on the physical schema, thus they are very safe to use.
• Views can be complicated to maintain, whereas Complex Folders are managed entirely within Discoverer Administration Edition.

Creating Complex folder:

1. On the Data page of the work area, select the business area to which, you want to add a Complex folder.
2. Choose Insert | Folder | New. This creates a new complex folder.
3. Drag items from any folder (in any open business area) to your new folder.

• Each item that is added in the complex folder must belong to a folder, that is joined to the folder of at least one other item in the complex folder
• Item dragged and dropped in complex folder references the original, source item. Therefore any change made in the original item will reflect on the item inside the complex folder.

Navigate: Insert->Folder->New

Manage Folders:

Items:

•  An Item, is a representation of a database table's column, in the EUL
• Administrator can do following on items (Columns):

                         Formatting Change

                         Name Change

                         Other changes to enable user to clearly read the data

• Items are stored in folders and can be:

                        Created

                        Deleted

                        Moved among different folders

Editing Item Properties:

   This section shows you how to enhance the user's view of the data by editing item properties.

Navigate: Item->Right click->properties

Joins:

•  A join relates two folders using one or more common items.
•  End users cannot create joins
•  Administrator has to create joins for end users to create reports that combine information from multiple folders.
•  A join between two folders enables you to include items from both folders when creating (Worksheet, Complex Folder, Hierarchy)
•  Single item joins relate two folders using an item that is common to both folders.
•  Multi-item joins relate two folders using more than one join condition.
•  You cannot directly include functions or literals (e.g. text strings, numbers, dates) in the join conditions.
•  Items in joins can be hidden later, allowing end users to benefit from the use of joined folders without having to see the join details.
• While Creating Joins, You do not need to enter a name for the join, Discoverer Administrator automatically creates a default name for the join when you click OK.

Create Joins:

To create a join, select

• the Master folder
• the operator 
• the detail folder

Use ‘Multi-Item’ for joining multiple Master and detail folders 

For defining join types (outer join, Foreign key with null value, one to one relationship) use ‘OPTIONS'

Navigate: Item->Right click->New Join

Edit Join Properties:

•  You can inspect and edit more than one Join item at a time. 
• Select a join to view its properties.
• If you select more than one Join Property and make a change to a property, the changes apply to all selected properties.

Navigate: Join Name->Right Click->Properties

The Screen details are same as 'Create Join' screen

Navigate: Join Name->Right Click->Edit Join

 Create Calculation Item:

• Calculation Items behave much like any other Item in a Folder
• Calculated Item can be used in Conditions, Summaries, Lists of Values, Joins, and other Calculation Items.
• 3 Calculations in Discoverer

                 Derived calculations

                Aggregate calculations

                Aggregate Derived calculations

• Add new item to show difference of order qty and invoice qty in sample report

Navigate: Folder Name->Right Click->New Item

Editing Calculation Properties:

 Same as item Properties

Navigate: Item Name->Right click->Properties

Editing Item Calculation:

 This section describes how to edit an existing Calculation.

Navigate: Item Name->Right Click->Edit Item

Create Conditions:

•  End users can use Conditions to restrict the results of their query to the areas they are interested
• This can result in faster queries.
• No condition is applied in the sample report

Navigate: Item (on which condition to apply)> Right Click> New Condition

Hierarchies:

•  Hierarchy is a logical linking that you define between Items that enables Discoverer Plus users to:

                  drill up (to a greater level of aggregation) 

                  drill down (to a greater level of detail).

• Hierarchical relationships are not defined in the database; you create them in the Business Area.
• Discoverer Hierarchies mimic data relationships from the end user’s perspective instead of from the database perspective.
• There are two types of Hierarchy in Discoverer Administration Edition:

                   Item Hierarchies

                   Date Hierarchies

Creating Item Hierarchy:

   Navigate: Hierarchies Tab> Business Area> Right Click> New Hierarchy> Item Hierarchy

 Creating Date Hierarchy:

 Example: Year->Month->Week

• User looks at total sales for each year in their records, they can drill down (using the Date Hierarchy)
• you implement this Date Hierarchy in a Business Area, and an end user has a report in Discoverer Plus
• You can use Discoverer Administration Edition’s existing Date Hierarchy templates to define many common Date Hierarchies, or you can create your own customized Date Hierarchies.

Navigate: Hierarchies Tab> Business Area> Right Click> New Hierarchy> Date Hierarchy

Default date hierarchy is used in sample report

You can choose the date items in your business area that will use the new date hierarchy

Navigate: Hierarchies Tab> Business Area> Right Click> New Hierarchy> Date Hierarchy

Note: This step is optional.

Like other items in your Business Area, you can name your hierarchy. 

Choose names that describe the hierarchy so they are easy to find and use later.

Navigate: Hierarchies Tab> Business Area> Right Click> New Hierarchy> Date Hierarchy

Item Class:

 An item class is a group of items that share similar attributes.

• The administrator creates an item class to enable the following features:

                           Lists of values

                           Alternative sorts

                           Drill-to-detail links (hyper drills)

• These features help users build queries more quickly and easily.
• The item class can be created to support these features individually, or in combination
• The only exception is that an alternative sort must relate to a list of values

List of Values:

    A list of values is the item’s set of unique values

• The values the item class references correspond to those found in a database column.
• Lists of values are used by end users to refer to values in the database and to apply conditions and parameter values.
• Lists of values are often generated automatically when the business area is first created (in Load Wizard: Step 4).
• The Item Class Wizard provides a way to extend a list of values to other items.

create List of Values:

•  Select the items that use this item class. 
• If you do not select the items using the item class, you will need to apply the item class to specific items later. 

Navigate: Item Classes Tab> New Item Class> List of Values

Alternative sorts:

 Data elements to be sorted in an alternative order other than Ascending or Descending Order

• For example, a series of sales regions would be sorted alphabetically by default, such as 

                               East,

                               North, 

                               South, and 

                               West

• But the end user may need them sorted in this order: 

                               North, 

                               South,

                               East, and 

                               West.

No Alternate sort is used in Sample Report

Create Alternative sorts:

• Select the item that generates list of values.
• You may choose from any Business Area in the End User Layer. 
• Selecting a Business Area displays its folders.
• Click the plus (+) symbol to open folders and view the items contained in them.

Navigate: Item Classes Tab> New Item Class> Alternative sort

Select the item containing the alternative sort sequence 

Note that the alternative sort item must be in the same folder as the list of values.

Navigate: Item Classes Tab> New Item Class> Alternative sort

  

Select the items that use this item class. 

If you do not select the items using the item class, you will need to apply the item class to specific items later.

Navigate: Item Classes Tab> New Item Class> Alternative sort

Summary Folders:

•  Summary folders are a representation of queried data that has been saved for reuse.
•  Created to improve query response time for end users.
• Query is improved because the query accesses pre-aggregated and pre-joined data rather than accessing the database tables.
• The data is stored in the database in one of the following

                    materialized views

Why Summary Folders?

        This can improve query performance in Discoverer Plus and Discoverer Viewer.

Cursors

CURSORS

Cursor is a pointer to memory location which is called as context area which contains the information necessary for processing, including the number of rows processed by the statement, a pointer to the parsed representation of the statement, and the active set which is the set of rows returned by the query.

Cursor contains two parts:

• Header
• Body

Header includes cursor name, any parameters and the type of data being loaded.

Body includes the select statement.

Ex:

Cursor c(dno in number) return dept%rowtype is select *from dept;

           In the above 

Header – cursor c(dno in number) return dept%rowtype

Body – select *from dept

Cursor Types:

1. Implicit Cursor(SQL)
2. Explicit Cursor

               a) Parameterized Cursors

               b) REF cursors

Cursor Stages:

• Open
• Fetch
• Close

Cursor Attributes:

1. %found
2. %notfound
3. %rowcount
4. %isopen
5. %bulk_rowcount
6. %bulk_exceptions

Cursor Declaration:

Syntax:

       Cursor <cursor_name> is select statement;

   EX: 

       Cursor c is select * from dept;

Cursor Loops:

1. Simple Loop
2. While Loop
3. For Loop

1.Simple Loop:

  Syntax:

     Loop

Fetch <cursor_name> into <record_variable>;

Exit when <cursor_name> % notfound;

    <statements>;

End loop;

Ex:

DECLARE

     cursor c is select * from student;

     v_stud student%rowtype;

BEGIN

     Open c;

     Loop

        fetch c into v_stud;

        exit when c%notfound;

        dbms_output.put_line('Name = ' || v_stud.name);

     end loop;

     close c;

END;

Output:

Name = saketh

Name = srinu

Name = satish

Name = sudha

2.While Loop:

  Syntax:

     While <cursor_name> % found 

      loop

         Fetch <cursor_name> into <record_variable>;

          <statements>;

      End loop;

Ex:

DECLARE

     cursor c is select * from student;

     v_stud student%rowtype;

BEGIN

     open c;

     fetch c into v_stud;

     while c%found loop

          fetch c into v_stud;

          dbms_output.put_line('Name = ' || v_stud.name);

     end loop;

     close c;

END;

Output:

Name = saketh

Name = srinu

Name = satish

Name = sudha

3.For Loop:

    Syntax:

       for <record_variable> in <cursor_name> 

        loop

            <statements>;

        End loop;

Ex:

     declare

     cursor c is select * from student;

     begin

         for v_stud in c

     loop

         dbms_output.put_line('Name = ' || v_stud.name);

     end loop;

    end;

Output:

Name = saketh

Name = srinu

Name = satish

Name = sudha

Implicit Cursors:
============

• Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.
• Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected
• In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement. The following table provides the description of the most used attributes:

     Ex:

      The following program would update the table and increase salary of each customer by 500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected.

DECLARE 

   total_rows number(2);

BEGIN

   UPDATE customers

   SET salary = salary + 500;

   IF sql%notfound THEN

      dbms_output.put_line('no customers selected');

   ELSIF sql%found THEN

      total_rows := sql%rowcount;

      dbms_output.put_line( total_rows || ' customers selected ');

   END IF; 

END;

/

PL/SQL provides some attributes, which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements.

%ROWCOUNT	The number of rows processed by a SQL statement.
%FOUND	TRUE if at least one row was processed.
%NOTFOUND	TRUE if no rows were processed.
%ISOPEN	TRUE if cursor is open or FALSE if cursor has not been opened or has been closed. Only used with explicit cursors.

An Example of PL/SQL Attribute

DECLARE
rows_deleted NUMBER;
BEGIN
DELETE * FROM emp;
rows_deleted := SQL%ROWCOUNT;
END;

The implicit cursor has the following drawbacks:

• It is less efficient than an explicit cursor.
• It is more vulnerable to data errors.
• It gives you less programmatic control.

Explicit Cursors:
============
Explicit cursors are programmer defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is :
      CURSOR cursor_name IS select_statement;

Working with an explicit cursor involves four steps:

• Declaring the cursor for initializing in the memory
• Opening the cursor for allocating memory
• Fetching the cursor for retrieving data
• Closing the cursor to release allocated memory

Declaring the Cursor:

Declaring the cursor defines the cursor with a name and the associated SELECT statement.

Ex: CURSOR c_customers IS

   SELECT id, name, address FROM customers;

Opening the Cursor:

Opening the cursor allocates memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it.

Ex: OPEN c_customers;

Fetching the Cursor:

Fetching the cursor involves accessing one row at a time. 

For example we will fetch rows from the above-opened cursor as follows:

Ex:FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor:

Closing the cursor means releasing the allocated memory.

For example, we will close above-opened cursor as follows:

Ex:CLOSE c_customers;

Example:

Following is a complete example to illustrate the concepts of explicit cursors:

DECLARE

   c_id customers.id%type;

   c_name customers.name%type;

   c_addr customers.address%type;

   CURSOR c_customers is

      SELECT id, name, address FROM customers;

BEGIN

   OPEN c_customers;

   LOOP

      FETCH c_customers into c_id, c_name, c_addr;

      EXIT WHEN c_customers%notfound;

      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

   END LOOP;

   CLOSE c_customers;

END;

/

Parameterized cursors:

• This was used when you are going to use the cursor in more than one place with different values for the same where clause.
• Cursor parameters must be in mode.
• Cursor parameters may have default values.
• The scope of cursor parameter is within the select statement.

Ex:

     DECLARE

         cursor c(dno in number) is select * from dept where deptno = dno;

         v_dept dept%rowtype;

      BEGIN

         open c(20);

         loop

             fetch c into v_dept;

             exit when c%notfound;

            dbms_output.put_line('Dname = ' || v_dept.dname || ' Loc = ' || v_dept.loc);

         end loop;

         close c;

     END;

/

Output:

    Dname = RESEARCH Loc = DALLAS

Reference Cursors:

This is unconstrained cursor which will return different types depends upon the user input

• Ref Cursors cannot be closed implicitly
• Ref Cursors with return type is called strong cursor
• Ref Cursors with out return type is called weak cursor
• you can declare Ref cursor type in package spec as well as body
• you can declare ref cursor types in local subprograms or anonymous blocks. 

Ex:

CREATE OR REPLACE PROCEDURE REF_CURSOR(

    TABLE_NAME IN VARCHAR)

IS

type t

IS

  ref

  CURSOR;

    c t;

    v_dept dept%rowtype;

  type r

IS

  record

  (

    ename emp.ename%type,

    job emp.job%type,

    sal emp.sal%type);

  v_emp r;

  v_stud student.name%type;

BEGIN

  IF table_name = 'DEPT' THEN

    OPEN c FOR SELECT * FROM dept;

  elsif table_name = 'EMP' THEN

    OPEN c FOR SELECT ename,job,sal FROM emp;

  elsif table_name = 'STUDENT' THEN

    OPEN c FOR SELECT name FROM student;

  END IF;

  LOOP

    IF table_name = 'DEPT' THEN

      FETCH c INTO v_dept;

    EXIT

  WHEN c%notfound;

    dbms_output.put_line('Deptno = ' || v_dept.deptno || ' Dname = ' || v_dept.dname || ' Loc = ' || v_dept.loc);

  elsif table_name = 'EMP' THEN

    FETCH c INTO v_emp;

    EXIT

  WHEN c%notfound;

    dbms_output.put_line('Ename = ' || v_emp.ename || ' Job = ' || v_emp.job || ' Sal = ' || v_emp.sal);

  elsif table_name = 'STUDENT' THEN

    FETCH c INTO v_stud;

    EXIT

  WHEN c%notfound;

    dbms_output.put_line('Name = ' || v_stud);

  END IF;

END LOOP;

CLOSE c;

END;

Output: 

SQL> EXEC ref_cursor('DEPT') 

Deptno = 10 Dname = ACCOUNTING Loc = NEW YORK 

Deptno = 20 Dname = RESEARCH Loc = DALLAS 

Deptno = 30 Dname = SALES Loc = CHICAGO 

Deptno = 40 Dname = OPERATIONS Loc = BOSTON 

SQL> EXEC ref_cursor('EMP') 

Ename = SMITH Job = CLERK Sal = 800

 Ename = ALLEN Job = SALESMAN Sal = 1600 

 Ename = WARD Job = SALESMAN Sal = 1250 

 Ename = JONES Job = MANAGER Sal = 2975

 Ename = MARTIN Job = SALESMAN Sal = 1250 

 Ename = BLAKE Job = MANAGER Sal = 2850 

 Ename = CLARK Job = MANAGER Sal = 2450 

 Ename = SCOTT Job = ANALYST Sal = 3000 

 Ename = KING Job = PRESIDENT Sal = 5000

 Ename = TURNER Job = SALESMAN Sal = 1500

 Ename = ADAMS Job = CLERK Sal = 1100 

 Ename = JAMES Job = CLERK Sal = 950 

 Ename = FORD Job = ANALYST Sal = 3000 

 Ename = MILLER Job = CLERK Sal = 1300 

 

 SQL> EXEC ref_cursor('STUDENT') 

 Name = saketh 

 Name = srinu 

 Name = satish 

 Name = sudha

Nested Cursors:

• Oracle opens the nested cursor defined by a cursor expression implicitly as soon as it fetches the data containing the cursor expression from the parent or outer cursor
• Nested cursor closes if you closed explicitly
• Nested cursor closes whenever the outer or parent cursor is executed again or closed or cancelled
• Nested cursor closes whenever an exception is raised while fetching data from a parent cursor

Ex:

DECLARE

cursor c is select ename,cursor(select dname from dept d where e.empno = d.deptno)  from emp e;

type t is ref cursor;

c1 t;

c2 t;

v1 emp.ename%type;

v2 dept.dname%type;

BEGIN

open c;

loop

     fetch c1 into v1;

          exit when c1%notfound;

          fetch c2 into v2;

          exit when c2%notfound;

          dbms_output.put_line('Ename = ' || v1 || ' Dname = ' || v2);

end loop;

end loop;

close c;

END;

CURSOR CLAUSES:

                        1     Return

2     For update

3     Where current of

4     Bulk collect

RETURN:

         Cursor c return dept%rowtype is select *from dept;

Or

Cursor c1 is select *from dept;

Cursor c  return c1%rowtype is select *from dept;

Or

Type t is record(deptno dept.deptno%type, dname dept.dname%type);

Cursor c return t is select deptno, dname from dept;

FOR UPDATE AND WHERE CURRENT OF:

 Normally, a select operation will not take any locks on the rows being accessed. This will allow other sessions connected to the database to change the data being selected. The result set is still consistent. At open time, when the active set is determined, oracle takes a snapshot of the table. Any changes that have been committed prior to this point are reflected in the active set. Any changes made after this point, even if they are committed, are not reflected unless the cursor is reopened, which will evaluate the active set again.

However, if the FOR UPDATE clause is present, exclusive row locks are taken on the rows in the active set before the open returns. These locks prevent other sessions from changing the rows in the active set until the transaction is committed or rolled back. If another session already has locks on the rows in the active set, then SELECT … FOR UPDATE operation will wait for these locks to be released by the other session. There is no time-out for this waiting period. The SELECT…FOR UPDATE will hang until the other session releases the lock. To handle this situation, the NO WAIT clause is available.

 Syntax:

Select …from … for update of column_name [wait n];

If the cursor is declared with the FOR UPDATE clause, the WHERE CURRENT OF clause can be used in an update or delete statement.

Syntax:

Where current of cursor;

Ex:

DECLARE

       cursor c is select * from dept for update of dname;

BEGIN

       for v in c loop

             update dept set dname = 'aa' where current of c;

             commit;

       end loop;

END;

BULK COLLECT:

1     This is used for array fetches

2     With this you can retrieve multiple rows of data with a single round trip.

3     This reduces the number of context switches between the pl/sql and sql engines.

4     Reduces the overhead of retrieving data.

5     You can use bulk collect in both dynamic and static sql.

6     You can use bulk collect in select, fetch into and returning into clauses.

7   SQL engine automatically initializes and extends the collections you reference in the bulk collect clause.

8    Bulk collect operation empties the collection referenced in the into clause before executing the query.

9     You can use the limit clause of bulk collect to restrict the no of rows retrieved.

10  You can fetch into multiple collections with one column each.

11  Using the returning clause we can return data to the another collection.

BULK COLLECT IN FETCH

 Ex:

DECLARE

     Type t is table of dept%rowtype;

     nt t;

     Cursor c is select *from dept;

BEGIN

     Open c;

     Fetch c bulk collect into nt;

     Close c;

     For i in nt.first..nt.last loop

           dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' ||

                                                          nt(i).loc);

     end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

Dname = SALES Loc = CHICAGO

Dname = OPERATIONS Loc = BOSTON

BULK COLLECT IN SELECT

 Ex:

DECLARE

     Type t is table of dept%rowtype;

     Nt t;

BEGIN

     Select * bulk collect into nt from dept;

     for i in nt.first..nt.last loop

           dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' ||

                                                                nt(i).loc);

     end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

Dname = SALES Loc = CHICAGO

Dname = OPERATIONS Loc = BOSTON

LIMIT IN BULK COLLECT

 You can use this to limit the number of rows to be fetched.

 Ex:

DECLARE

     Type t is table of dept%rowtype;

     nt t;

     Cursor c is select *from dept;

BEGIN

     Open c;

     Fetch c bulk collect into nt limit 2;

     Close c;

     For i in nt.first..nt.last loop

           dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);

     end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

MULTIPLE FETCHES IN  INTO CLAUSE

 Ex1:

     DECLARE

           Type t is table of dept.dname%type;

           nt t;

           Type t1 is table of dept.loc%type;

           nt1 t;

           Cursor c is select dname,loc from dept;

      BEGIN

           Open c;

           Fetch c bulk collect into nt,nt1;

           Close c;

           For i in nt.first..nt.last loop

                  dbms_output.put_line('Dname = ' || nt(i));

           end loop;

           For i in nt1.first..nt1.last loop

                  dbms_output.put_line('Loc = ' || nt1(i));

           end loop;

      END;

Output:

Dname = ACCOUNTING

Dname = RESEARCH

Dname = SALES

Dname = OPERATIONS

Loc = NEW YORK

Loc = DALLAS

Loc = CHICAGO

Loc = BOSTON

Ex2:

DECLARE

      type t  is table of dept.dname%type;

      type t1 is table of dept.loc%type;

      nt t;

      nt1 t1;

BEGIN

      Select dname,loc bulk collect into nt,nt1 from dept;

      for i in nt.first..nt.last loop

           dbms_output.put_line('Dname = ' || nt(i));

      end loop;

      for i in nt1.first..nt1.last loop

           dbms_output.put_line('Loc = ' || nt1(i));

      end loop;

END;

Output:

Dname = ACCOUNTING

Dname = RESEARCH

Dname = SALES

Dname = OPERATIONS

Loc = NEW YORK

Loc = DALLAS

Loc = CHICAGO

Loc = BOSTON

RETURNING CLAUSE IN BULK COLLECT

 You can use this to return the processed data to the output variables or typed variables.

 Ex:

DECLARE

       type t is table of number(2);

       nt t := t(1,2,3,4);

       type t1 is table of varchar(2);

       nt1 t1;

       type t2 is table of student%rowtype;

       nt2 t2;

BEGIN

       select name bulk collect into nt1 from student;

       forall v in nt1.first..nt1.last

        E           update student set no = nt(v) where name = nt1(v) returning 

                              no,name,marks bulk collect into nt2;

       for v in nt2.first..nt2.last loop

               dbms_output.put_line('Marks = ' || nt2(v));

       end loop;

END;

Output:

Marks = 100

Marks = 200

Marks = 300

Marks = 400

POINTS TO REMEMBER

     1     Cursor name can be up to 30 characters in length.

2   Cursors declared in anonymous blocks or subprograms closes automatically when that block terminates execution.

3     %bulk_rowcount and %bulk_exceptions can be used only with forall construct.

4     Cursor declarations may have expressions with column aliases.

5     These expressions are called virtual columns or calculated columns.