PL/SQL Programming   «Prev  Next»

Lesson 6Using DBMS_OUTPUT.PUT_LINE
ObjectiveUse the DBMS_OUTPUT.PUT_LINE Package Procedure

DBMS_OUTPUT.PUT_LINE Package Procedure

You can display the value from your PL/SQL block with the DBMS_OUTPUT.PUT_LINE package procedure.
DBMS_OUTPUT is an Oracle-supplied package, and PUT_LINE is a procedure within that package.
To enable the DBMS_OUTPUT package in your SQL*PLUS screen, you must execute the
SET SERVEROUTPUT ON
command.
Within a PL/SQL block, you can reference DBMS_OUTPUT.PUT_LINE and, in parentheses, provide the information you want to print to the screen. The information you put in the parentheses must be a string value.

Integration with SQL

One of the most important aspects of PL/SQL is its tight integration with SQL. You do not need to rely on any intermediate software such as ODBC (Open Database Connectivity) or JDBC (Java Database Connectivity) to run SQL statements in your PL/SQL programs.
Instead, you just insert the UPDATE or SELECT into your code, as shown here:

1 DECLARE
2 l_book_count INTEGER;
3
4 BEGIN
5 SELECT COUNT(*)
6 INTO l_book_count
7 FROM books
8 WHERE author LIKE '%Critchley, Terry%';
9
10 DBMS_OUTPUT.PUT_LINE (
11 'Terry has written (or co-written) ' ||
12 l_book_count ||
13 ' books.');
14
15 --  Update statement
16 UPDATE books
17 SET author = REPLACE(author,'Terry','Terrance')
18 WHERE author LIKE '%Critchley, Terry';
19 END;


Let us take a more detailed look at this code in the following table:

Line(s) Description
  1. 1–3: This is the declaration section of this so-called "anonymous" PL/SQL block, in which I declare an integer variable to hold the number of books that I have authored or coauthored.
  2. 4: The BEGIN keyword indicates the beginning of my execution section, the code that will be run when I pass this block to SQL*Plus.
  3. 5–8: I run a query to determine the total number of books I have authored or coauthored. Line 6 is of special interest: the INTO clause shown here is actually not part of the SQL statement but instead serves as the "bridge" from the database to local PL/SQL variables.
  4. 10–13: I use the DBMS_OUTPUT.PUT_LINE built-in procedure (i.e., a procedure in the DBMS_OUTPUT package supplied by Oracle) to display the number of books.
  5. 15: This single-line comment explains the purpose of the UPDATE.
  6. 16–18: I have decided to change the spelling of my first name to "Terrance", so I issue an update against the books table. I take advantage of the built-in REPLACE function to locate all instances of "Terry" and replace them with "Terrance"
|
Oracle DBMS Packages

PL/SQL Hierarchical Profiler

As of Oracle 11g, you can use the PL/SQL Hierarchical Profiler to report the dynamic execution time by subprogram calls. Its output displays the time for the PL/SQL steps apart from the SQL execution time. The profile package, DBMS_HPROF, generates output for each function call. See the package documentation for details on the generation and interpretation of its output.
You may use the DBMS_OUTPUT package, one of a set of packages that is automatically installed when you create an Oracle database. To use DBMS_OUTPUT, you must issue the set serveroutput on command before executing the procedural object you will be debugging. DBMS_OUTPUT allows you to use three debugging functions within your package:

PUT Puts multiple outputs on the same line
PUT_LINE Puts each output on a separate line
NEW_LINE Used with PUT; signals the end of the current output line

PUT and PUT_LINE are used to generate the debugging information you want to display. For example, if you are debugging a procedure that includes a loop, you may want to track the changes in a variable with each pass through the loop. To track the variable’s value, you may use a command similar to the one shown in the following listing. In this example, the value of the Owed_Amount column is printed, prefixed by the literal string 'Owed:'.
DBMS_OUTPUT.PUT_LINE('Owed:'||Owed_Amount);

To use DBMS_OUTPUT, you should first issue the set serveroutput on command within your session. You may also use PUT and PUT_LINE outside of loops, but such uses may be better accomplished via the use of the return command in functions.
SET SERVEROUTPUT ON
DECLARE
BEGIN 
DBMS_OUTPUT.PUT_LINE (‘string value that needs 
to be displayed on the screen’)
END;

The declaration of a host variable and referencing it within your PL/SQL block has been discussed.
Take a look at the following link to understand exactly how to accept the price of a pet and display the price on the screen.
Values SQL/Plus Screen
In the next lesson, the importance of naming conventions will be discussed.

Oracle Naming Conventions Exercise

Click the Exercise link below to use bind and host variables in a simulation.
Oracle Naming Conventions - Exercise