TYPE declarations in package specification - Oracle

This article is part of a series focusing on database interactions with Oracle PL/SQL. In my previous article, we examined named notation, default values of parameters, stored procedures, stored functions and finally took our first look at package and package body. In this article, we will focus completely on package and package body. Before reading this article I suggest you to go through my last three articles in this series thoroughly.

TABLE OF CONTENTS:

By: Jagadish Chatarji

Poor Best

Rating:

/ 29

September 12, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

If we declare any TYPE declaration within the package specification, it will be available to the public. The word ‘public’ has several meanings here. Any data structure declared in the specification of a package is a global, public data structure. This means that any program outside of the package can access the data structure. Let us see some of the issues of “public” declarations within package specification.

A public declaration is available to every sub-program within the same package.
It also acts like a stored data type (indirectly).
Other stored procedures (or even stored functions) can use the same TYPE any number of times.
PL/SQL programs can also use those types declared as public.
Apart from all of the above, even external sources can identify these public types and follow accordingly.

This makes life easier in almost all aspects without remembering and re-declaring the same TYPE again and again. You can also create a package of constants which are used throughout all of your programs. Then all developers will reference the packaged constants instead of hardcoding the values into their programs. It also proves the concept of single definition at single location, reused several times at several locations.

Let’s see an example for the above theory:

create or replace package SamplePkg as
    TYPE t_emprec is RECORD
    (
        name    emp.ename%TYPE,
        salary emp.sal%type
     );
     procedure dispEmp;
     procedure dispEmp(p_deptno dept.deptno%type);
end SamplePkg;
/

create or replace package body SamplePkg as
    procedure dispEmp as
        cursor c_emp is
            select ename, sal from emp;
        r_emp t_emprec;
    begin
        open c_emp;
        loop
            fetch c_emp into r_emp;
            exit when c_emp%notfound;
            dbms_output.put_line(r_emp.name || ',' || r_emp.salary);
        end loop;
        close c_emp;
     end;

     procedure dispEmp(p_deptno dept.deptno%type) as
         cursor c_emp is
             select ename, sal from emp
             where deptno = p_deptno;
         r_emp t_emprec;
     begin
         open c_emp;
         loop
             fetch c_emp into r_emp;
             exit when c_emp%notfound;
             dbms_output.put_line (r_emp.name || ',' || r_emp.salary);
          end loop;
          close c_emp;
      end;

end SamplePkg;
/

My next section would help you to understand the above package.

Next: How the above package works >>