Learning the Basics of PL/SQL - Page 4
June 4, 2001
Executing PL/SQL Using Developer 2000's Procedure Builder
Procedure Builder is part of Oracle's Developer 2000 development
environment. It allows you to develop and debug PL/SQL program units for use in
Developer 2000 applications. With Developer 2000, PL/SQL is used on the client
to program the behavior behind the forms, reports, and menus that you develop.
You end up with a PL/SQL engine on the client as well as on the server. A nice
benefit of this is that Procedure Builder can be used to execute PL/SQL code
without having to be connected to a database.
Note - Many of the advanced features discussed
later in this book are available only when executing PL/SQL code in the
Starting Procedure Builder
If you have Developer 2000 installed, you start Procedure Builder by selecting
Start, Programs, Developer 2000 R2.0, Procedure Builder. The opening screen
is shown in Figure 1.5 and is divided into
Procedure Builder's opening screen.
(Click for full size)
As you can see, the Procedure Builder window is divided into three major
parts. The Object Navigator window allows you to navigate through the various
program units, PL/SQL libraries, and database objects to which you have access.
The other two parts of the display combine to make up the PL/SQL Interpreter
window. The top pane is used when debugging PL/SQL code and shows the code being
debugged. The bottom pane is where you can type in and execute ad-hoc PL/SQL
New Term - PL/SQL may be used to write
procedures, functions, package bodies, package types, and triggers. These
constructs are referred to as program units.
Using Interactive PL/SQL
The PL/SQL interpreter allows you to enter a PL/SQL anonymous block and have
it executed. The small block in Listing 1.2, that you typed in earlier, is one
such anonymous block. You can type that block into Procedure Builder and execute
it, but first you need to make one small change. The code shown in Listing 1.2
contains the following two calls to
dbms_output.put_line('The variable X = ');
DBMS_OUTPUT is a package that only exists within the database
server. Procedure Builder will return errors if you try to execute the code as
it stands now. Fortunately, Oracle has a package similar to
that can be used in its place when you are executing code on a client. The name
of that package is
TEXT_IO, and it also contains an entry point named
PUT_LINE. Take the code shown in Listing 1.2, replace the calls to
TEXT_IO.PUT_LINE, and you have the
code shown in Listing 1.6. This code will run from Procedure Builder.
Listing 1.6 A PL/SQL Block Using
TEXT_IO That Will Run from Procedure Builder
x := 72600;
text_io.put_line('The variable X = ');
Now, you can take this code and type it into Procedure Builder's PL/SQL
Interpreter. The interpreter will automatically execute the block when you
finish entering the last line. The results will look like the following:
The variable X =
Procedure Builder has been written specifically to work with PL/SQL. Unlike
SQL*Plus, you do not need to enter a forward-slash to tell Procedure Builder
that you are done entering a block of PL/SQL.
Creating a function (or any other program unit such as a procedure or package)
using Procedure Builder requires a bit more than just typing the
FUNCTION statement into the interpreter. To create a function, you need
to tell Procedure Builder that you want to create a new program unit. Do this
by selecting the File, New, Program Unit menu option. You will see the dialog
box shown in Figure 1.6.
Creating a New Program Unit.
This dialog contains radio buttons allowing you to choose the type of program
unit that you are creating and also contains a textbox for the program unit's
name. Choose Function, type the name
SS_THRESH into the textbox, and
click OK. You will see a screen similar to that shown in Figure
Entering the code for
Figure 1.7 shows the function with the code
already written. Of course, Procedure Builder does not write the code for you.
When Procedure Builder opens this window, it places a skeleton function in the
textbox. You have to fill in the details. When you get the code entered the
way that you want it, click the Compile button to compile it, and then click
the Close button to close the window.
To execute the function that you just created, type the following statement
into the PL/SQL interpreter:
When you execute this statement, Procedure Builder will execute the function
and display the following results:
Connecting to a Database
In addition to creating PL/SQL program units on the client, Procedure Builder
can also be used to create and execute program units in a database. To do this,
you first need to connect to a database. Use the File, Connect menu option to
connect to a database. Once you've logged in, you will be able to browse
database program units using the Object Navigator. Figure
1.8 shows the program units owned by the user named
Program units in the
To create a stored function or other program unit in the database, follow
Click to highlight the Stored Program Units entry under the
Click the Create Toolbar button.
Proceed as you would when creating a local program unit.
Except for having to choose the schema, the process for creating a PL/SQL
function in the database is the same as for creating one locally.
Using SQLPlus Worksheet
If you have Enterprise Manager available, consider using SQLPlus Worksheet
for the examples in this book. SQLPlus Worksheet is completely compatible with
SQL*Plus, and can be used for all the examples in this book. The advantage that
SQL*Plus worksheet has over SQL*Plus is in the interface. Rather than type in
large blocks of code one line at a time, you can use a text editor-like
interface. After you get the code entered the way that you want it, you can
click a toolbar button to execute it.
Executing a PL/SQL Block Using SQLPlus Worksheet
Figure 1.9 shows the SQLPlus Worksheet.
The SQLPlus Worksheet.
As you can see, the SQLPlus Worksheet screen is divided into two
halves. The upper half is used for the entry and editing of SQL statements and
PL/SQL blocks. The lower half is used to display output. The execute toolbar
button, the one with the lightning bolt, is used to execute the statements that
you have entered in the upper pane.
There are two ways to use SQLPlus Worksheet to execute commands from a file.
One way is to use the File, Open menu option to load the contents of a file into
the upper pane, and then click the lightning bolt button. The other way is to
use the Worksheet, Run Local Script menu option.
In this chapter you learned a little about PL/SQL, what it is, and why it is
used. You know that PL/SQL is Oracle's procedural language extension to
SQL, and that you can use it to write procedures and functions that execute on
This chapter also explains the relationship between PL/SQL, SQL, and
SQL*Plus. This should give you a good grasp of how PL/SQL fits into the larger
You wrote your first PL/SQL stored function, which should give you a good
feel for the mechanics of programming with PL/SQL.
SQL*Plus is the tool used throughout this book for PL/SQL code examples.
SQLPlus Worksheet and Procedure Builder are two other tools that may also be
used to write and execute PL/SQL code.
Q & A
Where does PL/SQL code execution take place?
Usually, execution takes place at the server level. For the
examples in this book, that will always be the case. Some Oracle products, such
as Developer/2000, also have the capability to execute PL/SQL blocks locally on
the client machine.
Can I write a complete application with PL/SQL?
Generally speaking you cannot, at least not as most people
envision an application. For an end-user application, you would still need a
tool, such as PowerBuilder or Developer/2000, in order to design screens and
I executed some PL/SQL code which used
dbms_output.put_line() to print some data, but I didn't see
anything. How come?
You probably forgot to enable the server output option. Use
this SQL*Plus command:
SET SERVEROUTPUT ON
If you forget that, your PL/SQL output goes to oblivion.
I am using Procedure Builder, and I get errors when I try to
execute code that contains calls to
When you use Procedure Builder to execute code
locally, you must use
text_io.put_line rather than
dbms_output.put_line(). If you are using Procedure Builder, and you
have connected to a database, you will be able to execute calls to
dbms_output.put_line(), but you won't see the results.
Use the following workshop to test your comprehension of this chapter and put
what you've learned into practice. You'll find the answers to the quiz
and exercises in Appendix A, "Answers."
What tells SQL*Plus to send your PL/SQL code to the Oracle database for
What is the fundamental basis of all PL/SQL code?
List an advantage of pushing program logic up to the server
Name three Oracle products that use PL/SQL.
What command tells SQL*Plus to display PL/SQL output?
Name at least two options for managing your PL/SQL source code.
If you didn't encounter any errors when compiling your first
function, try putting some in on purpose. Then try out the
Try each of the three ways mentioned in the chapter for
managing your source code. Become familiar with the SQL*Plus
command. Try using the
@ command or the
START command to
execute your PL/SQL code from a text file.
© Copyright Pearson Education. All rights reserved.
This article is brought to you by Sams Publishing, publisher of Sams
Teach Yourself PL/SQL in 21 Days, Second Edition.