Page 239
Page 240
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.
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.sqlStored 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.sqlExecuting 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.sqlA 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