FB

How To Turn On and Use SQL Developer DBMS_OUTPUT

SQL Developer DBMS_OUTPUTIn 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:

dbms_output.put_line

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.

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.

3 thoughts on “How To Turn On and Use SQL Developer DBMS_OUTPUT”

  1. Hey Ben,
    this is working for me in SQL Developer as you have demonstrated here, but not working from inside my function.

    I have a password verify function that calls a second function that reads a dictionary file. Inside that function I have:
    BEGIN


    DBMS_OUTPUT.PUT_LINE(pw_upper0);


    END

    I am not getting any output in my Dbms Output window.

    I call that from SQL Developer by typing ‘password’

    The password verify function calls the dictionary function which does it’s thing then returns after successfully changing the password. I get the message “Password changed” in the Script Output window, but nothing in the Dbms Output window.
    I can force it to return raise_application_error messages to the Script_Output_Window but I am missing something.
    Again, this works from the SQL Developer worksheet:

    BEGIN
    dbms_output.put_line(‘Hello world!’);
    END;

    Please advise

    1. Ben (Database Star)

      Hi Clodius,

      That’s an interesting problem. I don’t have access to my Oracle instance at the moment to test this, but I can offer a few suggestions.

      1 – Could you change the “pw_upper0” variable to a string such as “hello world” and see if it works by calling the password function? If it works, there is some issue with the pw_upper0 value.

      2 – Could you call the put_line function from outside this function (in the first function, perhaps)? If it works, then maybe there’s an issue with calling it in this function.

      As far as I know, calling put_line in a nested function like this should work, but I can’t test it at the moment.

      Let me know how it goes.

      Ben

  2. I’m not able to get the output after following the steps given by you and also tried with set serveroutput on, then also not getting any output printed in the screen.

    PL/SQL procedure successfully completed, then no output is showing on.

    Please help here.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.