Display Oracle DBMS_OUTPUT

Within a Oracle PL/SQL, it is possible to write debugging and other information messages with a statement such as:

dbms_output.put('message')

AQT provides a tool to display these dbms_output messages. This tool can be invoked from either the Database Explorer window or the Run SQL window with Tools > Oracle DBMS Output.

AQT will open only one DBMS Output window for each Oracle database.

AQT Function

In order to use this feature, AQT needs to create a function on your Oracle database. This function enables the dbms_output messages to be returned as a result-set - this is needed so that AQT can read these messages through the ODBC interface used by AQT.

When you first use the DBMS Output tool, AQT will prompt you to:

• create the function on your Oracle database
• use an existing function (for instance, one created by another user)

This function needs to be created on every Oracle database you use this tool with. Once you have created / selected the function, AQT remembers this name so won't prompt you again for this.

AQT doesn't remember different names for different databases; it is recommended that you use the same name for all Oracle databases.

Other notes:

• the name of the function that AQT creates is AQT_READ_DBMS
• the SQL used for creating the function is given near the bottom of the oracle.cfg configuration file

Enabling DBMS_OUTPUT

Before you can use the dbms_output feature of Oracle, you need to enable this for your Oracle session. This is done with:

call dbms_output.enable()

and disabled with

call dbms_output.disable()

These statements will be done automatically by AQT when you start/stop the DBMS Output window. Eg.

• when the DBMS Output window is started, AQT will do a dbms_output.enable
• when the DBMS Output window is stopped, AQT will do a dbms_output.disable

So, before you run some PL/SQL (or Stored Procedure) which generates some dbms_output messages, you should either open the DBMS Output window, or manually run the dbms_output.enable command.

Using the DBMS Output window

This window show the dbms_output messages generated during your Oracle session.

• click on Refresh to check the database for new messages.
• messages are unformatted. Oracle concatenates the text; separate messages do not appear on separate lines.

  You can obtain a degree of formatting by placing a chr(10) character at the end of each message - eg. dbms_output.put('message' || chr(10)). When displaying the output, select the variable row height option to see the text in multiple lines.

• the column Approx Time gives the time when AQT retrieved the message from Oracle. This is not the time when the message was created. However, when you are retrieving messages over a long period of time, this can be useful.
• you can have AQT auto-refresh the display - check Auto Refresh and set a value for the Refresh period. The minimum refresh period you can specify is 5 seconds.

  Note that AQT can only run one query at once. When the Auto Refresh feature is enabled, and you are running queries and using other windows within AQT, you may periodically get the message Cannot process SQL as a query is already in process.

• the Clear button clears the contents of the grid.
• the Row Height button displays the grid with variable-row-heights. This is useful if you have large or multi-line messages.
• if you have a large value in a cell, you can display the full value by double-clicking the cell, or right-clicking and selecting Display Cell.
• you can copy cells by selecting them then hitting Ctrl+C.

Advanced Query Tool

https://www.querytool.com

© 2023 Cardett Associates Ltd. All rights reserved.