Previous | Table of Contents | Next

Page 264

Next you see a couple of constants for the program name and version date/author.

-- version name and string constants
  pkg_name CONSTANT VARCHAR2(30) := `REV_ENG';
  version  CONSTANT VARCHAR2(21) := `V.001 11-AUG-1997 djc';

These are in here because it is very difficult to track compiled versions. In some languages, version strings can be pulled right out of the object files so you can ascertain when they were built. As archaic a practice as this may seem, when you're dealing with fairly old object modules this may be all you have. Because only one executable image of a stored program object may exist in the data dictionary, you want to know that the compiled version really does match the latest and greatest in your code management system. Perhaps one objection to using a version constant is that every time you modify the package, you are expected to update the constant string as well. I have found the extra effort worth the trouble, in exchange for being able to identify easily the compiled program version.

Moving right along, the next thing that boggles the eyes are a number of procedures that each perform one facet of the schema reverse-engineering, as shown in Table 11.2.

Table 11.2 List of Significant Procedures in rev_eng.sql


Procedure Name Schema Objects It Processes
rev_eng all
rev_tables tables
rev_pkeys primary key constraints
rev_ukeys secondary indexes defined as unique
rev_fkeys foreign key relationships
rev_checks tables' check constraints
rev_indexes tables' secondary indexes
rev_views views
rev_syns synonyms made on the objects belonging to the given owner
rev_syns synonyms defined for a given owner
rev_grants grants made against the given owner's schema objects to any user
rev_grants grants made to the specified grantee to any objects to any schema
rev_col_grants grants made against columns of the given owner's schema objects to any user
rev_seqs sequence objects by owner

Page 265

It was pretty straightforward to declare all these routines right up front, before I had even considered their implementation. I knew what schema objects I wanted to handle. I chose a standard style for their declarations so that they would be easier to remember. Because the package body must also contain all these subprograms, initially I created stub code which consisted only of the subprogram declaration and a NULL statement.

The procedure rev_eng is sort of the main program entry point, although each procedure is capable of being run as standalone. This procedure calls all the other ones so that the DDL statements come out in a particular order. You would call rev_eng when you wanted to reverse- engineer an entire schema, or all related objects of one particular table in the schema. For example, by specifying a table, the program will generate DDL statements for all indexes, constraints, and grants against that table. You could do this with the following invocation.

EXECUTE rev_eng.rev_eng(`sales', 'salestsp');

where the schema owner is SALES and the tablespace name is SALESTSP. Notice the heavy use of default parameters. In general, this makes the routines more flexible and easier to use; if you really want all tables, you can safely leave off this parameter.

-- this procedure reverse engineers only the tables' secondary indexes
  -- user can specify one table or omit for all
  -- when run following pkeys it ignores indexes created for primary keys
  PROCEDURE rev_indexes(
    Piowner IN dba_indexes.owner%TYPE,
    Ptowner IN dba_tables.owner%TYPE,
    Ptspace IN dba_tables.tablespace_name%TYPE,
    Ptable  IN dba_tables.table_name%TYPE DEFAULT NULL,
    Pclear  IN BOOLEAN DEFAULT FALSE);

Every procedure gets a comment with any special instructions on its use. Even the procedure names follow a common pattern. I also use the same or similar parameter names throughout, for consistency. Every parameter is based on the column on which it will be matched. So from looking at the declarations you can tell what table it's going against.

CAUTION
Be careful when overloading subprograms that have default parameters. A runtime problem can arise if the only difference between them is the number of parameters, and one of the extra parameters has a default value. Even though the package will compile successfully, you may get the error "PLS-00307: too many declarations of `<overloaded subprogram>' match this call" if the default parameter is left off.
For example, these two procedure declarations are perfectly valid:
PROCEDURE a(x NUMBER);
PROCEDURE a(x NUMBER, y NUMBER DEFAULT 0);
But would generate the PLS-00307 error with this invocation:
a(10);

Page 266

I didn't try to bite off too much for each procedure. Each does one task, and that's all. In a very few cases, there are interdependencies, such as rev_indexes following rev_pkeys. This happened because I needed to know to skip the primary key indexes when looking at the secondary indexes. But if rev_indexes is being run standalone, especially repeatedly, you want to be able to specify whether to clear the list of indexes already examined. This list of index names is maintained by a private, global PL/SQL table.

The two procedures rev_syns are a prime example of overloading. The first focuses on the schema object owner. The second focuses on the synonym owner, which may be defined against multiple schemas.

-- this procedure reverse engineers only synonyms made on
  -- the objects belonging to the given owner
  -- the user can specify a particular synonym owner or NULL for all
  -- NOTE: user must have CREATE ANY SYNONYM privilege to create a
  -- private synonym in another user's schema. User must have
  -- CREATE PUBLIC SYNONYM privilege to create a PUBLIC synonym.
  PROCEDURE rev_syns(
    Poowner IN dba_synonyms.table_owner%TYPE,
    Psowner IN dba_synonyms.owner%TYPE);
-- this procedure reverse engineers only synonyms defined for a given owner
  -- the objects' owners are unspecified and may be on various owners' objects

  PROCEDURE rev_syns(Psowner IN dba_synonyms.owner%TYPE);

I found this to be useful, so I put it in. Fortunately, the code between the two wound up being fairly similar, and it became a cut-and-paste matter. The overloaded procedure rev_grants turned out the same way. Technically, rev_col_grants should also have been done this way for completeness. However, the package was getting too long, and after examining several schemas I noticed that the use of column level grants was seldom used, so why go through all that trouble?

Designing the Package Body

In the package body, notice that nearly all cursors are declared outside of all procedures. Normally, I restrict cursors to just the procedures that use them, but in this case I wasn't sure whether a cursor would be used in more than one procedure. In fact, a few cursors are used by more than one procedure. Also, they will persist for the duration this way. If I'd pushed them inside procedures, they'd have to be re-created every time the procedure was invoked, because then they would be strictly local in scope to the procedure. While this makes efficient use of memory, I was more interested in allocating all of them up front.

In order to handle the possible NULL parameters, the cursor's WHERE clause logic includes the form (parameter is NULL or column = parameter).

WHERE
      owner = Powner AND
      (Ptable IS NULL OR table_name = Ptable)

Because Boolean logic short-circuits, this avoids the problem of comparing a column incorrectly to a NULL, which would always fail. This is a common idiom I use regularly, especially

Previous | Table of Contents | Next