RelationalDBDesign RelationalDBDesign

Creating Packages   «Prev 

Execute Package’s Procedure

  1. 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.
  2. 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
  3. 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:

    Press Enter when you are done typing.
  4. 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:

    Press Enter when you are done typing.
  5. 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.
  6. 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.
  7. 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);
  8. Great! 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 :=
  9. Now you can call the function, passing a date to it. Type this line and press Enter: GET_MONTHEND_DATA.CALC_DIVIDENDS('31-DEC-1999');
  10. 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.
  11. Excellent job. 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, 1999 is 2.6304. SQL*Plus also tells you that it successfully completed the procedure.