Database Interaction with PL/SQL, User-defined Packages
(Page 1 of 5 )

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.

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
    begin
        dbms_output.put_line ('Square = ' || (a*a) );
    end;

    procedure dispCube(a IN number) is
    begin
        dbms_output.put_line ('Cube = ' || (a*a*a));
    end;
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);

Next: Database interaction using a PACKAGE >>
 

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!