A Guide to DBMS_OUTPUT.PUT_LINE

In this guide, you’ll learn what the DBMS_OUTPUT.PUT_LINE function in Oracle does, see some examples, learn how to enable it in different editors, and learn why it should not be used in production code.

Let’s take a look.

Table of Contents

What is DBMS_OUTPUT?

DBMS_OUTPUT is a package in the Oracle database. A package is an object that contains code, such as functions and procedures. The DBMS_OUTPUT package contains code to let you send and display messages in your code.

What is DBMS_OUTPUT.PUT_LINE?

The PUT_LINE function in the DBMS_OUTPUT package is one of the most widely-used functions, in my experience.

This function allows you to write a message to the screen.

It works by placing the text you specify onto a buffer. That buffer is written to the screen in your IDE or program when the procedure completes.

It’s often used for debugging or displaying messages at certain points in PL/SQL programs.

The messages written by the PUT_LINE function are not sent until the calling PL/SQL program completes. There is no ability to write them earlier, or “flush the buffer”, as you are running your code.

What are the Limits?

The PUT_LINE function has a maximum line size of 32,767 bytes. If this is exceeded, you receive both the ORA-20000 and ORU-10028 errors.

The maximum size of the buffer is unlimited, but the default is 20,000 bytes and the minimum is 2,000 bytes. It can be changed in the database. If the text is too large for the buffer, you’ll get an ORA-20000 error and an ORU-10027 error.

Example – Anonymous Block

Let’s see an example of the DBMS_OUTPUT.PUT_LINE function.

Here’s how the PUT_LINE function can be used in a simple anonymous block of code.

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;

When this code is run, you’ll see the following output in your IDE:

Hello

It’s a simple statement but shows how messages can be written.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

IDE Not Showing PUT_LINE Output?

Are you running SQL*Plus, or SQL Developer, or another IDE, and don’t see any output?

By default, many IDEs don’t show output.

To see the output in SQL*Plus, you need to enable SERVEROUTPUT first. You do this by adding SET SERVEROUTPUT ON at the start of your code.

Here’s an example of enabling it with your code.

SET SERVEROUTPUT ON;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;

This would display the message in SQL*Plus.

To enable the output in SQL Developer, you’ll need to:

Display the DBMS_OUTPUT panel by going to View > DBMS Output
Add a tab for your current connection
Run your code

I’ve explained the steps along with screenshots in this guide.

Example – Stored Procedure

If you want to use PUT_LINE in a stored procedure, or a function, then your code may look like this.

This procedure calculates the sum of two numbers and displays the output to the screen.

CREATE OR REPLACE PROCEDURE add_numbers
(num1 NUMBER, num2 NUMBER)
AS
  sum_number NUMBER;
BEGIN
  sum_number := num1 + num2;
  DBMS_OUTPUT.PUT_LINE('The sum is ' || sum_number);
END;

Once the procedure is created, you can run it.

CALL add_numbers (4, 6);

The output of this call is:

The sum is 10

This demonstrates how you can use DBMS_OUTPUT.PUT_LINE in a PL/SQL procedure or function.

Why You Should Not Use It In Production

This PUT_LINE function can be helpful to display values and text at different points in your code.

When writing code, it can be helpful to put a bunch of PUT_LINE calls throughout it, so you can see what’s being done or see the value of variables.

However, there are reasons that it should not be used in code that may go into production.

Steven Feuerstein has written a great article about this. His reasons are:

You won’t see the output unless you have enabled it, by setting serveroutput on or the equivalent option in an IDE.
It only accepts strings or values that can be converted to strings.
If the program exits unexpectedly, the output is not displayed and is lost.
There is nowhere to see the output when the code is in production.

So what’s the solution?

Steven recommends using a different kind of tracing to monitor your code. You can use an open-source tool such as Logger, or build your own wrapper.

This will allow you to write any output to the screen in the development environment, or to a table in production. You could also enable and disable the output feature without adjusting the code.

Conclusion

The DBMS_OUTPUT.PUT_LINE function is simple to use and a good way to display messages to the screen when you’re writing code.

There are a couple of things to be aware of, such as buffer limits and how to enable it in some IDEs, but it’s quite a useful function.