Home arrow Oracle arrow Page 4 - Database Interaction with PL/SQL, User-defined Packages

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.

  1. Database Interaction with PL/SQL, User-defined Packages
  2. Database interaction using a PACKAGE
  3. Overloading sub-programs in a PACKAGE
  4. TYPE declarations in package specification
  5. How the above package works
By: Jagadish Chatarji
Rating: starstarstarstarstar / 29
September 12, 2005

print this article



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;
        open c_emp;
            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;

     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;
         open c_emp;
             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 SamplePkg;

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

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: