102-106

Previous

Table of Contents

Next

Anonymous PL/SQL Blocks

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 .

Using DBMS_Output And UTL_File

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.

Step-By-Step: Building A Report In SQL*Plus

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.

Requirements

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 

Pseudocode

The report only has to perform these two tasks :

   Setting up the break command

   Retrieving the data

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 

Code

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 

Summary

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