How to use dbms_output package ? | dbms_output.put_line examples

In my previous article I have already given information about the oracle packages and how to create the packages in oracle. In this article we need to give more information about the oracle supplied packages in detail . We will see what is mean by oracle supplied packages and then i would like to throw light on How to use dbms_output package in detail . We are using dbms_outout.put_line in most of the PL/SQL examples. I would like to give the detailed introduction and examples of dbms_output.Put_line in detail.

In this article we will find information about :

1. What are oracle supplied packages?
2. What is dbms_output package with examples?
3. What are procedures and options of dbms_output with examples?

What are oracle supplied packages ?

In this section I would like to give more information about the oracle supplied packages. These are the packages provided internally by the oracle to extend the functionality of databases. These packages enables the access to the user where certain features are not working in PL/SQL.

The following are few oracle supplied packages. In this article we will discuss more about the DBMS_OUTPUT package in detail.

Example :

The dbms_output package has specifically designed to debug the PL/SQL programs but now a days users are using that package for different purpose.

What is dbms_output package with examples?

In this section i would like to give you examples of dbms_output package and how it works in different steps.

The key purpose of dbms_output package is it enables you to send the message to stored procedures, functions, packages and triggers. In C you have printf and scanf functions. dbms_output function has same functionality which sent the message to objects of databases. This package is using buffer to keep the message which has 20000 as highest limit.

1. Put function and PutLine function places the string in buffer.
2. GETLINE or GETLINES reads the string from the buffer.
3. Messages are not sent until sending procedures, functions or any database objects complete the program.
4. You require to on SET SERVEROUTPUT ON to display the messages on Oracle SQL developer or SQL * PLUS.

Simple Example : If user want to print hello PL/SQL

Declare

Begin

DBMS_OUTPUT.PUT_LINE(“Hello PL/SQL”);

End;

The PUT_LINE function will print the Hello PL/SQL on oracle console.

What are procedures and options of dbms_output package with examples?

In tis section I would like to give some options and procedures of dbms_output with examples in detail. The following are few scenarios :

1.Setting up the buffer size using Enable statement :

As we have given that the buffer size for the package is 20000 initially. But what if you want to use the data more than 20000 characters. There is way to set up the property or buffer size between 20000 to 1000000 as maximum buffer size.

Real life example :

If you want to print the statement which has more than 20000 characters then set the buffer size accordingly using following statement.

dbms_output.enable(350000); — You can set the max 1,000,000 as buffer size.

2.Message display enable or disable :

With using the enable and disable keywords you can set up the message sending enabled or disabled.

Example :

begin

dbms_output.disable; —Due to this statement you can not be able to see message
dbms_output.put_line(‘Disabled sending’);
dbms_output.enable;
dbms_output.put_line(‘Enabled sending’); –Due to enable settings it will only print this message
end;

3.How to use the newline function :

The newline functions combines all the lines which are calling from Put function.

Example :

begin
dbms_output.put_line(‘PL SQL Code for new_line’);
dbms_output.put(‘PLSQL ‘);
dbms_output.put(‘Program ‘);
dbms_output.put(‘for ‘);
dbms_output.put(‘new_line ‘);
dbms_output.new_line; –you should use this after put function call

end;

The output of above program is :

PL SQL Code for new_line

PLSQL Program for new_line

Tne new_line function will combine buffer used by put function together.

Get_Line examples :

The Get_line procedure retrieves the current line from the buffer in to procedure variable. The get_line procedure has two parameters one is varchar2 and other is integer. Just make sure that get_line procedure will retrieve a single line of buffered information. The second parameter of GET_LINE function is integer parameter which gives value as 0 if call completes successfully. If there are no lines in buffer then status will be 1. If you want to read directly from the buffer we require to use Get_line procedure. It will fetch only 1 line in the buffer.

–get_line function requires to use the buffer

DECLARE
V_buffer_line VARCHAR2(100); —define variable for displaying buffer line
V_status INTEGER; —Status variable
begin
dbms_output.put_line(‘line not in buffer’);
dbms_output.put_line(‘line in buffer’);
–This procedure retrieves a single line of buffered information.
dbms_output.get_line(V_buffer_line, V_status); –both are out parameters
dbms_output.put_line(‘Buffer Line: ‘ || V_buffer_line);
dbms_output.put_line(‘Status: ‘ || V_status);
/*If the call completes successfully,
then the status returns as 0. If there are no more
lines in the buffer, then the status is 1.
*/
end;

The output of this is :

Buffer Line : line in buffer

Status : 0

If you want to display multiple lines in buffer just use the for loop to display it.

There is another procedure named Put_lines also which will retrieve the array from the oracle. If you want the examples kindly contact me on complexsql@gmail.com.

These are some examples of dbms_output in built package of oracle. If you like this article or if you have any issues with the same kindly comment in comments section.