Feb 25, 2016

Cursor Execution Cycle




  Detailed phase of cursor execution


 1. The cursor is opened
 2. Parse SQL
 3. The query is described to know the projection (what columns are going to be      
    returned, names, datatypes etc.)
 4. Bind SQL (Variable Binding)
 5. SELECT Query execution
 6. Move the record pointer to the first record


     FETCH


 7. A row of data is fetched 
 8. The data values from the columns within that row are extracted into the known    
      projection 
 9. Step 6 and 7 are repeated until there is no more data or another condition ceases
       the fetching

10. The cursor is closed

Restore point

 A restore point is an attribute of a database, not a transaction, so you can generate many transactions and still restore the entire database back to the restore point.  

Restoring to a restore point, however, will affect every change that had been made to the database between the time that you defined the restore point and the time that you do restore  you would lose all changes made not just by your session or your user but all sessions and users in the database. 


CREATE RESTORE POINT statement to create a restore point, which is a name associated with an SCN of the database corresponding to the time of the creation of the restore point.

A restore point can be used to flash a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.

SQL> CREATE RESTORE POINT before_upgrade;

Guaranteed restore points never age out of the control file and must be explicitly dropped.

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;
SQL> DROP RESTORE POINT before_app_upgrade;

V$RESTORE_POINT

CREATE RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
     12000

UPDATE employees SET salary = salary*10 WHERE employee_id = 108;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
    120000

COMMIT;

FLASHBACK TABLE employees TO RESTORE POINT good_data;

SELECT salary FROM employees WHERE employee_id = 108;

    SALARY
----------
     12000
 

Feb 17, 2016

SAVEPOINT


SAVEPOINT identify a point in a transaction to which you can later roll back.

Example

create table countries (region_id number,region_name varchar2(100),country_id varchar2(100)) ;
insert into COUNTRIES (REGION_ID, REGION_NAME, COUNTRY_ID) values (33, 'Middle East', null);
insert into COUNTRIES (REGION_ID, REGION_NAME, COUNTRY_ID) values (5, null, 'NG');

UPDATE countries SET region_name = 'Middle East' WHERE region_name = 'Middle East and Africa';

SAVEPOINT reg_rename;

UPDATE countries SET country_id = 'ZM'  WHERE region_id = 5;

SAVEPOINT zambia;

UPDATE countries SET country_id = 'NG'  WHERE region_id = 5 ;

SAVEPOINT nigeria;

UPDATE countries SET country_id = 'ZW'  WHERE region_id = 5;

SAVEPOINT zimbabwe;

UPDATE countries SET country_id = 'EG' WHERE  region_id = 5;

SAVEPOINT egypt;

ROLLBACK TO SAVEPOINT nigeria;

COMMIT;

Feb 11, 2016

Examples of Ref Cursor1

REF CURSOR Example


create or replace package strongly_typed is
    type return_cur is ref cursor return all_tables%rowtype;
    procedure child(p_return_rec out return_cur);
    procedure parent(p_numrecs pls_integer);
end strongly_typed;

-----------------------
create or replace package body strongly_typed is

procedure child(p_return_rec out return_cur) is
begin
   open p_return_rec for select * from all_tables;
end child;

procedure parent (p_numrecs pls_integer) is
p_retcur return_cur;

at_rec all_tables%rowtype;
begin
   child(p_retcur);
   for i in 1 .. p_numrecs loop fetch p_retcur into at_rec;
   dbms_output.put_line(at_rec.table_name || ' - ' || at_rec.tablespace_name || ' - ' || to_char(at_rec.initial_extent) || ' - ' || to_char(at_rec.next_extent));
   end loop;
end parent;

end strongly_typed;

Feb 8, 2016

Examples of Ref Cursor

SYS_REFCURSOR  Example
create or replace function f return SYS_REFCURSOR as
  c SYS_REFCURSOR;
begin
  open c for select empno  from employees;
  return c;
end;
----------------------------------------------------

declare
  refCursorValue  SYS_REFCURSOR;
  v varchar2(1);
begin
 refCursorValue := f();   -- Get ref cursor from function
 loop
   fetch refCursorValue into v;
   exit when refCursorValue%notfound;
   dbms_output.put_line('Value from cursor: '||v);
 end loop;
END;
----------------------XXXXXXXXXXXXXXXXXXXXXXXXXXX-------------

  declare
      refCursorValue  SYS_REFCURSOR;
      myRecord product%rowtype;
  begin
      open refCursorValue for select * from product ;
      loop
        fetch refCursorValue into myRecord;
        exit when refCursorValue%notfound;
     end loop;
     closerefCursorValue;
  end;

REF Cursors

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.

• A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. Ref Cursors are opened with an OPEN FOR statement

There are two types of refcursor
    Strong REF Cursor and Weak REF Cursor

Normal cursor is a static cursor in which the query is assigned at design time and cant be changed at run time. REF cursors are dynamic

 Ref cursor can be returned to a client. A PL/SQL cursor cannot be returned to a client. This allows you to pass that numeric cursor handle from PL/SQL code to an external client process.

 Another difference is a cursor can be global - a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)

 Ref cursor can be passed from subroutine to subroutine - a cursor cannot be.

 Strong REF Cursor : A REF CURSOR that specifies a specific return type
 Weak REF Cursor :  A REF CURSOR that does not specify the return type such as
      SYS_REFCURSOR. 

• A REF CURSOR involves an additional database round-trip.

•A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.

•A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.


1) A ref cursor can not be used in CURSOR FOR LOOP, it must be used in simple CURSOR LOOP statement as in example.

2) A ref cursor is defined at runtime and can be opened dynamically but a regular cursor is static and defined at compile time.

3) A ref cursor can be passed to another PL/SQL routine (function or procedure) or returned to a client. A regular cursor cannot be returned to a client application and must be consumed within same routine.

4) A ref cursor incurs a parsing penalty because it cannot cached but regular cursor will be cached by PL/SQL which can lead to a significant reduction in CPU utilization.

5) A regular cursor can be defined outside of a procedure or a function as a global package variable. A ref cursor cannot be; it must be local in scope to a block of PL/SQL code.

6) A regular cursor can more efficiently retrieve data than ref cursor. A regular cursor can implicitly fetch 100 rows at a time if used with CURSOR FOR LOOP. A ref cursor must use explicit array fetching.

Use of ref cursors should be limited to only when you have a requirement of returning result sets to clients and when there is NO other efficient/effective means of achieving the goal.Generally we use ref cursor when we need to return result set. Else it is advisable to use static cursor because they give better performance.

Different ways to declare REF CURSORS


   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  -- strong
   TYPE genericcurtyp IS REF CURSOR;  -- weak
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
  
my_cursor SYS_REFCURSOR; -- didn't need to declare a new type (From 9i onwards for weak refcursor use this )

   TYPE EmpRecTyp IS RECORD ( employee_id NUMBER,
      last_name VARCHAR2(25),  salary   NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;

   emp_cv EmpCurTyp;  -- declare cursor variable

 Opening a  REF CURSOR (Cursor Variable)

 OPEN  cursorname  FOR  vquery [(optional)  USING bindvariable_in_vquery ]
 -- reamining as in Cursor  loop,fetch ,exit ,end loop
 CLOSE cursorname