Use dbms_output.put_line in Datagrip for .sql files

Question

I started to use Datagrip for my PL/SQL (school) projects that need the use of DBMS_OUTPUT.PUT_LINE. Before this I was using Oracle SQL developer and I was able to use DBMS_OUTPUT by adding the following:

SET serveroutput ON;

There is a related question that shows how to enable or disable showing the contents of the DBMS_OUTPUT buffer but this only works for the Database Console tool window. How can I apply this to any .sql file? Currently, I am copying the content of my .sql files and run it in the Console tool window but there must be a better way.

Warble

I don't know if it's actually a solution or not; but I'm apparently able to use the DBMS_OUTPUT with my .sql files by opening a Database Console and using the 'Enable SYS.DBMS_OUTPUT' option, then using my file-affiliated console. Executing something in the DB console may or may not be necessary to achieve this. – Idel

Or... it's done automatically due to a more recent version /shrug – Idel

Answer 1

Turn on this setting in the Output pane:

Orelie

This will work only for the Database Console tool window, right? Is this possible for a file with the .sql extension? Please read the second part of the question again. – Warble

Answer 2

I second the comment from Prometheos II. Jakob also seems to say the same.

As you might know, the .sql scratch files HAVE to be associated with a console. You need to toggle the Enable SYS.DBMS_OUTPUT option icon in the associated console and you do see the effect when executing from the associated .sql file.

Steps:

1. Open your .sql file
2. Associate it with a console
3. Open the console and enable Enable SYS.DBMS_OUTPUT option
4. Go back to the .sql file and run your code. You'll be able to get the DBMS_OUTPUT in the console output.

Suboptimal design by JetBrains, but it works.

My IDE version: IntelliJ 2018.3 Ultimate (DataGrip uses the same code, I think)

Couldn't comment to existing sections due to low rep. Hence, added a new answer.

Charleen

I don't have this button in Database Console (v. 2018.3) – Hypogenous

How do I do this in a 2020.1.2 version? – Heron

Answer 3

Finaly found it! The previous answer didn't read the question and answer for intelij instead of datagrip which have completely different interface.

For 2021.1 right click on the console list in the service window and there should be a enable DBMS_OUTPUT when you right click.

Phaidra

Thanks for this! This is very well hidden. There is also a button on the vertical toolbar for Services that looks like a graph with three bars. – Baulk

Answer 4

Press shift+shift and then search for DBMS_OUTPUT and then you can enable/disable

I tried it with new UI

Comradery

all of the above did not work until I found this on the new interface. THANK YOU! – Derogatory

Answer 5

When everything else fails, read the documentation: Showing DBMS_OUTPUT for Oracle:

For Oracle, you can enable or disable showing the contents of the DBMS_OUTPUT buffer in the output pane. To do that, use the apropriate icon (note by LF; can't reference that image) on the toolbar of the Database Console tool window (Ctrl+F8).

Cross

Read the second part again. The issue is running .sql files. – Warble

When everything else fails, LF, read the question properly. Sorry, @Elliad, I was trying to look smarter than I really am, but turned out to be quite stupid. No, I don't know how to do that. I use TOAD or SQL Developer; now I installed a trial DataGrip version, just to see what it is and how it works. In such a short time, I didn't manage to find the answer to your real problem. I hope, though, that you will. Good luck! – Cross

Answer 6

For all that are reading this for version(s) > 2021.1

You can enable the output in the connection properties. Source

Tyrant

Finally found something that works in 2023.2 with the new UI. I also had to restart DataGrip after this for it to work. – Beetlebrowed

Answer 7

This also works for sql files in DataGrip. Like moscas writes you need to activate the output console toggle button 'Enable SYS.DBMS_OUTPUT'.

Also you need to wrap it with begin end:

begin
  dbms_output.put_line('test');
end;

Foreignism

Answer 8

Using Rider 2023 I found the option in the Data Sources panel:

Toothpick