What Is DBMS_OUTPUT?
DBMS_OUTPUT is a PL/SQL package that allows you to write data to a screen.
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:
It takes one parameter – a VARCHAR2 variable that is the expression to output.
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:
BEGIN dbms_output.put_line('Hello world!'); END;
You can run this by pressing F5, or clicking Run Script.
However, when you run it, you see a statement, but no output.
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).
This will display the DBMS Output panel.
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.
The same thing is shown. The code runs successfully, but there’s no output.
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.
You’ll then be asked for connection details. Select the same connection that you are using now, and click OK.
Now the DBMS Output panel looks different.
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.
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.