Previous | Table of Contents | Next

Page 239

CHAPTER 11

Using Stored Subprograms
and Packages

In this chapter

Page 240

Defining Stored Subprograms and Packages

The real application development begins when you start building stored subprograms and packages, which are persistent code modules that are compiled and stored in the database. They are shareable, reentrant, and reusable software that you design. They are callable from other PL/SQL modules, SQL statements, and client-side applications in languages that support remote procedure calls.

Whenever you compile a stored subprogram or package, the source code, compiled code, compilation statistics, and any compilation errors are stored in the data dictionary. Various data dictionary views help you visualize these entities. You can get information about modules you compile with the USER_ views; you can get some restricted information about modules other folks compiled and granted you access to with the ALL_ views; and you can get all information anyone compiled with the DBA_ views, provided you have DBA rights. These views are listed in Table 11.1 (for simplicity, listed as DBA_ views).

Table 11.1 Data Dictionary Views for Stored Subprograms and Packages


View Description
DBA_SOURCE Textual source code for all compiled modules.
DBA_ERRORS Textual listing of any compilation errors for all modules.
DBA_OBJECT_SIZE Statistics for compiled modules, such as validity, source, and object sizes.
DBA_OBJECTS Catalog of compiled modules (stored procedures, functions, packages, package bodies).
DBA_DEPENDENCIES List of object dependencies, such as tables referenced in packages.

No views exist to expose the object code because you don't need to see it. All you need to know is that it's in there and whether it's valid. A compiled module becomes invalid when a dependent object is changed or removed, such as when a column is added or a table dropped. If a stored module becomes invalid, it first must be recompiled, either automatically by the server or manually by the owner.

Building and Using Stored Programs

The syntax for creating stored subprograms is very similar to that for defining subprograms in anonymous PL/SQL blocks. Stored subprograms have all the same features of the sub-programs you learned to write in the previous chapter, plus some additional ones. Let's make a stored function out of that bool_to_char function you saw earlier (see Listing 11.1).

Page 241

Listing 11.1 bool2chr.sql—Stored Subprograms Make for Reusable Code

CREATE OR REPLACE FUNCTION bool_to_char(Pbool IN BOOLEAN)
RETURN VARCHAR2 IS
  str VARCHAR2(5);  -- capture string to return
BEGIN
  IF (Pbool) THEN  -- test Boolean value for TRUE
    str := `TRUE';
  ELSIF (NOT Pbool) THEN  -- FALSE
    str := `FALSE';
  ELSE  -- must be NULL
    str := `NULL';
  END IF;  -- test Boolean value
  RETURN (str);
END bool_to_char;
/

The server replies:

Function created.

That's all you get. The server doesn't execute the program; it compiles the program so that you can execute it later when called from other PL/SQL blocks.

NOTE
The CREATE OR REPLACE syntax creates a new function or replaces an existing one. This means you don't make incremental recompilations or source changes to stored code; you totally replace them with new versions.

CAUTION
Good source code management is required when using CREATE OR REPLACE. Once you replace a subprogram, the old source is gone forever from the data dictionary. It also means you can only have one object of this name in your schema.

Now let's run this newly created stored function using an unnamed PL/SQL block, as shown in Listing 11.2.

Listing 11.2 testbool.sql—Executing a Stored Subprogram

SET SERVEROUTPUT ON
BEGIN
  DBMS_OUTPUT.enable;
  DBMS_OUTPUT.put_line(bool_to_char(TRUE));
  DBMS_OUTPUT.put_line(bool_to_char(FALSE));
  DBMS_OUTPUT.put_line(bool_to_char(NULL));
END;
/

Page 242

This example pretty much exhausts the possibilities for all possible values returned by the stored function. It is called a Unit Test. For each input, verify the output. The input values should test all boundary conditions (values at and near the limits defined for the inputs, as well as some random values in between). You should always have a Unit Test file for your stored subprograms like this one so you can verify and validate the correct functioning of your code. Keep the Unit Test program with the stored subprogram, so that when you modify the subprogram you can test it again.

After running Listing 11.2, the server sends back the following:

TRUE
FALSE
NULL
PL/SQL procedure successfully completed.

What happens if you try to pass in something other than a Boolean? Let's try it and see:

BEGIN
  DBMS_OUTPUT.put_line(bool_to_char(0));
END;
/

The server responds with:

ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00306: wrong number or types of arguments in call to `BOOL_TO_CHAR'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

This error message indicates a compilation error. The PL/SQL engine's strong type checking caught the problem and responded with a pretty decent message.

Let's look at another, slightly long-winded example of a stored procedure, shown in Listing 11.3.

Listing 11.3 showindx.sql—A Stored Procedure to Display Index Information for Tables

CREATE OR REPLACE PROCEDURE show_index(Ptable IN
all_indexes.table_name%TYPE ÂDEFAULT NULL) IS
  -- local cursors
  CURSOR show_index_cur(Ctable all_indexes.table_name%TYPE) IS
    SELECT
      table_owner, table_name, tablespace_name, index_name, uniqueness, status
    FROM all_indexes
    WHERE
      (Ctable IS NULL OR table_name = Ctable)  -- one table or all
    ORDER BY
      table_owner, table_name, index_name;
  -- local constants
  TB CONSTANT VARCHAR2(1) := CHR(9);      -- tab character

Previous | Table of Contents | Next