May 29, 2013


         Whenever a SQL statement is executed, Oracle automatically allocates a memory area (known as context area) in Oracle database PGA i.e. Process Global Area. This allocated memory space is the query work area which holds the query related information.This is know as cursors

OPEN stage

•PGA memory allocation for cursor processing (OPEN Cursor)
•Parsing of SELECT statement (Parse SQL)
•Variable binding (Bind SQL)
•SELECT Query execution (Execute Query)
•Move the record pointer to the first record

FETCH stage

The record, to which the record pointer points, is pulled from the result set. The record pointer moves only in the forward direction. The FETCH phase lives until the last record is reached.

CLOSE stage

After the last record of the result set is reached, cursor is closed, and allocated memory is flushed off and released back to SGA. Even if an open cursor is not closed, oracle automatically closes it after the execution of its parent block

Cursor FOR loops

Cursor FOR loops improvise upon the performance and code interactivity by their implicit actions

Parameterized Cursors

Parameterized cursors enables programmer to pass parameter to the cursors

Dynamic Cursor FOR Loops


FOR UPDATE OF clause is used to lock a set of rows in a session. This concept can be used in explicit cursors to impose exclusive row level lock on all the rows contained by the cursor query result set. These rows will remain locked until the session issues ROLLBACK or COMMIT.

Oracle provides WHERE CURRENT OF clause to update or delete the rows which are locked by the FOR UPDATE OF cursor in the session

May 7, 2013

Oracle Collection - Varrays

Varrays hold a fixed number of elements, although the number of elements can be changed at runtime. Like nested tables, varrays use sequential numbers as the index or key to the elements. You can define equivalent SQL types, allowing varrays to be stored in database tables. Varrays are a good choice when the number of elements is known in advance, and when the elements are likely to be accessed in sequence.

      A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same data type. The number of elements in a VARRAY is the "size" of the VARRAY. You must specify a maximum size (but not a minimum size) when you declare the VARRAY type.

         In general, the VARRAY type should be used when the number of items to be stored is small; it is not suitable for large numbers of items or elements. Note that you cannot index or constrain VARRAY values. Varray is available in PL/SQL as well as in SQL

Arrays must be dense (have consecutive subscripts). So, you cannot delete individual elements from an array

  • Use to preserve ordered list
  • Use when working with a fixed set, with a known number of entries
  • Use when you need to store in the database and operate on the Collection as a whole

  •   Varrays in SQL

    CREATE OR REPLACE TYPE varray_type AS VARRAY(SIZE) OF element_type;

     Varrays in PLSQL

    TYPE varray_type IS VARRAY(SIZE) OF element_type (datatype) ;

    varray_name varray_type; (variable type in PL/SQL)

    varray_name := varray_type();(create it using the constructor)

    CREATE TABLE table_name(field1 [VARCHAR2 NUMBER DATE],field2 varray_type);

    Example :
    CREATE TYPE Project AS OBJECT ( project_no NUMBER(2), title  VARCHAR2(35),cost  NUMBER(7,2));

    CREATE TYPE ProjectList AS VARRAY(50) OF Project;

    CREATE TABLE department ( dept_id  NUMBER(2), name  VARCHAR2(15), budget NUMBER(11,2), projects ProjectList);

       INSERT INTO department
          VALUES(30, 'Accounting', 1205700,
             ProjectList(Project(1, 'Design New Expense Report', 3250),
                         Project(2, 'Outsource Payroll', 12350),
                         Project(3, 'Evaluate Merger Proposal', 2750),
                         Project(4, 'Audit Accounts Payable', 1425)));

       new_projects ProjectList :=
          ProjectList(Project(1, 'Issue New Employee Badges', 13500),
                      Project(2, 'Develop New Patrol Plan', 1250),
                      Project(3, 'Inspect Emergency Exits', 1900),
                      Project(4, 'Upgrade Alarm System', 3350),
                      Project(5, 'Analyze Local Crime Stats', 825));
       UPDATE department
          SET projects = new_projects WHERE dept_id = 60;


    May 3, 2013

    Oracle Collection - Nested tables

    Nested tables

             Nested tables hold an arbitrary number of elements and use sequential numbers as the index or key to the elements. Nested tables can be stored in database tables and manipulated through SQL. They are appropriate for data relationships that must be stored persistently. Nested tables are flexible in that arbitrary elements can be deleted, rather than just removing an element from the end. Note that the order and subscripts (keys) of nested tables are not preserved as the table is stored and retrieved in the database

       Nested tables can be stored in a database column.Nested tables are initially dense, but they can become sparse (Data does not have to be stored in consecutive rows) when elements are deleted. Nested Table is available in PL/SQL as well as in SQL.

    Nested tables are dense, but they can become sparse (have nonconsecutive subscripts). So, you can delete elements from a nested table using the built-in procedure DELETE. That might leave gaps in the index, but the built-in function NEXT lets you iterate over any series of subscripts

    CREATE OR REPLACE TYPE nestedtable_type AS TABLE OF element_type; (PLSQL Type in SQL)

    CREATE TABLE nested_table (id NUMBER, col1 nestedtable_type) NESTED TABLE col1 STORE AS col1_tab;   (Nested Table)

    TYPE nestedtable_type IS TABLE OF element_type; (PLSQL Type in PLSQL)

    Oracle Collection - PLSQL Table


        A collection is an ordered group of elements ,of the same type.Collection have only one dimension,we can nodel multi-dimensional arrays by creating collections whose elements are also collections.

    The three type of collections are following

    1) Associative Array
    2) Nested tables
    3) Varrays

    Associative Array
    TYPE array_type IS TABLE OF element_type INDEX BY key_type;
    array_name array_type;
    array_name(index) := value;

    The “Associative Arrays” are also known as “Index-By” tables in PL/SQL.Associative arrays are single-dimensional, unbounded, sparse collections of homogeneous elements. Associative arrays can be sparse, which means not all elements between two elements need to be defined.Similar to hash table in other language
    For instance you can have an element at index -29 and one at index 12 with nothing in between. Homogeneous elements mean every element must be of the same type.
    Associative arrays are useful for small to medium sized lookup tables where the array can be constructed in memory ( only , which is destroyed after the session ends) each time a procedure is called or a package is initialized. There is no fixed limit on their size and their index values are more flexible- associative array keys can be negative and/or nonsequential, and associative arrays can use string values instead of numbers .The Associative Array is only available in PL/SQL

    type my_tab_t is table of number index by pls_integer/ binary_integer;
    type my_tab_t is table of number index by varchar2(4000);
    type my_tab_t is table of tab.value%TYPE index by;
    Example :
      cursor c_customer is select cust_name,cust_no,rownum from customers;
      type type_cname is table of customers% rowtype index by binary_integer;
      tab_cname type_cname;
      v_counter number:=0;


    for r_customer in c_customer loop
       tab_cname(v_counter) := r_customer.cust_name ;
    end loop;

    forall i in tab_cname.first .. tab_cname.last

    PLSQL Table attributes

    DELETE - Delete rows in a table.
    EXISTS - Return TRUE if the specified entry exists in the table.
    COUNT - Returns the number of rows in the table .
    FIRST - Returns the index of the first in the table.
    LAST - Returns the index of last row in the table.
    NEXT - Returns the index of the last row in the table .
    PRIOR - Returns the index of previous row in the table before the specified row.

    Use NOCOPY hint to reduce overhead of passing collections in and out of program units