Oracle PL/SQL Programming, 6th Edition

Chapter 22. I/O and PL/SQL

Many, perhaps most, of the PL/SQL programs you write need to interact only with the underlying Oracle database using SQL. However, there will inevitably be times when you will want to send information from PL/SQL to the external environment or read information from some external source (screen, file, etc.) into PL/SQL. This chapter explores some of the most common mechanisms for I/O in PL/SQL, including the following built-in packages:

DBMS_OUTPUT

For displaying information on the screen

UTL_FILE

For reading and writing operating system files

UTL_MAIL and UTL_SMTP

For sending email from within PL/SQL

UTL_HTTP

For retrieving data from a web page

It is outside the scope of this book to provide full reference information about the built-in packages introduced in this chapter. Instead, I will demonstrate how to use them to handle the most frequently encountered requirements. Check out Oracle’s documentation for more complete coverage. You will also find Oracle Built-in Packages (O’Reilly) a helpful source for information on many packages; several chapters from that book are available on this book’s website.

Displaying Information

Oracle provides the DBMS_OUTPUT package to enable you to send information from your programs to a buffer. This buffer can then be read and manipulated by another PL/SQL program or by the host environment. DBMS_OUTPUT is most frequently used as a simple mechanism for displaying information on your screen.

Each user session has a DBMS_OUTPUT buffer ...