Previous | Table of Contents | Next |
Every use of PL/SQL in your script will be done through an anonymous PL/SQL block . Even a call to a stored procedure or function must be executed through an anonymous block. An anonymous PL/SQL block is nothing more than a PL/SQL block that isn t already compiled in the data dictionary. This is shown in the following example:
DECLARE nGPA number := 0; BEGIN SELECT overall_gpa INTO nGPA FROM STUDENTS WHERE ssn = 999999999; END;
One of the most common uses of anonymous PL/SQL blocks is a call to a stored PL/SQL object, typically for testing purposes.
The exec statement is a quick method to create an anonymous PL/SQL block, but it doesn t allow you to define any variables . The use of the exec statement is shown in this example, which calls the Annual_Review() procedure.
exec Annual_Review;
If the Annual_Review() procedure required parameters, these would have to be passed as literal values to the procedure. Using exec simply nests the call to the stored object between a BEGIN and an END .
The DBMS_Output package is often used within anonymous PL/SQL blocks to display the values returned from queries and calls to stored PL/SQL objects. The results of queries inside PL/SQL blocks contained in a script are not spooled to standard output (the terminal or monitor). This package provides a useful method of generating messages that are spooled to standard output. In order to use the DBMS_Output package, you must use the set serveroutput on command at the beginning of your script.
The UTL_File package (introduced with Oracle 7.3) can be used within scripts instead of spooling output to a file with the spool command. The package writes to and reads from files at the operating system level. This package is especially useful if the output of the script needs to be stored in a file.
More information about these packages is provided in Chapter 9.
On the CD-ROM you ll find the source code for the Build_SUID_Matrix package, which stores dependency information about stored PL/SQL objects based on the type of table references the object makes. This information is stored in the SUID_MATRIX table, which has this structure:
object_name NOT NULL varchar2(30) object_type NOT NULL varchar2(30) table_name NOT NULL varchar2(30) ins varchar2(1) upd varchar2(1) sel varchar2(1) del varchar2(1) typ varchar2(1)
Let s write a script that builds a simple report of the information contained in this table that shows the tables referenced by each object.
Since each object can reference numerous tables, we want to avoid listing the name of the object on every row of the report. We want to list the name on the first row and leave it blank on each subsequent row for the same object. This is very easy to do using the break command in SQL*Plus:
break on object_name skip 0 nodup
The report only has to perform these two tasks :
The pseudocode for accomplishing these two tasks is shown in this example:
set up the break command to skip duplicate object_name values query the SUID_MATRIX table to get the data
The code for the report looks like this:
break on object_name skip 0 nodup SELECT object_name, table_name, ins, upd, del, sel, typ FROM SUID_MATRIX ORDER BY object_name;
Code for a report to list the objects that reference each table is quite similar:
break on table_name skip 0 nodup SELECT table_name, object_name, ins, upd, del, sel, typ FROM SUID_MATRIX ORDER BY table_name;
The reports could easily be altered to run for a specific object_name or table_name as well. The object_name report generates output like this:
OBJECT_NAME TABLE_NAME I U D S T ----------------------------- -------------------------- - - - - - ANNUAL_REVIEW EMPLOYEES Y Y Y PERFORMANCE_RULES Y Y AWARD_BONUSES EMPLOYEES N Y Y EMPLOYEE_BONUSES Y Y Y PERFORMANCE_RULES Y Y GRANT_SICK_LEAVE EMPLOYEES Y Y Y GRANT_VACATION EMPLOYEES Y Y Y RAISE_SALARY EMPLOYEES Y
The table_name report generates output like this:
TABLE_NAME OBJECT_NAME I U D S T ----------------------------- --------------------------- - - - - - EMPLOYEES ANNUAL_REVIEW Y Y Y AWARD_BONUSES Y Y GRANT_SICK_LEAVE Y Y Y GRANT_VACATION Y Y Y RAISE_SALARY Y Y Y EMPLOYEE_BONUSES AWARD_BONUSES Y Y Y PERFORMANCE_RULES ANNUAL_REVIEW Y Y AWARD_BONUSES Y Y
Chapter 3 has discussed the basic tools you ll need to develop scripts using SQL*Plus and PL/SQL. An excellent place to go for more information is the Oracle Server SQL Language Reference and the PL/SQL User s Guide , both of which you should be able to borrow from your DBA. By now, you should have a basic understanding of the various roles that scripts play in a system and the considerations that go into script development, as well as a basic understanding of how you can write your own reports using SQL*Plus. Chapter 4 will discuss the PL/SQL commands needed to create a stored procedure and provide some insights on designing and testing stored procedures.
Previous | Table of Contents | Next |