Chapter 10. Analyzing PL/SQL Code

Code writing and tuning is the first stage of application life cycle development. As this life cycle matures and grows, the maintenance of code base becomes mandatory for code analysis and forecasts. The code management strategy aims at code testing, tracing, profiling, and reporting the coding information. This chapter covers some recommended techniques to analyze PL/SQL code through Oracle-supplied resources such as data dictionary views, initialization parameters, and built-in packages. Within the scope of the chapter, we will cover the following topics:

• Tracing and generating reports on PL/SQL source code

• Reporting usage of identifiers in PL/SQL source code

• Extracting schema object definitions using DBMS_METADATA

Track coding information

Once the development stage of the code base is over, it might be required to track through the code for search operation or to extract some crucial information for analysis or maintenance purposes. Such scenarios do not require thorough line-by-line digging as might seem to be the case. The line-by-line or code-by-code approach not only eats up a lot of time and resource but also ends up in a huge effort with tiny result. For this reason, Oracle supplies a set of dictionary views which make the life of analysts easy. The Oracle-supplied dictionary views are proven metadata sources of Oracle to provide accurate and detailed end results. The dictionary views used for tracking PL/SQL code information are ALL_ARGUMENTS, ALL_OBJECTS, ALL_SOURCE, ALL_PROCEDURES, and ALL_DEPENDENCIES.

The following diagram lists the dictionary views along with a brief description. Note that only ALL_* views are listed in the chart but, nevertheless, the same purpose is achieved by the other...

Using SQL Developer to find coding information

The object metadata information retrieved from the dictionary views is a conventional way to track code information. But these days, the IDE have been made self-sufficient to generate some vital predefined reports. The metadata information demonstrated in the last section using dictionary views can also be generated from SQL Developer. SQL Developer is a free UI based interactive IDE tool which boards multiple database utilities.

Here, we will demonstrate the tracking of code through SQL Developer:

1. Go to View | Reports:

   

2. Go to All Reports | Data Dictionary Reports | PLSQL:

   • Under PLSQL, you find three options. These options are analogous to the dictionary views which we queried in the preceding section.

   • The Program Unit Arguments option queries the USER_ARGUMENTS dictionary view. The Search Source Code and Unit Line Counts options query the USER_SOURCE view.

   • When you click on any of the options for the first time, the following dialog box pops up...

Determining identifier types and usages

All the local declarations of a program unit are categorized as identifiers. An identifier's declaration locates a memory on the server and keeps it busy until the program unit is executed or terminated. Redundant identifiers must be recognized within a program so as to restrict them from holding a chunk of memory for no operation.

Oracle provides a tool known as PL/Scope to monitor the activities of identifiers in a program. It is one of the new features in Oracle 11g.

The PL/Scope tool

The PL/Scope tool compiles and captures the information of the identifiers declared and used in a program. Once the feature is enabled, the language compiler filters out the identifier's information and stores it in a dictionary view called USER_IDENTIFIERS. An identifier is recognized by its name, type, and usage.

Let us examine some of the key features of the PL/Scope tool:

• Only unwrapped program units can use the PL/Scope tool.

• The feature can be enabled by setting...

The DBMS_METADATA package

The DBMS_METADATA package was introduced in Oracle9i. It is a metadata API which is used to extract the definitions (DDL) of schema objects. The package was introduced to get rid of DDL exports, which used to produce poorly formatted DDL scripts. It is a powerful package which can generate DDL and retrieve relevant information associated with an object in XML (by default), or textual format. The package is owned by SYS while all other users work with its public synonym.

The package provides utilities to set the required formatting for the DDL, transforms, and parse items. Once the formatting settings start over, using transform handlers, the definition of an object can be retrieved as XML or text. It also provides the flexibility to execute DDL. Let us see some of the major features of DBMS_METADATA:

• Generate DDL through GET_DDL (GET_XML is its XML equivalent).

• Generate DDL for object dependencies through GET_DEPENDENT_DDL (GET_DEPENDENT_XML is its XML equivalent...

Summary

In this chapter, we understood the usage of Oracle supplied packages and dictionary views to find the coding information. We got introduced to a new feature in Oracle 11g, the PL/Scope tool, and learned how to determine the usage of an identifier in the PL/SQL program. At the end of the chapter, we covered the DBMS_METADATA package and demonstrated the extraction of a schema object definition as XML or DDL using the package.

In the next chapter, we will overview the strategies of tracing and profiling in PL/SQL.

Practice exercise

1. Which of the following dictionary views is used to get information about the subprogram arguments?

   1. ALL_OBJECTS

   2. ALL_ARGUMENTS

   3. ALL_DEPENDENCIES

   4. ALL_PROGRAMS

2. The tablespace information on a database server:

   SELECT tablespace_name 
   FROM DBA_TABLESPACES
   /
   
   TABLESPACE_NAME
   ----------------
   SYSTEM
   UNDOTBS1
   TEMP
   USERS
   EXAMPLE

   You execute the following command in the session:

   SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
   Session altered.

   Identify the correct statements:

   1. The identifier information would be captured by PL/Scope for the program created or compiled in the session.

   2. The identifier information would not be captured by PL/Scope as IDENTIFIERS:ALL can be enabled only at the SYSTEM level.

   3. The identifier information would be captured by PL/Scope only for the programs which are created in the session.

   4. The identifier information would not be captured by PL/Scope since the SYSAUX tablespace is not available.

3. The parameters specified in DBMS_METADATA are case sensitive...