The Oracle (tm) Users' Co-Operative FAQ

How do I use UTL_FILE as a debugging aid


Author's name: Norman Dunbar

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 24/09/2002

Oracle version(s): 8.1.7.4 & 9.2

How do I use UTL_FILE as a debugging aid and, if so, can I incorporate it into a package of my own to make my debugging easier?

Back to index of questions


Many people use DBMS_OUTPUT as a debugging aid and for small amounts of output, this is fine. However, when the output gets larger than 1 million bytes, DBMS_OUTPUT cannot be used. If you have a need to output more that the above limit, then DBMS_OUTPUT cannot be made to work. You will get your 1 million bytes, then nothing - apart from a buffer overflow error.

To get around this seemily huge, but actually quite small, limit, you need to use some other form of utility, and UTL_FILE comes in handy for this very purpose. Basically, the upper limit on how much output you can generate is limited only by your Operating System's file system. While DBMS_OUTPUT is simple to use and can be used by anyone, UTL_FILE is not quite so simple.

Depending on your Oracle version, you need to set up UTL_FILE with init.ora parameters (up to and including 9.0.1) or have the DBA create a directory for you (Oracle 9.2 onwards). For full details on setting up UTL_FILE, please see the FAQ question Why am I getting errors when I try to use the utl_file package to write a flat file ? The rest of this FAQ assumes that you have set up your system and that UTL_FILE is working correctly.

Rather than you having to type the same code into every procedure or function that you wish to debug, the following very simple package will attempt to reduce the amount of typing you have to do.

Connected to:
Personal Oracle9i Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> CREATE OR REPLACE PACKAGE Mydebug AS
  2  
  3      -- Define a variable to hold the file id for all debugging actions.
  4      FileId utl_file.file_type;
  5   
  6   
  7   -- This procedure will initialise the debugging system by opening the debugging file. You
  8   -- could add more stuff here if you wish. 
  9   PROCEDURE Initialise(iPath IN VARCHAR2, iFile IN VARCHAR2, iBuffer IN NUMBER := 256);
 10   
 11   -- This procedure terminates the debugging system - simply by closing whichever file is open.
 12   -- Although UTL_FILE allows a number of files to be open at any time, this is a simple
 13   -- system and only has a single debugging file.
 14   PROCEDURE TERMINATE;
 15   
 16   -- And the guts of the package is this simple procedure to send whatever is passed to it,
 17   -- straight out to the logging file. The option to flush the buffer contents is provided.
 18   PROCEDURE debug_out(iText IN VARCHAR2, iFlush IN BOOLEAN := FALSE);
 19   
 20  END;
 21  /
Package created.

SQL> CREATE OR REPLACE PACKAGE BODY Mydebug AS
  2  
  3      -- Define a variable to hold the file id for all debugging actions.
  4      -- FileId utl_file.file_type;
  5   
  6   
  7   -- This procedure will initialise the debugging system by opening the debugging file. You
  8   -- could add more stuff here if you wish. 
  9   PROCEDURE Initialise(iPath IN VARCHAR2, iFile IN VARCHAR2, iBuffer IN NUMBER := 256)
 10   AS
 11   BEGIN
 12      Mydebug.FileId := utl_file.fopen(iPath, iFile, 'W', iBuffer);
 13      Mydebug.debug_out('Debugging session begins at ' || TO_CHAR(SYSDATE));    
 14   EXCEPTION
 15        WHEN utl_file.invalid_path THEN
 16          RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
 17        WHEN utl_file.invalid_mode THEN
 18          RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
 19        WHEN utl_file.invalid_filehandle THEN
 20          RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
 21        WHEN utl_file.invalid_operation THEN
 22          RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
 23        WHEN utl_file.read_error THEN
 24          RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
 25        WHEN utl_file.write_error THEN
 26          RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
 27        WHEN utl_file.internal_error THEN
 28          RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
 29        WHEN OTHERS THEN
 30          RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
 31      END;
 32   
 33   -- This procedure terminates the debugging system - simply by closing whichever file is open.
 34   -- Although UTL_FILE allows a number of files to be open at any time, this is a simple
 35   -- system and only has a single debugging file.
 36   PROCEDURE TERMINATE
 37   AS
 38   BEGIN
 39      Mydebug.debug_out('Debugging session terminated at ' || TO_CHAR(SYSDATE)); 
 40      utl_file.fclose(Mydebug.FileId);
 41   END;
 42   
 43   -- And the guts of the package is this simple procedure to send whatever is passed to it,
 44   -- straight out to the logging file. The option to flush the buffer contents is provided.
 45   PROCEDURE debug_out(iText IN VARCHAR2, iFlush IN BOOLEAN := FALSE)
 46   AS
 47   BEGIN
 48      utl_file.put_line(Mydebug.FileId, iText);
 49      IF (iFlush) THEN
 50        utl_file.fflush(Mydebug.FileId);
 51      END IF; 
 52   END;
 53   
 54  END;
 55  /
 Package body created.

Testing the procedure is quite simple - but as this session is running on a 9.2 setup, I need to create a directory first. This is then passed in as the iPATH parameter when calling MyDebug.Initialise. Note that if you are using 9.2 the directory name must be passed in upper case. If this was a previous version of Oracle, the the iPATH parameter would need to be passed over exactly as it was defined in the init.ora file.

SQL> create directory mydebug as 'c:\temp';
Directory created.


SQL> BEGIN
  2     Mydebug.INITIALISE(IPATH=>'MYDEBUG', IFILE=>'test.log', IBUFFER=>1024 );
  3     FOR something IN 1..100 LOOP
  4        Mydebug.DEBUG_OUT('Currently processing record : ' || TO_CHAR(something));
  5     END LOOP;
  6  
  7     Mydebug.TERMINATE;
  8  END;
  9  /
PL/SQL procedure successfully completed.

And the proof of the pudding is in the generated output :

Debugging session begins at 24-SEP-02
Currently processing record : 1
Currently processing record : 2
....
Currently processing record : 99
Currently processing record : 100
Debugging session terminated at 24-SEP-02

By changing the 1..100 in the above test to 1.1000000 you will generate a file which is around 3,500,000 bytes in length which is larger than the 1,000,000 allowed by DBMS_OUTPUT.

The package can also be used with systems where there is a top level procedure calling other sub-procs, all you have to do is initialise and terminate in the top level procedure and debug_out() in the lower level ones. For example :

SQL> CREATE OR REPLACE PROCEDURE sub_proc(iLoop IN NUMBER)
  2  AS
  3  BEGIN
  4     FOR counter IN 1..iLoop LOOP
  5     Mydebug.DEBUG_OUT('Processing : '||TO_CHAR(counter));
  6     END LOOP;
  7  END;
  8  /
Procedure created.

SQL> CREATE OR REPLACE PROCEDURE top_level 
  2  AS
  3  BEGIN
  4     Mydebug.Initialise('MYDEBUG','test.log');
  5     sub_proc(5);
  6     Mydebug.TERMINATE;
  7  END;
  8  /
Procedure created.

SQL> execute top_level;
PL/SQL procedure successfully completed.

Which gives the following output in the log file :

Debugging session begins at 24-SEP-02
Processing : 1
Processing : 2
Processing : 3
Processing : 4
Processing : 5
Debugging session terminated at 24-SEP-02

You will see that the default date format is used in my Initialise and Terminate procedures - which in the examples shown do not give the actual time that the debugging session starts and finishes. You may find it better to set up a date format specifier in the call to to_char() so that the full date format is shown. Normally I have the time showing as well as the date - I just haven't set it up in this test database yet.

Note, the above package and directory examples were created in the SYSTEM user and tested within that user. This means that the package isn't yet available to other users. If the SYSTEM user grants execute to public then the procedures will be available to all users. If a public synonym is created then everyone can use the package without having to prefix it with 'SYSTEM.'.

SQL> create public synonym mydebug for mydebug;
Synonym created.

SQL> grant execute on mydebug to public;
Grant succeeded.

SQL> grant write on directory mydebug to public;
Grant succeeded.

SQL> connect scott/tiger
Connected.

SQL> alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
Session altered.

SQL> begin
  2  MyDebug.initialise('MYDEBUG','scott.log');
  3  for emprec in (select ename from emp) loop
  4    MyDebug.Debug_Out('Ename : '||emprec.ename);
  5  end loop;
  6  MyDebug.Terminate;
  7  end;
PL/SQL procedure successfully completed.

From which, the output is as follows :

Debugging session begins at 25-09-2002 12:22:47
Ename : SMITH
Ename : ALLEN
Ename : WARD
Ename : JONES
Ename : MARTIN
Ename : BLAKE
Ename : CLARK
Ename : SCOTT
Ename : KING
Ename : TURNER
Ename : ADAMS
Ename : JAMES
Ename : FORD
Ename : MILLER
Debugging session terminated at 25-09-2002 12:22:47

Hopefully, this will give you the incentive to go away, read up on UTL_FILE and perhaps build a better version of the debugging package for general use in your own databases. Oracle 9i has introduced a number of new functions withing the UTL_FILE package so there is plenty room for improving the above example.


Further reading:

Oracle PL/SQL built in packages manual.

Why am I getting errors when I try to use the utl_file package to write a flat file ?


Back to top

Back to index of questions