Getting Started with PLSQL

Getting Started with PL/SQL

To start, we'll be using anonymous blocks interactively using SQL*Plus. PL/SQL programs can provide output in one of three ways: by writing to the database, by returning a value (either as a return value or through an output parameter), or by writing to a buffer, using the DBMS_OUTPUT utility package. SQL*Plus can redirect the contents of this buffer to the screen, which makes it much easier to write and debug PL/SQL programs.

The DBMS_OUTPUT package is one of a large number of packages that Oracle provides to extend the functionality of the database, SQL, and PL/SQL. These packages are normally installed automatically when an Oracle database is created. Each package contains a number of related procedures or functions. DBMS_OUTPUT provides the following routines for writing to a buffer:

 PUT(arg); PUT_LINE(arg); 

PUT() and PUT_LINE() are similar to the System.out.print() and System.out.println() methods in Java. The arg parameter can be any valid PL/SQL type PL/SQL will perform the appropriate conversion to text automatically.

PUT() and PUT_LINE() differ from System.out.print() and System .out.println() in that they do not print to the screen but rather to a memory buffer. To get the text out of the memory buffer, DBMS_OUTPUT provides the complementary routines GET() and GET_LINE(), but we don't need to use them directly. The following command will cause SQL*Plus to display on screen everything sent to the buffer by PUT() and PUT_LINE():

 SQL> SET SERVEROUTPUT ON SIZE 20000 FORMAT WRAPPED 

After a PL/SQL block executes, SQL*Plus will automatically use GET_LINE() to retrieve the contents of the buffer and send them to the screen. This SET command also includes options to increase the output buffer size from the default 2,000 bytes to 20,000 and improve the format.

Before diving into the details of PL/SQL, let's write and run a simple program to get familiar with the mechanics that are involved and make sure everything is working right. You can type the code directly in SQL*Plus at the SQL prompt or you can type it in a text editor, such as Notepad or vi, save it as hello.sql, then run it from an sqlplus prompt with the START command. We'll take this last approach. Here is the program:

 /* A first PL/SQL program: hello.sql */ BEGIN   DBMS_OUTPUT.PUT_LINE('Hello, world!'); END; 

After saving the program and starting SQL*Plus from a command prompt in the same directory where we saved the program, we can run the program like this:

 SQL> start hello   4  / Hello, world! PL/SQL procedure successfully completed. 

Notice that we need to type a slash, (/), followed by Enter, to execute the program.