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

Database Interaction with PL/SQL, User-defined Packages

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



Please note that all the examples in this series have been tested only with Oracle 10g.  I didn’t really test them with all the previous versions of Oracle.  I suggest you refer to the documentation for the respective version you are using, if any of the programs failed to execute.

Implementing a PACKAGE

In this section, we will look into the implementation of package in Oracle database. First of all, let us have a very simple example as follows:

create or replace package SamplePkg as
    procedure dispSquare (a IN number);
    procedure dispCube (a IN number);
end SamplePkg;

The above code actually creates only a package specification (not the body yet).  Consider the following code:

create or replace package body SamplePkg as

    procedure dispSquare(a IN number) is
        dbms_output.put_line ('Square = ' || (a*a) );

    procedure dispCube(a IN number) is
        dbms_output.put_line ('Cube = ' || (a*a*a));
end SamplePkg;

The above code creates the package body for the previous package specification.  All the members in the package body should match with all the declarations within the package specification.  Make sure that both package specification and package body gets stored in the database.

Now the question is, how do we execute a package?  If it is a stored procedure, we just execute by using an ‘execute’ command (part 16) followed by the procedure name.  Even here, the concept is almost similar. The only issue is that we need to use the command ‘execute’ followed by the “packagename.sub-programname” convention.  The following is a demonstration for the above package to execute from SQL prompt:

Execute SamplePkg.dispSquare(10);
Execute SamplePkg.dispCube(10);

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