Re: Debugging pl/sql

From: Scott Martin <smartin_at_bluewhale.emergent.com>
Date: 1995/05/30
Message-ID: <3qfm6a$h0a_at_kadath.zeitgeist.net>#1/1


In article <3q57lu$6ab_at_news4.primenet.com>, Paul Salay <psalay_at_primenet.com> wrote:
>Are there any facilities to debug pl/sql other than the DBMS_OUTPUT
>facility? Are there any third party debuggers?
>

Although not a "true debugger", SQL*Trax, the log analyzer for Oracle7, can significantly aid in the debugging of PL/SQL, triggers, and "normal" SQL. Putting SQL*Trax in "tail mode" (similar to that of "tail -f" in UNIX), causes it to poll the end of the redo log stream waiting for changes to come down the pipe. With one window in SQL*Trax tail mode, and another running the PL/SQL you wish to debug, you can get immediate feedback on the sequence of changes generated by the procedure. For example, the SQL*Trax output from a trigger to delete dependants when an employee is deleted might look like the following...

05/07/95 13:00:56      BEGIN TXN
05/07/95 13:00:56 UNDO INSERT INTO SYS.EMP (ROWID='00000c95.0000.0001')
05/07/95 13:00:56 UNDO   EMPNO=1
05/07/95 13:00:56 UNDO   NAME='John Doe'
05/07/95 13:00:56 UNDO   SALARY=48000
05/07/95 13:00:56      DELETE FROM SYS.EMP WHERE ROWID = '00000c95.0000.0001';
05/07/95 13:00:56 UNDO INSERT INTO SYS.DEP (ROWID='00000004.0000.0001')
05/07/95 13:00:56 UNDO   EMPNO=1
05/07/95 13:00:56 UNDO   NAME='Mary Doe'
05/07/95 13:00:56      DELETE FROM SYS.DEP WHERE ROWID = '00000004.0000.0001';
05/07/95 13:00:56 UNDO INSERT INTO SYS.DEP (ROWID='00000004.0001.0001')
05/07/95 13:00:56 UNDO   EMPNO=1
05/07/95 13:00:56 UNDO   NAME='Henry Doe'
05/07/95 13:00:56      DELETE FROM SYS.DEP WHERE ROWID = '00000004.0001.0001';
05/07/95 13:00:56 UNDO INSERT INTO SYS.DEP (ROWID='00000004.0002.0001')
05/07/95 13:00:56 UNDO   EMPNO=1
05/07/95 13:00:56 UNDO   NAME='Steve Doe'
05/07/95 13:00:56      DELETE FROM SYS.DEP WHERE ROWID = '00000004.0002.0001';
05/07/95 13:01:00      COMMIT

There is some company out there marketing a PL/SQL debugger that I've heard good things about. You will probably still want to look into it, however, even with that tool, you will not be able to get this level of detail of the before and after image values for rows affected by the PL/SQL or trigger.

If you would like further details about this product, I can be reached at 415-851-9576, or send E-Mail to smartin_at_turnstone.com.

SCott. Received on Tue May 30 1995 - 00:00:00 CEST

Original text of this message