ThatJeffSmith

DBMS_OUTPUT in SQL Developer

DBMS_OUTPUT is a technology that many Oracle people will just assume you already grok. It is a very basic building block for Oracle PLSQL programs. If you do not understand it, then your first attempts at scripting will likely be very frustrating.

So in a nutshell from the Oracle Docs

The DBMS_OUTPUT package enables you to send messages from stored procedures, packages, and triggers. The package is especially useful for displaying PL/SQL debugging information.

DBMS_OUTPUT provides a method of communication for for your program.

Did you hear something?

So this post is going to be pretty boring if you’ve worked with Oracle for more than a few months. But, feel free to skip ahead to see how to activate the output support in SQL Developer.

What is DBMS_?

DBMS is short for Database Management System. You have have heard of a RDBMS? Oracle is an example of an RDBMS. Part of Oracle’s ‘management system’ is a collection of support packages. A majority of these packages are owned by the ‘SYS’ user, and are prefixed by ‘DBMS_’.

Don’t have access to the SYS schema? That’s OK, there is a PUBLIC SYNONYM for SYS.DBMS_OUTPUT called ‘DBMS_OUTPUT.’ That’s why you’ll see it called in your code as

 begin
  DBMS_OUTPUT.PUT_LINE(''); -- like so
  SYS.DBMS_OUTPUT.PUT_LINE('yo'); -- NO!
 end;
/

It’s also granted to PUBLIC. That means everyone can see it, AND everyone can use it.

So How does it work?

You want to write something. That would be the PUT_LINE() procedure. But where does it go? According to the package spec, the message is written ‘in the buffer.’ Think of this as a memory space. You write to it, and you can read from it. So you make a call to write (PUT_LINE), and then you make a call to read (GET_LINE.)

What most new folks don’t realize is that

Messages sent using DBMS_OUTPUT are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.

This means you cannot read the messages as the program executes. Everything gets written to the buffer at once, after the program has completed.

All of this is generally handled by your IDE. Most developers will use DBMS_OUTPUT for debugging purposes. No one is going to read these messages but the developer (most likely.) Need something more poweful and flexible? You can also write to a file or even to the web.

Using DBMS_OUTPUT in SQL Developer

In the worksheet or in the Procedure Editor, you will write your code. Here is a simple anonymous PLSQL block

BEGIN
 dbms_output.put_line('Are you there Codd, it''s me, Jeff.');
end;
/


Step 0: Enable DBMS_OUTPUT
This is step 0 because most folks will start with step 1, and forget this step and have to start all over.

View – Dbms Output

With the DBMS_OUTPUT panel open, click the ‘Enable’ button.

Turn it on!

Step One: Run the code.
Use the ‘Run Script’ toolbar, or it’s keyboard shortcut, F5.

Step Two: Check the output panel.
You will notice the output shows up almost immediately after the ‘completed’ message appears in the regular ‘Script Output’ panel.

One last thing, mind the buffer size.

Remember that buffer thing we’re writing to with the package? You only get so much space. The default is 20,000 which equates to about 20,000 characters in most systems. You can increase it to 1,000,000, or my favorite – UNLIMITED.

See Tom’s take on “ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes.”

SQL Developer’s DBMS_OUTPUT panel allows you to set the buffer size directly. We also handle the GET and ENABLE calls – stuff that you should expect from your PLSQL IDE. Once you understand how it works, it’s very easy. If you’re a n00b though, I can see why you might have been confused and frustrated (until now of course!)

Unlimited isn't free!

Mind the buffer size. You’ll be consuming memory, and the more you ask for, the more likely you are to attract the attention of the DBAs.