1 2 Previous Next 22 Replies Latest reply: Oct 18, 2012 1:09 AM by user346369 RSS

    Zdebug -- A Forms debugging message tool

    user346369
      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çois Degrelle
          Hello,

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

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

            --pat                                                                                                                                                                                                                                   
            • 3. Re: Zdebug -- Download a Forms debugging message tool
              user346369
              Just bumping this thread up...
              • 5. Re: Zdebug -- Download a Forms debugging message tool
                119596
                nice tool Steve !
                • 6. Re: Zdebug -- Download a Forms debugging message tool
                  sgalaxy
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                Hi Steve,

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

                                Dave
                                • 14. Re: Zdebug -- Download a Forms debugging message tool
                                  user346369
                                  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