How To Turn On and Use SQL Developer DBMS_OUTPUT

calendar Aug 20, 2015 · 4 min read · sql  ·
Share on: linkedin copy

In this article, I'll explain what the DBMS_OUTPUT function is and how it works in SQL Developer.

What Is DBMS_OUTPUT?

DBMS_OUTPUT is a PL/SQL package that allows you to write data to a screen.

If you've worked with any other programming language, you'll be taught early on that there is a way to output data. In JavaScript, this is document.write, and in Java, this is System.out.println.

In Oracle SQL, it's done using the dbms_output, which has a function called put_line.

So, to output data as part of your PL/SQL code, you call this function:

1dbms_output.put_line

It takes one parameter - a VARCHAR2 variable that is the expression to output.

While you're here, if you want a helpful list of ways to save time with Oracle SQL Developer, get my PDF guide here:

How Does It Work in Oracle SQL Developer?

So, you might be here because you've written a dbms_output.put_line function, but you aren't seeing the result.

Let's say you have a basic Hello World piece of code:

1BEGIN
2  dbms_output.put_line('Hello world!');
3END;

You can run this by pressing F5, or clicking Run Script.

However, when you run it, you see a statement, but no output.

output 01

How do you see the result of DBMS_OUTPUT in SQL Developer?

(If you don't have SQL Developer installed, see how you can install it here)

First, go to the View menu and select DBMS Output (shortcut is Alt+V, then D).

output 02a

This will display the DBMS Output panel.

output 03

Now, you can see the panel, but there's nothing in there. This is because the panel was not visible when you ran the code.

Let's run the code again.

output 04

The same thing is shown. The code runs successfully, but there's no output.

Now what?

Well, you'll need to enable DBMS_OUTPUT for this connection. You can do this by clicking on the green + symbol on the DBMS Output panel.

output 05a

You'll then be asked for connection details. Select the same connection that you are using now, and click OK.

output 06

Now the DBMS Output panel looks different.

output 07

There's a small tab with the Connection name there.

Let's run our code again. Click on the Code Editor section and press F5.

output 08

Success! The output is now showing at the bottom of the page.

So, in summary, to enable SQL Developer DBMS_OUTPUT:

1. Show the DBMS_OUTPUT panel by going to View > DBMS Output.

2. Click the green + symbol to enable it for this connection.

3. Ensure you have a DBMS_OUTPUT statement in your code somewhere, and execute that code.

What Other Functionality Does SQL Developer Have for DBMS_OUTPUT?

Well, there are a few other things that you can do in the DBMS_OUTPUT panel in SQL Developer:

  • Clear - you can clear the results from the output window by clicking this button. Helpful if you want to refresh the results or if you're doing debugging.
  • Save - you can save the results of this output to a file. It can also be helpful for debugging.
  • Print - you can print the results of this output to a printer. I've never actually used this as I prefer to save to a file.
  • Buffer Size - you can set the size of the buffer. This number is the number of characters that the DBMS Output window will hold in memory. A larger number means more characters but it can run slower.

How Do You Turn DBMS Output On By Default?

Instead of having to go into SQL Developer for each query and change these options, you can turn it on by default.

Jeff Smith, from Oracle, has a great article on how to do this by using a startup script, which you can read here.

So, that's explained how DBMS_OUTPUT can be turned on in Oracle SQL Developer.

While you're here, if you want a helpful list of ways to save time with Oracle SQL Developer, get my PDF guide here:

comments powered by Disqus