This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Oct 17, 2012 11:09 PM by user346369 RSS

Zdebug -- A Forms debugging message tool

user346369 Expert
Currently Being Moderated
I have used the Message() command for years to debug everything I have ever written in Forms, so I have created a form that displays an entire test session's messages in one place.  The form is called zdebug (the z so it appears last in any list of forms), and I have made it available to anyone who might like to use it.

It handles both Message() messages written from within your form, as well as DBMS_Output messages created in server-side packages and triggers.

Whenever I need to debug a complex situation in a form, I just copy two objects (a procedure and a button) from Zdebug.fmb into my form, and then create messages using my own procedure, AA(), instead of the longer Message() command.

I can compile and run my form, then turn on or off messaging with a mouse-click.  Messages can be displayed immediately to the screen, or stored and then displayed all together.

You can download zdebug.fmb, and read all the documentation here:

ZDebug - Forms Debugging Message Tool

If you have any questions or comments, please post them here.

Thanks.

Edited by: Steve Cosner on Jul 31, 2009 4:05 PM
To clean out html tags from the old forum software

Edited by: Steve Cosner on Oct 17, 2012 11:01 PM
New URL -- the web page has been moved.
  • 1. Re: Zdebug -- Download a Forms debugging message tool
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Hello,

    Have just tested it. Works like a charm ;o)

    Francois
  • 2. Re: Zdebug -- Download a Forms debugging message tool
    474368 Newbie
    Currently Being Moderated
    Cool. Thanks Steve.

    --pat                                                                                                                                                                                                                                   
  • 3. Re: Zdebug -- Download a Forms debugging message tool
    user346369 Expert
    Currently Being Moderated
    Just bumping this thread up...
  • 4. Re: Zdebug -- Download a Forms debugging message tool
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    re-post again.
  • 5. Re: Zdebug -- Download a Forms debugging message tool
    119596 Newbie
    Currently Being Moderated
    nice tool Steve !
  • 6. Re: Zdebug -- Download a Forms debugging message tool
    sgalaxy Journeyer
    Currently Being Moderated
    Hi ,
    Is it better or simpler or..... than the Forms Builder debugging tool.....???

    Thanks ,
    Simon
  • 7. Re: Zdebug -- Download a Forms debugging message tool
    119596 Newbie
    Currently Being Moderated
    you can't compare them.

    The Forms Debugger is a really big tool - and very powerful in 10g - the zdebug-tool is a nice add-on for your forms-development.

    Use both for the debugging-work you have...
  • 8. Re: Zdebug -- Download a Forms debugging message tool
    446135 Newbie
    Currently Being Moderated
    This has been useful in another way - at last I worked out how to get DBMS_OUTPUT messages into Forms.
    PROCEDURE P_CHECK_DBMS_OUTPUT IS
    i         BINARY_INTEGER :=0;
    L_status  BINARY_INTEGER;
    L_txt     VARCHAR2(2000);
    L_outmsg  VARCHAR2(2000) := '--Start dbms_output--';
    -- Get the current contents of the buffer.
    BEGIN
    LOOP
       DBMS_OUTPUT.GET_LINE(L_txt,L_status);
       ---
       if L_status = 0 then -- ok       
          L_outmsg := L_outmsg || chr(10)||L_txt;
          i := i+1;
       else
          MSGBOX(L_outmsg ||chr(10)|| '--End dbms_output--');
       exit;
       end if;
       ---
    end LOOP;          
    END;
  • 9. Re: Zdebug -- Download a Forms debugging message tool
    EddieR Newbie
    Currently Being Moderated
    I use a database package for this. It has the advantage of letting you see messages for long processes, infinite loops and jobs. It also shows the time taken between messages and includes client and server side messages in the order they were written.

    It needs a table, view and package, and a synonym if you want to pick up dbms_output messages. I never use dbms_output, so I did a quick and dirty fix for this and the synonym effects tools which make automatic calls to the dbms package.

    The init() procedure starts a new run of messages, the other procedures should be self-explanatory.


    CREATE TABLE log_table(
      oracle_id        VARCHAR2(30) DEFAULT USER,
      datestamp        DATE DEFAULT SYSDATE,
      run_num          INTEGER,
      seq_num          INTEGER,
      text             VARCHAR2(4000 CHAR),
      exception_log    VARCHAR2(1)
    );
     
     
    CREATE OR REPLACE VIEW v_log_table (
      oracle_id,
      datestamp,
      t_diff,
      run_num,
      seq_num,
      text,
      exception_log) AS
    SELECT
      oracle_id,
      datestamp,
      Round(24*60*60*(Lead(datestamp)
        over(PARTITION BY run_num ORDER BY seq_num) - datestamp))
        AS t_diff,
      run_num,
      seq_num,
      text,
      exception_log
    FROM log_table
    ORDER BY run_num DESC, seq_num ASC
    /
    
    
    
    CREATE OR REPLACE PACKAGE log_pkg AS
    
    -- initialise the run number and sequence
    PROCEDURE init;
    
    -- return the current run number for the set of logs.  will be null if a log
    -- has not yet been written or after a call to init().
    FUNCTION get_run_num RETURN PLS_INTEGER;
    
    -- write a log
    PROCEDURE write_log(
      p_text IN VARCHAR2,
      p_indent PLS_INTEGER DEFAULT 0);
    
    -- write an exception log
    PROCEDURE write_exception(
      p_text IN VARCHAR2,
      p_indent PLS_INTEGER DEFAULT 0);
    
    
    -- for dbms_output compatibility via synonym
    PROCEDURE put_line(
      p_text IN VARCHAR2);
    
    END log_pkg;
    /
    
    CREATE OR REPLACE PACKAGE BODY log_pkg AS
    
    --------------------------------------------------------------------------------
    
    gn_run_num PLS_INTEGER;
    gn_seq_num PLS_INTEGER := 1;
    
    --------------------------------------------------------------------------------
    
    PROCEDURE init IS
    BEGIN
      gn_run_num := Null;
      gn_seq_num := 1;
    END init;
    
    --------------------------------------------------------------------------------
    
    -- return the current run number for the set of logs.  will be null if a log
    -- has not yet been written or after a call to init().
    FUNCTION get_run_num RETURN PLS_INTEGER IS
    BEGIN
      RETURN gn_run_num;
    END get_run_num;
    
    --------------------------------------------------------------------------------
    
    -- internal procedure which does the real work
    PROCEDURE write_log_internal(
      p_text IN VARCHAR2,
      p_indent PLS_INTEGER,
      p_exception_log IN log_table.exception_log%TYPE) IS
    
      PRAGMA autonomous_transaction;
    BEGIN
      INSERT INTO log_table(run_num, seq_num, text, exception_log)
        VALUES (CASE WHEN gn_run_num IS NULL
                -- with NVL both arguments are always evaluated so CASE is quicker
                  THEN (SELECT Nvl(Max(run_num),0) + 1 FROM log_table)
                  ELSE gn_run_num
                  END,
                gn_seq_num,
                SubStr(RPad(' ',2 * p_indent,' ') || p_text,1,4000),
                p_exception_log)
        RETURNING run_num INTO gn_run_num;
    
      COMMIT;
    
      gn_seq_num := gn_seq_num + 1;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END write_log_internal;
    
    --------------------------------------------------------------------------------
    
    -- write a log
    PROCEDURE write_log(
      p_text IN VARCHAR2,
      p_indent PLS_INTEGER DEFAULT 0) IS
    BEGIN
      write_log_internal(p_text, p_indent, 'N');
    END write_log;
    
    --------------------------------------------------------------------------------
    
    -- write an exception log
    PROCEDURE write_exception(
      p_text IN VARCHAR2,
      p_indent PLS_INTEGER DEFAULT 0) IS
    BEGIN
      write_log_internal(p_text, p_indent, 'Y');
    END write_exception;
    
    --------------------------------------------------------------------------------
    
    -- for dbms_output compatibility via synonym
    PROCEDURE put_line(
      p_text IN VARCHAR2) IS
    BEGIN
      write_log(p_text);
    END put_line;
    
    END log_pkg;
    /
    
    
    
    
    
    
    -- testing:
    
    BEGIN
      log_pkg.write_log('aaa');
      log_pkg.write_log('bbb',1);
      Dbms_Output.put_line(log_pkg.get_run_num);
      log_pkg.write_exception('eeebbb',1);
      WHILE NOT(To_Char(SYSDATE,'SS') LIKE '%5') LOOP
        NULL;
      END LOOP;
      log_pkg.write_log('ccc');
      log_pkg.init;
      Dbms_Output.put_line(log_pkg.get_run_num);
      log_pkg.write_log('aaa222');
      Dbms_Output.put_line(log_pkg.get_run_num);
    END;
    /
    
    
    SELECT * FROM v_log_table;
  • 10. Re: Zdebug -- Download a Forms debugging message tool
    user346369 Expert
    Currently Being Moderated
    James, what advantage does your table/view/package have over using DBMS_Output?

    With an infinite loop, you would need a commit someplace in the loop in order to see any messages, wouldn't you? ...Oh, now I see the commit. And that would be dangerous in some situations, especially testing situations where I want to roll back the process because it was not running right.

    I once tried adding a timestamp to my ZDebug process, and found in Forms that the time required to pick up sysdate over the network took far more time than any of the internal Forms processing.
  • 11. Re: Zdebug -- Download a Forms debugging message tool
    EddieR Newbie
    Currently Being Moderated
    The advantages are that it restricts text to 4000 instead of 255 characters and does not restrict the number of rows, it records a timestamp, the output can be seen immediately and is not lost if the session crashes. Because the output can be queried you can easily see if a specific message was shown, find the time difference between any 2 messages, or query the exception_log column to see if an exception was hit (but the view is all I've ever needed). Because the data is persistent you can use this on a production system to keep track of what's happened before a crash. Obviously, you'll want to comment out the test messages and leave just the important ones before putting a program into production, and you might want to add something to delete old records.

    It uses "PRAGMA autonomous_transaction" for the commit.

    The timestamp is applied on the database server. On my pc I can write 100,000 messages in 20 seconds (0.0002 seconds per row).
  • 12. Re: Zdebug -- Download a Forms debugging message tool
    user346369 Expert
    Currently Being Moderated
    Time to bump this thread back up...
    I can write 100,000 messages in 20 seconds
    Wow! You have a different approach to debugging than I do. :-)

    I like to write out just the minimum, and ONLY the values and checkpoints I want to see.

    But I can see where your method could be useful in some situations.
  • 13. Re: Zdebug -- Download a Forms debugging message tool
    590356 Newbie
    Currently Being Moderated
    Hi Steve,

    That's a handy utility. Thanks for posting it!

    Dave
  • 14. Re: Zdebug -- Download a Forms debugging message tool
    user346369 Expert
    Currently Being Moderated
    Thanks for the complement. It DOES come in handy at times.

    Here's and example:

    Here, we have several complex (nearly legacy) systems to keep track of personnel and students in a large university. It is unfortunate, but sometimes individuals' data get entered into the systems twice, so there are actually two IDs and sets of data for a single person.

    I have written a form with a package in the background that facilitates displaying the data for two ID numbers, and allowing the user to merge the two into one. It uses lots of dynamic SQL and other tricks, but it has been working well over time. And then during the merge process, it calls a package at the client site to do the same ID merging across their entire database and array of systems. And here is where zdebug comes in handy:

    The author of the client-written package had the good sense to use dbms_output to display its progress in the merge utility. With the form doing its merging and writing zdebug messages, and then the package writing (hundreds of) messages, it is so much easier to track a problem.

    They are moving from an Oracle 8 db to Oracle 10, and the merge package failed. All we had to do was turn on debugging, and attempt a merge. After the error occurred, we just displayed all the debugging messages. They can actually be copied directly from the zdebug form's display, and emailed to the proper person. With the last progress message before the error, they were able to quickly identify a table whose grants had not been set correctly.
1 2 Previous Next