Saturday, September 4, 2010

DBMS_OUTPUT in Oracle

Oracle built in with a set of built-in packaged procedures that provide a wealth of functionality for DBA's and developers.

DBMS_OUTPUT

You can use this standard packaged procedure to write messaged to the buffer area and later retrieve those messages.
One of the remarkable usage of this packaged procedure is it capability to display the buffer to your screen if you are using SQLDBA or SQLPLUS.

DBMS_OUTPUT.PUT_LINE(message varchar2);

This procedure is used to Write a message to the session's buffer. You can invoke DBMS_OUTPUT.PUT_LINE(message). either within a PL/SQL block or directly from SQL prompt.

DBMS_OUTPUT.GET_LINE( Message out Varchar2 , Status out integer)
Once this procedure is executed it will return the buffer line into the (Message) variable and will return the Status into the (Status) variable. If a line of information is found in the buffer, the procedure will return a zero in the Status variable, otherwise status is <> 0