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.

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"

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

1. To connect to the database, type a valid user name, password, and service. Type PETSTORE in the User Name box. Type GREATPETS in the Password box. Type MYDB in the Host String box. Click the OK button.
2. To display the result of the PL/SQL block on the screen, set the server output on. Type the following command at the SQL > prompt:
   SET SERVEROUTPUT ON
   End your SET command by clicking ENTER. This tells SQL*Plus to execute the command.
3. Accept the value from the user and store it in the av_price_of_pet variable. Type the following command at the SQL > prompt:
   ACCEPT av_price_of_pet PROMPT 'Please enter the price of the pet: '
   End your ACCEPT command by clicking ENTER. This tells SQL*Plus to execute the command.
4. Next, begin the main block with the DECLARE statement. Type DECLARE at the SQL > prompt.
   End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
5. Next, declare a variable of type NUMBER and call it v_num_price_of_pet. Assign the accepted value from the user to this variable. Type v_price_of_pet NUMBER := &av_price_of_pet; at the 2 prompt.
   End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
6. Start the executable section of the block. Type BEGIN at the 3 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
7. Display the string value using the DBMS_OUTPUT.PUT_LINE package procedure.
   Type DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_price_of_pet)); at the 4 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
8. End the block. Type END; at the 5 prompt. End your command by clicking ENTER. This tells SQL*Plus to move to the next line.
9. Type / at the 6 prompt for SQL*Plus to compile your PL/SQL block. End your command by clicking ENTER.
10. SQL*Plus now displays the result of the compilation of your block.

SQL> -- Invoke standalone procedure with CALL statement
SQL>
SQL> CALL award_bonus(179, 1000);
Call completed.
SQL>
SQL> -- Invoke standalone procedure from within block
SQL>
SQL> BEGIN
2 award_bonus(179, 10000);
3 END;
4 /
PL/SQL procedure successfully completed.
SQL>

Using the BEGIN-END block is recommended in several situations. For example, using the CALL statement can suppress an ORA-n error that was not handled in the PL/SQL subprogram.

Click the Exercise link below to use bind and host variables in a simulation.
Oracle Naming Conventions - Exercise
In the next lesson, the importance of naming conventions will be discussed.