Global / Package based cursors - pl/sql

This type of cursors are written to facilitate other languages to use oracle table in their queries. Widely used by Java develpers. Another advantage of writing cusrors at package level is that they can be used by any other procedures / functions and save from rewriting most commonly used cursors repeatedly.

To write a global / package level cursor we may need at least folloiwng 2 steps.

1. Defind a global cursor in package specs

create or replace package read_cursor_pkg is
    cursor c_read_employees is
    select * from employees;
    read_employees c_read_employees%ROWTYPE;
    procedure run_query;
end;
/

2.  Use package body to open and fetch the cursor

create or replace package body read_cursor_pkg is
    procedure run_query is
    begin
        open read_cursor_pkg.c_read_employees ;
        loop
            fetch read_cursor_pkg.c_read_employees into read_cursor_pkg.read_employees;
            exit when read_cursor_pkg.c_read_employees%NOTFOUND;
            DBMS_OUTPUT.put_line(
                                 read_cursor_pkg.read_employees.EMPLOYEE_ID ||' - ' ||
                                 read_cursor_pkg.read_employees.FIRST_NAME  ||' - ' ||
                                 read_cursor_pkg.read_employees.first_Name
                                );
        end loop;
        close read_cursor_pkg.c_read_employees;
     end;
end;
/


SQL>set serveroutput on
SQL>execute read_cursor_pkg.run_query ;
Package body created.
SQL>
SQL> set serveroutput on
SQL> execute read_cursor_pkg.run_query ;
198 - Donald - Donald
199 - Douglas - Douglas
200 - Jennifer - Jennifer
201 - Michael - Michael
202 - Pat - Pat
203 - Susan - Susan
204 - Hermann - Hermann
205 - Shelley - Shelley
206 - William - William
100 - Steven - Steven
101 - Neena - Neena
102 - Lex - Lex
103 - Alexander - Alexander

PL/SQL procedure successfully completed.
SQL>