PL/SQL is a programming language developed by Oracle as an extension to SQL in order to allow procedural logic to be implemented at the database level. PL/SQL is used to write stored procedures, stored functions, and triggers, and, beginning with Oracle8, to define object types. It can also be used to simply write a block of procedural code for the database to execute. SQL*Plus was originally one of the only front-ends that could be used to send PL/SQL code to the database, and even today it is still one of the most widely used.
This section explains the mechanics of entering and executing PL/SQL code with SQL*Plus. You’ll learn what PL/SQL mode is, and you’ll learn the differences between entering a PL/SQL block and a SQL query.
If you are unfamiliar with PL/SQL, you may want to pick up a copy of Steven Feuerstein and Bill Pribyl’s book, Oracle PL/SQL Programming, second edition (O’Reilly & Associates, 1997). PL/SQL opens up a world of possibilities. You’ll want to take advantage of it if you are doing serious work with Oracle.
The PL/SQL block is the fundamental unit of PL/SQL programming. The term block refers to a program unit that contains some or all of the following elements:
Variable and subprogram declarations
Procedural code, which may include nested PL/SQL blocks
An error handler
Here is an example of a reasonably simple, but complete, PL/SQL block:
DECLARE
X VARCHAR2(12) := 'Hello World!';
BEGIN
DBMS_OUTPUT.PUT_LINE(X);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred.'),
END;This code contains all the elements of a PL/SQL block, and is one implementation of the traditional “Hello World!” program. Using SQL*Plus, you can send it to the database for execution.
To execute a PL/SQL block, you type it into SQL*Plus and terminate it with a forward slash. The forward slash tells SQL*Plus that you are done entering the block and to send it to the database for execution. Here’s how it would look to enter and execute the code shown previously:
SQL>DECLARE2X VARCHAR2(12) := 'Hello World!';3BEGIN4DBMS_OUTPUT.PUT_LINE(X);5EXCEPTION6WHEN OTHERS THEN7DBMS_OUTPUT.PUT_LINE('An error occurred.'),8END;9/PL/SQL procedure successfully completed.
Now you may be wondering why there was no output from the code block in the previous section. After all, the code does contain a call to the PUT_LINE procedure that sure looks as if it ought to display something.
In fact, the code did generate some output. You just didn’t see it. Remember from Chapter 1 that SQL*Plus itself does not execute PL/SQL code. It just sends that code to the database server, which executes the code for you. The Oracle database server doesn’t have any way to directly display the output for you to see. Instead, any output from PL/SQL code is buffered by the server for later retrieval by the application that executed it, in this case SQL*Plus.
By default, SQL*Plus does not retrieve PL/SQL output from the server. You have to tell it to retrieve the output if you want to see it. The command for that is:
SET SERVEROUTPUT ON
If you enter the above command, followed by the same PL/SQL block that you entered earlier, your output will look like this:
SQL>SET SERVEROUTPUT ONSQL>DECLARE2X VARCHAR2(12) := 'Hello World!';3BEGIN4DBMS_OUTPUT.PUT_LINE(X);5EXCEPTION6WHEN OTHERS THEN7DBMS_OUTPUT.PUT_LINE('An error occurred.'),8END;9/Hello World! PL/SQL procedure successfully completed.
This time around, you do see the output from the block. The SERVEROUTPUT setting “sticks” for the duration of your SQL*Plus session, so you don’t have to keep turning it on each time you execute another block. There are some other parameters to the SET SERVEROUTPUT command that affect formatting and the output buffer size. The SIZE parameter lets you increase the buffer size from the default of 2000 bytes, something you should do if you expect to display a lot of information from PL/SQL. The FORMAT parameter lets you control whether, and how, long lines of output are wrapped when they are displayed. The following example shows how you can turn server output on, allow for a maximum of 1,000,000 bytes to be displayed, and word-wrap any long lines.
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WORD_WRAPPED
Prior to version 8 of SQL*Plus, the SIZE and FORMAT parameters did not exist. To increase the buffer size, you had to make a call to DBMS_OUTPUT.ENABLE. You can read about this in Chapter 11, under the section titled Section 11.4.
When you begin entering a PL/SQL block, SQL*Plus switches to what is called PL/SQL mode. It knows to do this by watching for the keywords BEGIN and DECLARE, either of which may start a PL/SQL block. Once in PL/SQL mode, you can pretty much type anything you please. SQL*Plus simply buffers everything you type until you terminate PL/SQL mode by typing one of the termination characters — either a forward slash or a period on a line by itself. Parsing and syntax checking of your PL/SQL code is done by the database server, not by SQL*Plus, and doesn’t happen until after you have completely entered and terminated the block.
Tip
The following SQL commands also put you into PL/SQL mode: CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE PACKAGE BODY, and CREATE TYPE. That’s because these commands allow you to define stored objects based on PL/SQL code.
The rules for entering a PL/SQL block are as follows:
The first word of a PL/SQL block must be BEGIN, DECLARE, CREATE PROCEDURE, CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, or CREATE TYPE. Lowercase is OK; PL/SQL is not case-sensitive.
PL/SQL blocks may span multiple lines.
Line breaks may occur anywhere you can legally enter whitespace.
Comments, delimited by /*...*/, may be embedded anywhere whitespace is allowed. These commands may span multiple lines.
A double hyphen (—) makes everything after it on the same line a comment.
Blank lines are allowed in a PL/SQL block, but SQL*Plus filters them out.
Entry of a PL/SQL block must be terminated in one of two ways:
By use of the forward slash character. The forward slash must be on a line by itself, and must be in column 1 of that line. Using a forward slash tells SQL*Plus to execute the block you have just entered.
By use of a period. The period must be on a line by itself, and in the first position. Using a period causes the statement to be stored in the SQL buffer rather than being executed immediately.
Because blank lines are allowed within a block of code, they can’t be used to terminate a block. That’s where the period comes into play. Oracle needed to provide a way to enter a PL/SQL block into the buffer without executing it. Since a blank line can’t be used for that purpose, as it can be with a SQL statement, Oracle decided to allow the period on a line by itself to serve this function.
Likewise, because a PL/SQL block may be made up of many statements, each of which itself ends with a semicolon, that character cannot reliably be used as a termination character. So to enter and execute a block, we are left with only the forward slash.
The SQL*Plus EXECUTE command may be used to execute a singe PL/SQL statement. The syntax is simply:
EXECUTE statementwhere:
- EXEC[UTE]
May be abbreviated to EXEC.
- statement
Is the PL/SQL statement you want to execute.
EXECUTE is most helpful when you want to make a quick call to a PL/SQL function. In the following example, EXECUTE is used to make a call to DBMS_OUTPUT.ENABLE, in order to allow more than the default 2000 bytes of PL/SQL output to be displayed.
SQL> EXECUTE DBMS_OUTPUT.ENABLE(10000)The value of 10,000 in this example tells Oracle to allow for up to 10,000 bytes of output to be displayed by the DBMS_OUTUT.PUT_LINE command procedure. The EXECUTE command is nothing more than a SQL*Plus shortcut. SQL*Plus takes whatever text you type after EXECUTE, adds a semicolon to the end, wraps the keywords BEGIN and END around it, and sends it to Oracle as just another PL/SQL block.