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

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;

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;

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;