Oracle PL/SQL Best Practices

Name

DEV-04: Validate standards against source code in the database.

Synopsis

This book is chock-full of recommendations, standards, guidelines, and so on. The usual immediate, visceral response to all of these shoulds is: how can I possibly remember them? And how can I make sure that any of our developers actually follow through on their “shoulds?”

PL/SQL offers one big advantage in this area: all source code is stored in the database and is made available through data dictionary views (ALL_SOURCE, USER_SOURCE, DBA_SOURCE). Oracle also maintains additional information about code, such as dependencies, in other views. You can—and should—fairly easily validate at least some of the standards that you set by running queries against these views.

Here are some things you can do with this information:

• Set up a weekly job (via DBMS_ JOB) to identify any programs that have changed, have been created, or have been removed in the past week. Publish this information as HTML on an intranet so developers can, at any time, be aware of these changes. This approach can improve reuse within your organization, for example.

• Provide queries, preferably organized within programs in a package, that developers can run (or, again, can be run as scheduled, weekly jobs) to check to see how well their code complies with standards.

Tip

Executing, as well as writing, queries against data dictionary views (particularly the dependency-related views) can be time-consuming. Be patient!

Example

Suppose we have agreed ...