Database Interaction with PL/SQL, User-defined Packages

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