Lesson 6	Executing Components of a Package
Objective	Create a PL/SQL block that calls different parts of a package.

Executing Components of a Package

Testing a package involves testing each of the public procedures and public functions defined in the package specifications by executing them individually. Private functions and private procedures are tested at the same time, because these are both used when you execute the public functions and public procedures. Executing a public procedure contained in a package is similar to testing a stand-alone procedure, except you must add the package name as a qualifier to the procedure name. For example, a package named GET_CALCULATION has a procedure named TOTAL_SALARY. To call the procedure, you must use the name GET_CALCULATION.TOTAL_SALARY. Follow along with this Simulation to see how to call a procedure and a function that are part of the same package.

Execute Package's Procedure

You are starting inside SQL*Plus. Your job is to create a command that executes the procedure contained inside a sample package. When you call the sub-programs within a package, you can get the information about the parameters by looking at the package specification. To view the specification, you must execute a query, but first, execute the SQL*Plus environment command shown here. This adjusts the column named TEXT (which will display the contents of the package specification) so that the column displays within the borders of our small simulation window. To execute this command, simply press Enter.
The command is now in effect for your SQL*Plus environment. Now, type the query that displays the package specifications. You are querying the USER_SOURCE view, which is one of the dozens of data dictionary views that stores information about your schema. This view stores the source code for package specifications and package bodies. Type this line and press Enter: SELECT TEXT FROM USER_SOURCE
This line narrows the query results to the package named GET_MONTHEND_DATA, which was created in a previous lesson. Type this next line of the query:
```
WHERE NAME = 'GET_MONTHEND_DATA'
```
Press Enter when you are done typing.
The third line of the query narrows the results to display only the package specifications. (If you left out this line, you would see both the package specification code and the package body code. Type this line:
```
AND TYPE = 'PACKAGE'
```
Press Enter when you are done typing.
The final line of the query causes the results to be displayed in the order of the text code lines by sorting on the LINE column. The LINE column contains the line number of each line of code. The semicolon at the end of the line executes the query. Type this line and press Enter: ORDER BY LINE;
Press Enter when you are done typing.
Now you see the results of the query: the source code of the GET_MONTHEND_DATE package specification. You need this data to find the names of public procedures and functions and the required parameters for each of them. There is a procedure named CALC_PROFIT with two incoming and one outgoing parameters. There is also a function named CALC_DIVIDENDS with one incoming parameter and a returning number. Your next step is to create a PL/SQL block that executes the procedure and the function and displays the results. In other words, test the package. First, one more adjustment must be made to the SQL*Plus environment. Set your environment to display output from the DBMS_OUTPUT package, which you will use to display the results of executing the procedure and function. Here is the command: SET SERVEROUTPUT ON
Press Enter when you are done typing.
I have filled in the beginning of the PL/SQL block to save some steps in the simulation. The lines I filled in declare two variables that accept output from the package. Now it is time to call the procedure. Remember, the procedure name is CALC_PROFIT. It requires three parameters: A year, a month, and a variable to receive the calculated profit for the given year and month. To simplify the example, you will type values in for the year and month rather than using variables. Here is the line you type, which calls the procedure. Press Enter at the end:
```
GET_MONTHEND_DATA.CALC_PROFIT(2000,2,V_PR);
```
You now have a line that calls the procedure and supplies the three required parameters. I have filled in two lines after the one you typed. These two lines call the DBMS_OUTPUT package and display a line of feedback, showing the profit that was calculated by the package you called. The next step is to call the function. You can use an assignment statement to do this, since the function returns a value, rather than sending results in a parameter. Begin by typing the first part of the assignment and pressing Enter. This will place the value returned by the function into the V_DIV variable. V_DIV :=
Now you can call the function, passing a date to it. Type this line and press Enter:
```
GET_MONTHEND_DATA.CALC_DIVIDENDS('31-DEC-2019');
```
That completes the call to the function. I have added the last few lines of the PL/SQL block to speed up the simulation. These simply display results of the function and fill in the obligatory END commands. Execute the entire block by typing a forward slash and pressing Enter now.
The PL/SQL block has been executed. As you can see, the profit for February of 2000 is 20.47 and the dividends for the period ending December 31, 2019 is 2.6304. SQL*Plus also tells you that it successfully completed the procedure.

The mechanics of calling a procedure and function that are stored within a package are similar to using stand-alone procedures and functions. Now you can try out the package you created in the previous lesson. In this exercise, use the sample results shown at the end of the exercise.

Steps To Execute Procedure Package

Click the link below to read about the steps to execute Procedure Package.

Steps to Execute a package’s procedure

You must query the USER_SOURCE view to observe the package specifications. I have filled in the SELECT clause. You must now fill in the WHERE clause. Type in the appropriate text and press Enter.
The lesson simulation shows you how to set your environment so that output displays on the screen. Type in the appropriate SQL*Plus SET command now and press Enter.
I have defined appropriate variables to test the procedure. Your job is to type in the correct call command for the procedure and press Enter. Note: Specify sales id 2 as the incoming parameter.
I have completed the PL/SQL block. This one is somewhat different than the lesson example because the data is modified in the database rather than returned as values or parameters from the procedure. So, I have queried the modified table and then displayed the values that were changed by the procedure. Now, execute the PL/SQL block and view the results.
The results are now displayed on the screen. The four values that were updated by the procedure are displayed for you to view.

Rules for Calling Packaged Elements

It does not really make any sense to talk about running or executing a package, since a package is just a container for code elements. However, you will certainly want to run or reference those elements defined in a package. A package owns its objects, just as a table owns its columns. To reference an element defined in the package specification outside of the package itself, you must use the same dot notation to fully specify the name of that element. Below are some examples. The following package specification declares

a constant,
an exception,
a cursor, and
several modules:

PACKAGE pets_inc
IS
  max_pets_in_facility CONSTANT INTEGER := 120;
  pet_is_sick EXCEPTION;

  CURSOR pet_cur (pet_id_in IN pet.id%TYPE) RETURN pet%ROWTYPE;

  FUNCTION next_pet_shots (pet_id_in IN pet.id%TYPE) RETURN DATE;
  PROCEDURE set_schedule (pet_id_in IN pet.id%TYPE);
END pets_inc;

To reference any of these objects, I preface the object name with the package name, as follows:

DECLARE
  -- Base this constant on the id column of the pet table.
  c_pet CONSTANT pet.id%TYPE:= 1099;
  v_next_appointment DATE;
BEGIN
  IF pets_inc.max_pets_in_facility > 100
   THEN
     OPEN pets_inc.pet_cur (c_pet);

  ELSE
    v_next_appointment:= pets_inc.next_pet_shots (c_pet);
  END IF;
EXCEPTION
  WHEN pets_inc.pet_is_sick
  THEN
    pets_inc.set_schedule (c_pet);
END;

To summarize, there are two rules to follow in order to reference and use elements in a package:

When you reference elements defined in a package specification from outside of that package (an external program), you must use dot notation in the form package_name.element_name.
When you reference package elements from within the package (specification or body), you do not need to include the name of the package. PL/SQL will automatically resolve your reference within the scope of the package.

Package Body

The package body contains all the code required to implement the package specification. A package body is required when any of the following conditions are true: The package specification contains a cursor declaration with a RETURN clause You will then need to specify the SELECT statement in the package body. The package specification contains a procedure or function declaration You will then need to complete the implementation of that module in the package body. You want to execute code in the initialization section of the package. The package specification does not support an execution section (executable statements within a BEGIN...END); you can do this only in the body. Structurally, a package body is very similar to a procedure definition. Here are some rules particular to package bodies:

A package body can have declaration, execution, and exception sections. The declaration section contains the complete implementation of any cursors and programs defined in the specification, and also the definition of any private elements (not listed in the specification). The declaration section can be empty as long as there is an initialization section.
The execution section of a package is known as the initialization section; this optional code is executed when the package is instantiated for a session. I discuss this topic in the following section.
The exception section handles any exceptions raised in the initialization section. You can have an exception section at the bottom of a package body only if you have defined an initialization section.
A package body may consist of the following combinations: only a declaration section; only an execution section; execution and exception sections; or declaration, execution, and exception sections.
You may not include an AUTHID clause in the package body; it must go in the package specification. Anything declared in the specification may be referenced (used) within the package body.
The same rules and restrictions for declaring package-level data structures apply to the body as well as to the specification, for example, you cannot declare a cursor variable.
You can include an optional package name label after the END statement of the package body, as in:

Executing Package Components - Exercise

On Your Own Exercise: Build the PL/SQL block at home on your own database.
Executing Package Components - Exercise
The next lesson contains the module conclusion