Sayantan's Blog On Oracle

Working With Packages In Oracle

INTRODUCTION:

A package is a schema object. It groups logically related PLSQL types, variables, constants, subprogram, cursors and exceptions. A package can be compiled and stored in the database. There its contents can be shared by many applications.

Package has two parts:

• Specification
• Body

Package Parts:

Both specification and body are stored separately in the database.A specification can exist without a body. But for a body to be stored in database specification must be stored first. Specification is the mandatory component. In addition, it declares the public items that can be referenced from outside the package.

If the public items are cursors or subprograms then the package must also have a body. The body must define public cursors and public subprograms. The body can define private items that cannot be defined outside the package. But they are necessary for internal working off the package.

TYPE OF OBJECTS DECLARED IN PACKAGE SPECIFICATION:

The specification is the interface to your program. It declares the following:

• Types
• Variables
• Constants
• Exceptions
• Cursors
• Subprograms
• Pragmas (Directive to the compiler)

FEATURES OF PACKAGE:

The package itself cannot be

• Called
• Parameterized
• Nested

When you call a packaged PLSQL constructs for the first time, the whole package is loaded into the memory.

So the later calls to constructs in the same package require no disk I/O.

AUTHID CLAUSE:

The AUTHID clause is part of the package specification.

And it determines whether any package subprogram or cursor run with the privilege of their definer or the invoker.

• AUTHID DEFINER
• AUTHID CURRENT_USER

ACCESSIBLE BY:

Accessible by clause of the package specification lets you specify a white list of PLSQL units that can access the package.

TYPES OF VISIBILITY CONSTRUCT:

• LOCAL
• GLOBAL

LOCAL:

Private variable(Local to the package): You can define variables in a package body. These variables can be accessed only by the other objects in the same package. In addition, they are not visible to any subprogram or objects outside of the package.

GLOBAL:

A variable or subprogram that can be referenced or changed outside the package.

So it is visible to external users who are granted privileges on the package.

In other words, global package items must be declared in the package specification.

GUIDELINES OF DEVELOPING A PACKAGE:

• Constructs packages for general use
• Define the package specification before the body
• The package specification should contain only those constructs that you want to be public.
• Place items in the declaration part of the package body when you must maintain them through out a session or across the transactions.
• Changes to the package body do not require recompilation of dependent constructs.
• On the other hand, changes to the package specification require recompilation of each stored referencing subprogram.
• The package specification should contain as few constructs as possible. To reduce the need for recompiling when the code is change, place as few constructs as possible in a package specification.

COMPILATION OF A PACKAGE:

Creating a sample package:

create or replace package test_package_compile
is
  procedure test_proc;
  function test_fun return number;
end;
/
create or replace package body test_package_compile
is
  procedure test_proc
  is
  begin
    dbms_output.put_line('Inside procedure test_proc.');
  end;

  function test_fun
  return number
  is
  begin
    dbms_output.put_line('Inside function test_fun');
    return 0;
  end;

end;
/

COMPILE keyword is required to compile a package specification or body.

Compiling a package specification:

alter package test_package_compile compile;

Compiling a package body:

alter package test_package_compile compile body;

REUSE SETTINGS:

During compilation, the database drops all persistent compiler settings. After that it retrieves them again from session, and stores them at the end of compilation. To avoid this process use REUSE SETTINGS clause.

alter package test_package_compile compile reuse settings;
alter package test_package_compile compile body reuse settings;

When you recompile a package specification, the database invalidates any local objects that depend on the specification. For instance procedures that calls procedures or functions in the package. The body of a package is also depends on its specification. Let us suppose we subsequently reference any of these dependent objects without explicitly first recompiling it. Then the database implicitly recompile it during runtime. Specify BODY keyword to compile a package body regardless of whether its invalid. You might want to recompile a package body after modifying it. However, recompiling a package body does not invalidate objects that depend upon the package specification.

ADVANTAGES OF PACKAGES:

• MODULARITY
• EASIER APPLICATION DESIGN
• HIDING INFORMATION
• PERSISTENCY OF VARIABLE AND CURSORS
• BETTER PERFORMANCE
• OVERLOADING

MODULARITY:

Package enables you to encapsulate or group logically related programming structures in a named module.

EASIER APPLICATION DESIGN:

Encapsulation provides better organization and flexibility during development of packages. All you need initially is the interface information in the package specification. You can code and compile a specification without its body. You can create package specification and public constructs and refer them outside without actually creating the package body. Also Encapsulation makes easier the privilege process. In other words, granting privileges for a package makes its constructs accessible to the grantee.

HIDING INFORMATION:

You can decide which constructs are to be declared as public or private. In other words, only the declarations in the package specification are visible and accessible to the applications. But, the package body hides the definition of the private constructs. So that only the package is affected if the definition changes. In conclusion, this enables you to change the implementation without having to recompile calling programs. Also by hiding implementation details from users, you protect the integrity of the package.

PERSISTENCY OF VARIABLE AND CURSORS:

Packaged variables and cursors persist for the duration of a session. For instance, they can be shared by all subprograms that execute in the environment. Also they enable you to maintain data across transactions without having to store in the database. Private constructs also persists for the duration of the session, but can only be accessed within the package.

BETTER PERFORMANCE:

Point 1:

When you call a packaged subprogram the first time, the entire package is loaded into memory (SGA). So other package elements are thereby made immediately available for future calls to the package. And this way later calls to related subprograms in the package require no further disk I/O. The PLSQL runtime engine doesn't have to keep retrieving program elements or data from disk whenever a new object is referenced.

Point 2:

Oracle RDBMS automatically tracks the validity of all programs objects such as procedures, functions, packages stored in the database. It determines what other objects the that program is dependent on, such as tables. If any dependent object changes, e.g structure of the table, then all program rely on that object are marked as invalid. The database then automatically recompiles these invalid programs when they are referenced next. In addition, you can restricts the need for recompilation by placing these functions and procedures inside a package.

Point 3:

A package body can be replaced and recompiled without affecting the specification. So that the schema objects that references a package construct need not be recompiled. Those schema objects need to be recompiled only when package specification is replaced. So by using package, unnecessary recompilation can be minimized which in turn helps to increase the overall database performance.If a procedure call_test_package_compile calls test_proc procedure of package test_package_compile it does so through the package's specification. So as long as the package specification doesn't change, any program that calls test_proc is not marked as invalid. In other words it will not have to be recompiled.

OVERLOADING:

With package you can overload procedures and functions. In other words, we can create multiple subprograms with the same name in the same package. In addition each of them taking parameters in different number or datatype.

EXAMPLE OF PACKAGE:

CREATING A PACKAGE SPECIFICATION:

create or replace package employee_details_pkg
is
  v_empid employees.employee_id%type := null;
  v_deptno employees.department_id%type := null;
  v_commission_pct constant number := 0.05;
  
  type v_emp_record is record 
  (employee_id      number,
   first_name       varchar2(30),
   last_name        varchar2(30),
   job_id           varchar2(30),
   hire_date        date,
   department_id    number
  );
  type v_emp_rec_tab is table of v_emp_record index by pls_integer;
  v_emp_tab v_emp_rec_tab;
  
  type v_emp_address_record is record 
  (employee_id      number,
   email            varchar2(30),
   phone_number     varchar2(30)
  );
  type v_emp_address_rec_tab is table of v_emp_address_record index by pls_integer;
  v_emp_address_tab v_emp_address_rec_tab;
  
  type v_emp_sal_record is record 
  (employee_id      number,
   salary           number,
   commission_pct   number
  );
  type v_emp_sal_rec_tab is table of v_emp_sal_record index by pls_integer;
  v_emp_sal_tab v_emp_sal_rec_tab;
  
  cursor emp_cur
  is
  select employee_id,
         first_name,
         last_name,
         job_id,
         hire_date,
         email,
         phone_number,
         salary,
         commission_pct,
         department_id,
         manager_id
         from employees;
  v_emp_cur_rec emp_cur%rowtype;
  
  procedure emp_details(p_deptno employees.department_id%type);
  procedure emp_address_details(p_deptno employees.department_id%type);
  procedure emp_salary_details(p_deptno employees.department_id%type);
  function fun_emp_exists(p_empid employees.employee_id%type) return boolean;
end;
/

TABLE SETUP:

create table emp_details 
(
 employee_id      number,
 first_name       varchar2(30),
 last_name        varchar2(30),
 job_id           varchar2(30),
 hire_date        date,
 department_id    number
);

create table emp_address_details
(
 employee_id      number,
 email            varchar2(30),
 phone_number     varchar2(30)
);

create table emp_sal_details
(
 employee_id      number,
 salary           number,
 commission_pct   number 
);

CREATING PACKAGE BODY:

create or replace package body employee_details_pkg
is
  function fun_emp_exists(p_empid employees.employee_id%type) 
  return boolean
  is
    v_count number := 0;
  begin
    select count(1)
    into v_count
    from employees
    where employee_id = p_empid;
    
    if v_count > 0 then
      return true;
    else
      return false;
    end if;
  exception
    when others then
      dbms_output.put_line('Error -> '||sqlerrm);
      return false;
  end fun_emp_exists;
  
  procedure emp_details(p_deptno employees.department_id%type) 
  is 
    cursor cur_emp_details
    is
    select employee_id,
           first_name,
           last_name,
           job_id,
           hire_date,
           department_id
    from employees
    where department_id = p_deptno;
  begin 
    execute immediate 'truncate table emp_details';
    open cur_emp_details;
    loop
      fetch cur_emp_details
      bulk collect into v_emp_tab limit 20;
      exit when v_emp_tab.count = 0;
      
      forall i in 1..v_emp_tab.count
        insert into emp_details
        values(v_emp_tab(i).employee_id,
               v_emp_tab(i).first_name,
               v_emp_tab(i).last_name,
               v_emp_tab(i).job_id,
               v_emp_tab(i).hire_date,
               v_emp_tab(i).department_id);
    end loop;
  exception
    when others then
      dbms_output.put_line('Error -> '||dbms_utility.format_error_backtrace);
  end emp_details;

  procedure emp_address_details(p_deptno employees.department_id%type) 
  is 
    cursor cur_emp_address
    is
    select employee_id,
           email,
           phone_number
    from employees
    where department_id = p_deptno;
    
  begin 
    execute immediate 'truncate table emp_address_details';
    open cur_emp_address;
    loop
      fetch cur_emp_address
      bulk collect into v_emp_address_tab limit 20;
      exit when v_emp_address_tab.count = 0;
      
      forall i in 1..v_emp_address_tab.count
        insert into emp_address_details
        values(v_emp_address_tab(i).employee_id,
               v_emp_address_tab(i).email,
               v_emp_address_tab(i).phone_number);
    end loop;
  exception
    when others then
      dbms_output.put_line('Error -> '||dbms_utility.format_error_backtrace);    
  end emp_address_details;
  
  procedure emp_salary_details(p_deptno employees.department_id%type) 
  is
    cursor cur_emp_sal
    is
    select employee_id,
           salary,
           commission_pct
    from employees
    where department_id = p_deptno;
  begin 
    execute immediate 'truncate table emp_sal_details'; 
    open cur_emp_sal;
    loop
      fetch cur_emp_sal
      bulk collect into v_emp_sal_tab limit 20;
      exit when v_emp_sal_tab.count = 0;
      
      forall i in 1..v_emp_sal_tab.count
        insert into emp_sal_details
        values(v_emp_sal_tab(i).employee_id,
               v_emp_sal_tab(i).salary,
               v_emp_sal_tab(i).commission_pct);
    end loop;
  exception
    when others then
      dbms_output.put_line('Error -> '||dbms_utility.format_error_backtrace);
  end emp_salary_details;
  
end employee_details_pkg;
/

OVERLOADING IN PACKAGE:

Overloading feature enables you to define different subprograms with the same name. You can distinguish the subprograms both by name and by parameters. Sometimes the processing in two subprograms is the same, but the parameters passed to them varies. In that case its logical to give them the same name. PLSQL determines which subprogram is called by checking formal parameters. In addition, only local or packaged subprograms are overloaded. However, stand-alone subprograms cannot be overloaded.

OVERLOADING RESTRICTIONS:

You cannot overload two subprograms:

If there formal parameters differ only in datatype and the different datatypes are in the same family. For instance, NUMBER and DECIMAL belong to the same family. If there formal parameters differ only in subtype. In addition, the different subtypes are based on types in the same family. For instance VARCHAR and STRING are PLSQL subtypes of VARCHAR2. Tow functions that differ only in return type, even if the types are in different families.

Note: The above restrictions apply if the names of the parameters are also the same.

However, if you use different names, then you can invoke the subprograms by using named notation for the parameters.

EXAMPLE OF OVERLOADING:

create or replace package pkg_overloading_test
is
  v_empname varchar2(30) := null;
  v_deptno number := null;
  v_salary number := null;
  procedure sp_show_emp_details(p_empid number);
  procedure sp_show_emp_details(p_empid number, p_deptno number);
  procedure sp_show_emp_details(p_empid integer, p_deptno number);
  procedure sp_show_emp_details(p_empid number, p_deptno out number);
  function sp_show_emp_details(p_empid number) return varchar2;
  function sp_show_emp_details(p_empid integer, p_deptno number) return varchar2;
  function sp_show_emp_details(p_empid integer, p_deptno number) return number;
end pkg_overloading_test;
/

create or replace package body pkg_overloading_test
is
  procedure sp_show_emp_details(p_empid number)
  is
  begin
    begin
      select last_name
      into v_empname
      from employees
      where employee_id = p_empid;
    exception
      when others then
        v_empname := null;
    end;
    dbms_output.put_line('Name = '||v_empname);
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
  end sp_show_emp_details;
  procedure sp_show_emp_details(p_empid number, p_deptno number)
  is
  begin
    begin
      select last_name
      into v_empname
      from employees
      where employee_id = p_empid
      and department_id = p_deptno;
    exception
      when others then
        v_empname := null;
    end;
    dbms_output.put_line('Name = '||v_empname);
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
  end sp_show_emp_details;
  procedure sp_show_emp_details(p_empid integer, p_deptno number)
  is
  begin
    begin
      select last_name
      into v_empname
      from employees
      where employee_id = p_empid
      and department_id = p_deptno;
    exception
      when others then
        v_empname := null;
    end;
    dbms_output.put_line('Name = '||v_empname);
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
  end sp_show_emp_details;
  procedure sp_show_emp_details(p_empid number, p_deptno out number)
  is
  begin
    begin
      select department_id
      into v_deptno
      from employees
      where employee_id = p_empid
      and department_id = p_deptno;
    exception
      when others then
        v_deptno := null;
    end;
    p_deptno := v_deptno;
    dbms_output.put_line('Department Id = '||v_deptno);
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
  end sp_show_emp_details;
  function sp_show_emp_details(p_empid number) 
    return varchar2
  is
  begin
    begin
      select last_name
      into v_empname
      from employees
      where employee_id = p_empid;
    exception
      when others then
        v_empname := null;
    end;
    dbms_output.put_line('Department Id = '||v_deptno);
    return v_empname;
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
      v_empname := null;
      return v_empname;
  end sp_show_emp_details;
  function sp_show_emp_details(p_empid integer, p_deptno number) 
    return varchar2
  is
  begin
    begin
      select last_name
      into v_empname
      from employees
      where employee_id = p_empid
      and department_id = p_deptno;
    exception
      when others then
        v_empname := null;
    end;
    dbms_output.put_line('Department Id = '||v_deptno);
    return v_empname;
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
      v_empname := null;
      return v_empname;
  end sp_show_emp_details;
  function sp_show_emp_details(p_empid integer, p_deptno number) 
    return number
  is
  begin
    begin
      select salary
      into v_salary
      from employees
      where employee_id = p_empid
      and department_id = p_deptno;
    exception
      when others then
        v_salary := null;
    end;
    dbms_output.put_line('Salary = '||v_salary);
    return v_salary;
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
      v_salary := null;
      return v_salary;
  end sp_show_emp_details;
  
end pkg_overloading_test;
/

EXAMPLE OF FUNCITON OVERLOADING:

Most built-in subprograms are overloaded. For instance, the function TO_CHAR in STANDARD package has four different declarations.

FUNCTION TO_CHAR(p1 DATE) RETURN VARCHAR2;
FUNCTION TO_CHAR(p2 NUMBER) RETURN VARCHAR2;
FUNCTION TO_CHAR(p1 DATE, p2 VARCHAR2) RETURN VARCHAR2;
FUNCTION TO_CHAR(p1 NUMBER, p2 VARCHAR2) RETURN VARCHAR2;

If you redeclare a built-in subprogram in a PLSQL program, your local declaration overrides the global declaration.

In addition, to access the built-in subprogram, you need to qualify it with its package name. For instance, you have redeclared the built-in TO_CHAR function. Then to access the built-in function you refer it as STANDARD.TO_CHAR.

create or replace package pkg_overloading_test
is
  v_hiredate varchar2(100) := null;
  v_hiredate_local varchar2(100) := null;
  v_hiredate_standard varchar2(100) := null;
  
  function to_char(p_empid number) return varchar2;
  procedure sp_show_emp_details(p_empid number);
end pkg_overloading_test;
/

create or replace package body pkg_overloading_test
is
  function to_char(p_empid number)
    return varchar2
  is
  begin
    begin
      select to_char(hire_date, 'fm ddspth month, year')
      into v_hiredate
      from employees
      where employee_id = p_empid;
    exception
      when others then
        v_hiredate := null;
    end;
    return v_hiredate;
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
      v_hiredate := null;
      return v_hiredate;
  end to_char;
  procedure sp_show_emp_details(p_empid number)
  is
  begin
    begin
      v_hiredate_local := to_char(p_empid);
      v_hiredate_standard := standard.to_char(to_date('17-JUN-2003', 'DD-MON-RRRR'), 'DD/MM/RRRR HH24:MI:SS');
    exception
      when others then
        v_hiredate_local := null;
        v_hiredate_standard := null;
    end;
    dbms_output.put_line('Hire Date Local = '||v_hiredate_local);
    dbms_output.put_line('Hire Date Standard = '||v_hiredate_standard);
  exception
    when others then
      dbms_output.put_line('Error = '||sqlerrm);
  end sp_show_emp_details;
  
end pkg_overloading_test;
/

set serveroutput on;
begin
  pkg_overloading_test.sp_show_emp_details(100);
end;
/

Hire Date Local =  seventeenth june, two thousand three
Hire Date Standard = 17/06/2003 00:00:00

PL/SQL procedure successfully completed.

Let us suppose you have redeclared a built-in subprogram as a stand-alone subprogram.

Then to be able to access your subprogram you need to qualify it with your schema name. For instance, HR.TO_CHAR.

create or replace function to_char(p_empid number)
  return varchar2
is
  v_hiredate varchar2(100);
begin
  begin
    select to_char(hire_date, 'fm ddspth month, year')
    into v_hiredate
    from employees
    where employee_id = p_empid;
  exception
    when others then
      v_hiredate := null;
  end;
  --dbms_output.put_line('Hire Date = '||v_hiredate);
  return v_hiredate;
exception
  when others then
    dbms_output.put_line('Error = '||sqlerrm);
    v_hiredate := null;
    return v_hiredate;
end to_char;
/

Calling:

select hr.to_char(100) hire_date from dual;
select to_char(100) hire_date from dual;

FORWARD DECLARATION:

PLSQL does not allow forward references. You must declare an identifier before using it. Therefore a subprogram must be declared before using it. PLSQL enables for a special subprogram declaration called a forward declaration. It consists of the subprogram specification terminated by a semicolon.

And you can use forward declaration to do the following:

• Define subprograms in logical or alphabetical order.
• Define mutually recursive subprograms and
• Group subprograms in a package.

Mutually recursive programs are programs that call each other directly or indirectly.

Example:

create or replace package forward_pkg
is
  v_name varchar2(100) := null;
  v_cnt  number := 0;
  procedure sp_emp_details(p_empid employees.employee_id%type);
end;
/

create or replace package body forward_pkg
is
  function emp_exists(p_empid employees.employee_id%type) return boolean;
  procedure sp_emp_details(p_empid employees.employee_id%type)
  is
  begin
    if emp_exists(p_empid) then
      begin
        select first_name||' '||last_name
        into v_name
        from employees
        where employee_id = p_empid;
        dbms_output.put_line('Name - '||v_name);
      exception
        when others then
          v_name := null;
          dbms_output.put_line('Error in fetching Employee Name');
      end;
    else
      v_name := null;
      dbms_output.put_line('Employee does not exists.');
    end if;
  exception
    when others then
      dbms_output.put_line('Error - '||sqlerrm);
  end sp_emp_details;
  
  function emp_exists(p_empid employees.employee_id%type) 
    return boolean
  is
  begin
    select count(1)
    into v_cnt
    from employees
    where employee_id = p_empid;
    
    if v_cnt > 0 then
      return true;
    else
      return false;
    end if;
  exception
    when others then
      dbms_output.put_line('Error - '||sqlerrm);
      return false;
  end emp_exists;  
end forward_pkg;
/

set serveroutput on;
exec forward_pkg.sp_emp_details(100);

Name - Steven King

PL/SQL procedure successfully completed.

exec forward_pkg.sp_emp_details(1001);

Employee does not exists.

PL/SQL procedure successfully completed.

RESTRICTION ON PACKAGE FUNCTION USED IN SQL:

A Function called from:

• A query or DML statement may not
  • end the current transaction,
  • create or roll back to a savepoint and
  • alter the system or session.

• A query statement or parallelized DML statement
  • may not execute a DML statement
  • modify the database

• A DML statement may not read or modify the particular table being modified by that statement.

PERSISTENT STATE OF A PACKAGE VARIABLE:

Suppose you connect to the database and modify the value of in a package variable, e.g. from 10 to 20. After that, you or someone else invoke the package again to read the value of the variable. What will you/they see: 10 or 20 (it depends). Real application often invoke the same package many times. Therefore it is important to understand when the values in a package variable are kept (persists). And when they are lost.

PACKAGE STATE:

The collection of package variables and their current values define the package state.

The package state is:

• Initialized when the package is first loaded.
• Persists (by default) for the life of the session
  • Stored in session's private memory area
  • Unique to each session even if the second session is started by the same user
  • Subject to change when package subprogram is called or public variables are modified.
• Other sessions each have their own package state, and do not see your changes.

EXAMPLE OF PACKAGE STATE:

create or replace package pers_pkg
is
  g_var number := 10;
  procedure upd_g_var(p_var number);
end pers_pkg;
/

create or replace package body pers_pkg
is
  procedure upd_g_var(p_var number)
  is
  begin
    g_var := p_var;
  end upd_g_var;
end pers_pkg;
/

grant execute on pers_pkg to san, san_test;

connect to san schema;

set serveroutput on;
begin
  dbms_output.put_line('g_var = '||hr.pers_pkg.g_var);
end;
/

g_var = 10

PL/SQL procedure successfully completed.

begin
  hr.pers_pkg.upd_g_var(20);
  dbms_output.put_line('g_var = '||hr.pers_pkg.g_var);
end;
/

g_var = 20

PL/SQL procedure successfully completed.

connect to san_test schema

set serveroutput on;
begin
  dbms_output.put_line('g_var = '||hr.pers_pkg.g_var);
end;
/

g_var = 10

PL/SQL procedure successfully completed.

begin
  hr.pers_pkg.upd_g_var(30);
  dbms_output.put_line('g_var = '||hr.pers_pkg.g_var);
end;
/

g_var = 30

PL/SQL procedure successfully completed.

Go to San schema

begin
  dbms_output.put_line('g_var = '||hr.pers_pkg.g_var);
end;
/

g_var = 20

PL/SQL procedure successfully completed.

connect to a new session of san schema

set serveroutput on;
begin
  dbms_output.put_line('g_var = '||hr.pers_pkg.g_var);
end;
/

g_var = 10

PL/SQL procedure successfully completed.

The changes would not be visible in other session, even if both sessions are connected under the same user name.

PERSISTENT STATE OF A PACKAGE CURSOR:

• A cursor declared in the package specification is a type of global variable.
• In other words it follows the same persistency rules as any other variable.
• A cursor's state is not defined by a single numeric or other value and it consists of the following attributes:
  • whether the cursor is opened or closed
  • if open, how many rows have been fetched from the cursor (%ROWCOUNT) and whether the most recent fetch was successful (%FOUND or %NOTFOUND)

The cursor declaration is declared globally within the package specification.

Therefore any or all of the package procedures can reference it.

Package Specification:

create or replace package curs_pkg
is
  cursor emp_curs
  is
  select employee_id
  from employees
  order by employee_id;
  procedure open_curs;
  function fetch_n_rows(n number := 1) return boolean;
  procedure close_curs;
end curs_pkg;
/

Package Body:

create or replace package body curs_pkg
is
  procedure open_curs
  is
  begin
    if not emp_curs%isopen then
      open emp_curs;
    end if;
  end open_curs;
  function fetch_n_rows(n number := 1)
  return boolean
  is
    v_empid employees.employee_id%type;
  begin
    for i in 1..n
    loop
      fetch emp_curs into v_empid;
      exit when emp_curs%notfound;
      dbms_output.put_line('Empid : '||v_empid);
    end loop;
    return emp_curs%found;
  end fetch_n_rows;
  procedure close_curs
  is
  begin
    if emp_curs%isopen then
      close emp_curs;
    end if;
  end close_curs;
end curs_pkg;
/

Invoking Curs_pkg:

• 1st Step: open the cursor
• 2nd Step: (in loop) fetches and displays the next 3 rows from the cursor untill all the rows have been fetched.
• 3rd Step: closes the cursor.

declare
  v_more_rows_exists boolean := true;
begin
  curs_pkg.open_curs;                                     --1
  loop
    v_more_rows_exists := curs_pkg.fetch_n_rows(3);       --2
    dbms_output.put_line('------');
    exit when not v_more_rows_exists;
  end loop;
  curs_pkg.close_curs;                                    --3
end;
/

Empid : 100
Empid : 101
Empid : 102
------
Empid : 103
Empid : 104
Empid : 105
------
Empid : 106

The first looped call to fetch_n_rows displays the first 3 rows.

The second time round the loop, the next 3 rows are fetched and displayed and so on.

This technique often used in applications that need to fetch a large no of rows from a cursor.

But can only display a few of them at a time.

DATA DICTIONARY USED IN PACKAGE:

select OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, LAST_DDL_TIME, STATUS
from user_objects 
where object_name = 'CURS_PKG';

select OBJECT_NAME, PROCEDURE_NAME, OBJECT_ID, SUBPROGRAM_ID, OBJECT_TYPE 
from user_procedures 
where object_name = 'CURS_PKG';

OBJECT_NAME	PROCEDURE_NAME	OBJECT_ID	SUBPROGRAM_ID	OBJECT_TYPE
CURS_PKG	OPEN_CURS	89413	2	PACKAGE
CURS_PKG	FETCH_N_ROWS	89413	3	PACKAGE
CURS_PKG	CLOSE_CURS	89413	4	PACKAGE

RELATED TOPICS:

• WORKING WITH PROCEDURES IN ORACLE