|Lesson 6||Using DBMS_OUTPUT.PUT_LINE|
|Objective||Use 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
package in your SQL*PLUS screen, you must execute the
SET SERVEROUTPUT ON
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:
2 l_book_count INTEGER;
5 SELECT COUNT(*)
6 INTO l_book_count
7 FROM books
8 WHERE author LIKE '%Critchley, Terry%';
10 DBMS_OUTPUT.PUT_LINE (
11 'Terry has written (or co-written) ' ||
12 l_book_count ||
13 ' books.');
15 -- Update statement
16 UPDATE books
17 SET author = REPLACE(author,'Terry','Terrance')
18 WHERE author LIKE '%Critchley, Terry';
Let us take a more detailed look at this code in the following table:
- 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.
- 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.
- 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.
- 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.
- 15: This single-line comment explains the purpose of the UPDATE.
- 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:
||Puts multiple outputs on the same line
||Puts each output on a separate 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:'.
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
DBMS_OUTPUT.PUT_LINE (‘string value that needs
to be displayed on the screen’)
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