PL/SQL Tutorial
PL/SQL Useful Resources
Selected Reading
- PL/SQL - Object Oriented
- PL/SQL - DBMS Output
- PL/SQL - Date & Time
- PL/SQL - Transactions
- PL/SQL - Collections
- PL/SQL - Packages
- PL/SQL - Triggers
- PL/SQL - Exceptions
- PL/SQL - Records
- PL/SQL - Cursors
- PL/SQL - Functions
- PL/SQL - Procedures
- PL/SQL - Arrays
- PL/SQL - Strings
- PL/SQL - Loops
- PL/SQL - Conditions
- PL/SQL - Operators
- PL/SQL - Constants and Literals
- PL/SQL - Variables
- PL/SQL - Data Types
- PL/SQL - Basic Syntax
- PL/SQL - Environment
- PL/SQL - Overview
- PL/SQL - Home
PL/SQL Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
PL/SQL - DBMS Output
PL/SQL - DBMS Output
In this chapter, we will discuss the DBMS Output in PL/SQL. The DBMS_OUTPUT is a built-in package that enables you to display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers. We have already used this package throughout our tutorial.
Let us look at a small code snippet that will display all the user tables in the database. Try it in your database to pst down all the table names −
BEGIN
dbms_output.put_pne (user || Tables in the database: );
FOR t IN (SELECT table_name FROM user_tables)
LOOP
dbms_output.put_pne(t.table_name);
END LOOP;
END;
/
DBMS_OUTPUT Subprograms
The DBMS_OUTPUT package has the following subprograms −
| S.No | Subprogram & Purpose | |
|---|---|---|
| 1 | DBMS_OUTPUT.DISABLE; Disables message output. |
|
| 2 | DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); Enables message output. A NULL value of buffer_size represents unpmited buffer size. |
|
| 3 | DBMS_OUTPUT.GET_LINE (pne OUT VARCHAR2, status OUT INTEGER); Retrieves a single pne of buffered information. |
|
| 4 | DBMS_OUTPUT.GET_LINES (pnes OUT CHARARR, numpnes IN OUT INTEGER); Retrieves an array of pnes from the buffer. |
|
| 5 | DBMS_OUTPUT.NEW_LINE; Puts an end-of-pne marker. |
|
| 6 | DBMS_OUTPUT.PUT(item IN VARCHAR2); Places a partial pne in the buffer. |
|
| 7 | DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); Places a pne in the buffer. |
Example
DECLARE
pnes dbms_output.chararr;
num_pnes number;
BEGIN
-- enable the buffer with default size 20000
dbms_output.enable;
dbms_output.put_pne( Hello Reader! );
dbms_output.put_pne( Hope you have enjoyed the tutorials! );
dbms_output.put_pne( Have a great time exploring pl/sql! );
num_pnes := 3;
dbms_output.get_pnes(pnes, num_pnes);
FOR i IN 1..num_pnes LOOP
dbms_output.put_pne(pnes(i));
END LOOP;
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Hello Reader! Hope you have enjoyed the tutorials! Have a great time exploring pl/sql! PL/SQL procedure successfully completed.Advertisements