Database Interaction with PL/SQL, User-defined Packages - TYPE declarations in package specification

(Page 4 of 5 )

A package specification can not only contain sub-programs, it can even contain TYPE declarations as well.  What is the use of declaring TYPE declarations at the level of package specification?  Why don’t we declare the same within the sub-programs (as we usually do)?

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 >>
 

More Oracle Articles
More By Jagadish Chatarji

Comments On: Database Interaction with PL/SQL, User-defined Packages

· Hello guys, this is my new article on working with packages in PL/SQL. You can...    · when i try one small example,it gives like thispackage,function,procedure and type...   >>> Post your comment now!