dbms_output Oracle

Dbms_ouput is a Oracle Supplied package uses to display or print the message. Dbms_output is one of the most used in-built package in Oracle.

dbms_package has below program units.

1. procedure disable;

Use to disable the dbms_output.

SQL>set serveroutput on;

SQL> begin
2  dbms_output.put_line(‘abc’);
3  end;
4  /
abc

PL/SQL procedure successfully completed.

SQL> exec dbms_output.disable;

PL/SQL procedure successfully completed.

SQL> begin
2  dbms_output.put_line(‘abc’);
3  end;
4  /

PL/SQL procedure successfully completed.
2. procedure enable(buffer_size in number);

Enables the output.

SQL> exec dbms_output.enable(10000);

PL/SQL procedure successfully completed.

SQL> begin
2  dbms_output.put_line(‘abcd’);
3  end;
4  /
abcd

PL/SQL procedure successfully completed.

3. procedure get_line(line out varchar2,status out number);

It retrieves one line of information from the buffer. Note that this procedure has no use in SQL*Plus since SQL*Plus automatically captures from buffer.

4. procedure get_lines(line out varchar2,numlines in out number);

It retrieves multiple lines of information from the buffer. Note that this procedure has no use in SQL*Plus since SQL*Plus automatically captures from buffer.

5. procedure new_line;

It create a new line for output.

6. procedure put_line(a in varchar2);

This is the most used procedure out of dbms_output. It prints message. Maximum size is 32627.

Important Notes:-

1. In SQL*Plus set serveroutput on command enables dbms_output.

2. In other Oracle PL/SQL tools like TOAD, SQL Developer you need to explicitly enable the dbms_output to show the messages

3.The maximum line size is 32767 bytes.

4. The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.

5. If you get ORU-10027 : Buffer overflow then you need to increase the buffer size

( set set buffer 200000; — In SQL*Plus)

Limitations :-

Dbms_output cannot be usable in some circumstances. For example Oracle stored procedures are called from GUI programs like Java, .Net, Php etc. In these case you can use Autonomous_transactions.

Read also — Simple debugging tool Oracle

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

1 thought on “dbms_output Oracle”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s