Lesson 8

This module discussed the tools that you to use to build your queries with PL/SQL. Then you learned about the different variable types that are used within PL/SQL. Further, you enhanced your knowledge of non-PL/SQL bind and host variables. You also reviewed the DBMS_OUTPUT.PUT_LINE package procedure to display the output to your screen. Finally, you learned the importance of using standard naming conventions while writing your code.

The DBMS_OUTPUT.PUT_LINE procedure is a pre-defined packaged procedure within Oracle PL/SQL's DBMS_OUTPUT package. It plays a fundamental role in the debugging and logging processes by enabling developers to output values, messages, or debug information during PL/SQL program execution.
The syntax for DBMS_OUTPUT.PUT_LINE is as follows:

DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2);

Here, item is a VARCHAR2 parameter that represents the string or variable to be output.
This procedure prints a line of text, followed by a newline character, to the buffer. This output is not visible during normal procedure execution and is only visible once the execution completes. However, it's crucial to remember that the output will not be shown unless the server output is turned on. This is accomplished in SQL*Plus or similar tools by using the SET SERVEROUTPUT ON command. The primary purpose of the DBMS_OUTPUT.PUT_LINE procedure is to support debugging operations. As PL/SQL lacks a traditional debugging interface, DBMS_OUTPUT.PUT_LINE is used extensively to display the internal processing status, variable values, or execution flow. This helps developers to trace the execution path, locate the errors, and troubleshoot them accordingly.
Moreover, it is also used to produce formatted results for reporting purposes, or to generate logs of PL/SQL program execution.
Keep in mind that DBMS_OUTPUT.PUT_LINE has a limitation on the length of string it can handle. As of my knowledge cut-off in 2021, the maximum string length is 32,767 bytes.
Lastly, while the DBMS_OUTPUT.PUT_LINE is a handy tool, it is advisable not to use it extensively in a production environment as it can impact the performance of PL/SQL programs. Alternative logging or exception handling methods are recommended for handling errors in production code.

In this module, you learned how to:

1. Define the term PL/SQL and describe some of the common tools used to develop PL/SQL
2. Identify different types of variables
3. Identify variable datatypes
4. Use the non-PL/SQL bind and host variables to manipulate data within SQL*Plus
5. Use the DBMS_OUTPUT.PUT_LINE package procedure to display values to the SQL*Plus screen
6. Illustrate the importance of naming conventions for application code

SQL> CREATE OR REPLACE PROCEDURE award_bonus (
2	emp_id NUMBER, bonus NUMBER) AS
3 commission REAL;
4 	comm_missing EXCEPTION;
5 BEGIN
6  SELECT commission_pct / 100 INTO commission
7   FROM employees
8    WHERE employee_id = emp_id;
9
10  IF commission IS NULL THEN
11   RAISE comm_missing;
12  ELSE
13   UPDATE employees
14   SET salary = salary + bonus*commission
15   WHERE employee_id = emp_id;
16  END IF;
17 EXCEPTION
18  WHEN comm_missing THEN
19   DBMS_OUTPUT.PUT_LINE
20    ('This employee does not receive a commission.');
21    commission := 0;
22  WHEN OTHERS THEN
23   NULL;
24 END award_bonus;
25 /
Procedure created.
SQL>

In this module you were introduced to the following glossary terms:

1. Block: A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size.
2. code walkthrough: Code Walkthrough is a form of peer review in which a programmer leads the review process and the other team members ask questions and spot possible errors against development standards.
3. Global: Globals do not respect the boundaries of program interfaces. You can read and write globals without using the parameter list of a module.
4. Package: A database program consisting of one or more procedures. A package executes when called using SQL or a programming language interface.
5. Procedure: A procedure is a group of PL/SQL statements that you can call by name. A call specification (sometimes called call spec) declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL.
6. Runtime: In computer science, runtime, or execution time is the time during which a program is running a.k.a. (executing).

In the next module, you will learn in detail about datatypes.