Previous | Table of Contents | Next

Page 267

when I wish to default the parameter to NULL in order to specify a "don't care" value for this column. If the parameter value is supplied, the WHERE clause will match on it; otherwise, the column is effectively ignored, for the purpose of matching.

The cursor get_constraints is actually used by all the constraint routines that need to check for the constraint type, so I gained some coding efficiency here. I generalized the cursor just enough to avoid having to declare various flavors of what's essentially the same cursor. Cursors are probably one of your bigger memory hogs. The cursor get_index_params also falls into the reuse bin. I broke out this information into a separate cursor, even though I could have shoved it messily into another, because it was cleaner this way. In general, I think I avoided joins because the underlying data dictionary tables are difficult to decipher. Try looking at the Explain Plans for some of the dba_* views. Besides, they're potentially more flexible as separate objects, which enables me to combine them as needed in nested loops.

Below the cursor declarations are the global, private variables. The known_indexes PL/SQL table stores the names of all indexes you have already processed. The known_indexes array can be cleared using the clear_indexes array by simply assigning clear_indexes to known_indexes.

known_indexes := clear_indexes;  -- clear table (7.2)
known_indexes.DELETE;            -- clear table (7.3)

You never assign values to the elements of clear_indexes. It is always left empty. This is the standard method of clearing a PL/SQL table in Oracle7.2. In Oracle7.3, you can use the PL/SQL table attribute DELETE to clear all elements.

In order to simplify existence checks for the index presently being examined, I wrote the function idx_already_rev. Since the only way to check the array for an index name is to loop through all its entries, this function hides this implementation detail in an easy-to-use manner, as with:

      IF (idx_already_rev(get_indexes_rec.index_name)) THEN
        GOTO SKIPIT;  -- index was already reverse engineered
      END IF;

which is pretty readable.

Due to a bug between SQL*Plus and DBMS_OUTPUT in Oracle7.2 that causes leading whitespace to be trimmed, I use tabs heavily for indentation. A variety of character constants help me in this endeavor.

NOTE
In Oracle7.3 and later, use set serveroutput on format wrapped to prevent leading whitespace from being trimmed.

I created a date-formatting string because I write out the date everywhere, and this was a decent code reducer. If you wanted to change the date format, you only need to do it in one place.

-- declare global constant data
  CR CONSTANT VARCHAR2(1) := CHR(13);  -- carriage return character
...
  DFORMAT CONSTANT VARCHAR2(20) := `DD-MON-YYYY HH:MI:SS';  -- date format

Page 268

  -- declare global data
  status NUMERIC;
...
-- record variables
  get_tables_rec            get_tables%ROWTYPE;
...

These global private variables were added here instead of within procedures because, again, I never knew where I'd actually use them, and some I use in more than one subprogram. Also, it was a helpful organizational aid; I could see how far along I'd gotten with the implementation by checking these, especially the cursor record variables. I only defined them as I went along, instead of trying to enumerate them all up front.

The private local routine get_parent_table is a "helping" routine used further down. It's up here and not inside the one procedure that calls it because, again, I didn't know beforehand where it might be used. The most annoying thing is to overly restrict yourself and then be forced to move chunks of code around the module. This routine uses an implicit cursor so I can detect the TOO_MANY_ROWS exception.

Note that all the exception handler sections have similar coding constructs. I always follow the same general method for writing exception handlers. I trap the error, display or store it, clean up potentially open cursors, and set any return values to default values.

EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      status := SQLCODE;
      DBMS_OUTPUT.put_line(`-- rev_indexes ERROR: ` || SQLERRM(status));
      IF (get_index_cols%ISOPEN) THEN
        CLOSE get_index_cols;
      END IF;
      IF (get_indexes%ISOPEN) THEN
        CLOSE get_indexes;
      END IF;
    EXCEPTION
    WHEN OTHERS THEN
      NULL;  -- don't care
    END;
  END rev_indexes;
  

The displayed error message is printed as a comment because I intended the programs' output to be spooled to a file and I didn't want to muck it up with stuff the user might have to cut out to get the resulting SQL file to run. Each exception handler tells you what its name is for easier debugging. Return values, if any, are set to NULL. Any cursors that might be open are closed. The entire exception handler is placed in its own block to avoid a potential infinite loop on any operation done inside the exception handler.

I wound up having to write put_long_line because of a line length limitation with DBMS_OUTPUT, particularly when printing the long text for views. It seemed like such a generally useful thing, so I made it public. This routine looks for suitable places to break long lines. I think I must have coded it five times over before I handled every situation correctly. And lest you think me

Page 269

less than analytical, I wrote out all the cases and logic on paper (also five times)! Sometimes, it's easy to miss things. Actually, the cases became more refined and the logic more concise with repetition.

TIP
For complex, difficult code pieces, it's a good idea to write out on paper beforehand all the cases and logic goals before attempting a coding solution. Then exercise the algorithm on paper to see if you missed anything.

Designing the Procedures

Turns out most of the main routines have similar elements suitable for a cut and paste operation, especially with things like converting strings to uppercase and storing in local variables.

    Lowner  dba_tables.owner%TYPE;
    Ltspace dba_tables.tablespace_name%TYPE;
    Ltable  dba_tables.table_name%TYPE;
  BEGIN
    status := 0;
    Lowner  := UPPER(Powner);
    Ltspace := UPPER(Ptspace);
    Ltable  := UPPER(Ptable);

Note that status is a global private variable and thus it must be initialized in each routine. I've found from other projects that I might want to test certain variables in places other than in the most obvious, inside the procedures using them, so historically I've always defined status this way. Old habits die hard. When I start fleshing out a new package, I add in early the common elements I know I'll need. That's why they wind up being global private items. This way, I can test the value of status outside of any of the procedures that modify it. It's guaranteed to be available to all routines without having to remember to declare it everywhere.

The comment headers for each subprogram, and therefore each DDL statement generated, are printed with the following code:

    IF (Ltable IS NULL) THEN  -- parameter comments
      DBMS_OUTPUT.put(`-- ALL TABLES');
    ELSE
      DBMS_OUTPUT.put(`-- TABLE ` || Ltable);
    END IF;
    DBMS_OUTPUT.put_line(` FOR OWNER ` || Lowner ||
                         `, TABLESPACE ` || Ltspace);
    DBMS_OUTPUT.put_line(`-- PERFORMED ` || TO_CHAR(SYSDATE, DFORMAT));

This stuff was added late in the game, and required modification of every procedure, but it was really a no-brainer after setting up the pattern.

Wrapping Up the Package

This package makes heavy use of the DBA data dictionary views for tables, indexes, constraints, views, etc. I chose the DBA views so that users could reverse-engineer any schema, not just their own or ones they have privileges on. This was especially important because an index, foreign key reference, or grant might be made by a user other than the schema owner.

Previous | Table of Contents | Next