Learning the Basics of PL/SQL 27
1
TEXT_IO.PUT_LINE(SS_THRESH);
When you execute this statement, Procedure Builder will execute the function and dis-
play the following results:
72600
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
JEFF
.
F
IGURE
1.6
Creating a New
Program Unit.
F
IGURE
1.7
Entering the code for
SS_THRESH
.
I
NPUT
O
UTPUT
03 7982 ch01 11.8.00 11:22 AM Page 27
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

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.
Summary
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 the server.
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 Oracle picture.
You wrote your first PL/SQL stored function, which should give you a good feel for the
mechanics of programming with PL/SQL.
F
IGURE
1.9
The SQLPlus
Worksheet.
The Execute Button
The Entry Pane
The Output Pane
03 7982 ch01 11.8.00 11:22 AM Page 29
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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
Q Where does PL/SQL code execution take place?
A Usually, execution takes place at the server level. For the examples in this book,

Appendix A, “Answers.”
30 Day 1
03 7982 ch01 11.8.00 11:22 AM Page 30
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Learning the Basics of PL/SQL 31
1
Quiz
1. What tells SQL*Plus to send your PL/SQL code to the Oracle database for
execution?
2. What is the fundamental basis of all PL/SQL code?
3. List an advantage of pushing program logic up to the server level.
4. Name three Oracle products that use PL/SQL.
5. What command tells SQL*Plus to display PL/SQL output?
6. Name at least two options for managing your PL/SQL source code.
Exercises
1. If you didn’t encounter any errors when compiling your first function, try putting
some in on purpose. Then try out the
SHOW ERRORS
command.
2. Try each of the three ways mentioned in the chapter for managing your source
code. Become familiar with the SQL*Plus
EDIT
command. Try using the
@
com-
mand or the
START
command to execute your PL/SQL code from a text file.
03 7982 ch01 11.8.00 11:22 AM Page 31
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

and arrays.
T
ABLE
2.1
PL/SQL Datatypes
Datatype Usage
VARCHAR2
Variable-length character strings
CHAR
Fixed-length character strings
NUMBER
Fixed or floating-point numbers
BINARY_INTEGER
Integer values
PLS_INTEGER
New in version 2.3; used for fast integer computations
DATE
Dates
BOOLEAN true
/
false
values
NVARCHAR2
Variable-length character strings using the national character set
NCHAR
Fixed-length character strings using the national character set
ROWID
Used to store physical rowids (obsolete, use UROWID instead)
UROWID
Used to store both physical and logical rowids

PL/SQL also provides subtypes of some datatypes. A subtype represents a spe-
cial case of a datatype, usually representing a narrower range of values than the
parent type. For example,
POSITIVE
is a subtype of
BINARY_INTEGER
that holds only pos-
itive values. In some cases, the subtypes exist only to provide alternative names for com-
patibility with the SQL standard or other popular database brands on the market.
N
EW
T
ERM
Variable Naming Rules
Before you go on to learn about each of the datatypes in detail, you should first consid-
er some basic rules and conventions for naming variables. Oracle has some simple rules
for variable naming. Variable names can be composed of letters, dollar signs, under-
scores, and number signs. No other characters can be used. A variable name must start
with a letter, after which any combination of the allowed characters can be used. The
maximum length for a variable name is 30 characters. Variable names, like those of key-
words and other identifiers, are not case-sensitive.
In addition to the preceding rules, it is often helpful to follow some sort of naming con-
vention for variables and to make their names as descriptive as possible. For example,
although
empyersal
is a legal variable name, your code might be easier to read if you
used
emp_yearly_salary
. Another option, which uses capital letters to highlight each
word in order to dispense with the underscores, is

employee_comments VARCHAR2(10000);
36 Day 2
Even though PL/SQL allows a maximum of 32767 bytes for a
VARCHAR2
vari-
able, the Oracle database does not. The Oracle database itself only allows
VARCHAR2
columns to be a maximum of 4000 bytes long. You can use longer
strings in PL/SQL, but 4000 is the limit (2000 if you are using any release of
Oracle7) if you want to store the string in the database.
Note
Referring to the example declaration of
employee_name
, here are some sample assign-
ment statements showing values that could be assigned to this variable:
employee_name := ‘Jenny Gennick’;
employee_name := ‘Jonathan Gennick’;
VARCHAR2
Subtypes
Oracle has two subtypes defined for
VARCHAR2
, which are
•
VARCHAR
•
STRING
These subtypes exist for compatibility with other database brands and also with the SQL
standard. Both have the exact same meaning as
VARCHAR2
. However, Oracle currently rec-

employee_comments CHAR(10000);
S
YNTAX
04 7982 ch02 11.8.00 11:22 AM Page 36
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing Declarations and Blocks 37
2
Referring to the example declaration of
employee_name
, here are some sample assign-
ment statements showing values that could be assigned to this variable:
employee_name := ‘Jenny Gennick’;
employee_name := ‘Jeff Gennick’;
Because
CHAR
variables are fixed length and the preceding strings are each less than 32
characters long, they will be right-padded with spaces. In other words, enough spaces
will be appended to make them 32 characters long. Thus, the actual values in
employee_name
would be
‘Jenny Gennick ‘
and
‘Jeff Gennick ‘
This point is important to remember, especially when doing string comparisons, because
the trailing spaces count as part of the string. Listing 2.1 illustrates the impact those trail-
ing spaces have when comparing different types of strings.
The Oracle database only allows
CHAR
columns to be 2000 bytes long (255 if
you are using any release of Oracle7). Even though PL/SQL allows a maxi-

10: IF employee_name_c = employee_name_v THEN
11: DBMS_OUTPUT.PUT_LINE(‘The names are the same’);
12: ELSE
13: DBMS_OUTPUT.PUT_LINE(‘The names are NOT the same’);
14: END IF;
15: END;
16: /
17: The names are NOT the same
18:
19: PL/SQL procedure successfully completed.
What happened here? The same value was assigned to both strings (lines 6
and 7), yet they did not test as being equal (line 10). This occurred because the
CHAR
string contains a number of trailing spaces, whereas the
VARCHAR2
string does not.
Day 3, “Writing PL/SQL Expressions,” talks about the issue in detail.
38 Day 2
I
NPUT
O
UTPUT
A
NALYSIS
When comparing
CHAR
strings against
VARCHAR2
strings, use the
rtrim

NUMBER
Datatype
variable_name NUMBER [(precision[,scale])]
In this syntax,
variable_name
is whatever name you want to give this variable.
precision
specifies the number of decimal digits used to represent the value internally.
The range is
1
to
38
, and the default is
38
.
scale
indicates where the decimal point is
and where rounding occurs. The range is
–84
to
127
, and the default is zero.
Here are some examples:
dollar_amount number (5,2);
no_cents number (3);
big_floating number;
shares_traded number (5,-2);
microns number (1,6)
The easiest way to understand precision and scale is to think of
precision

, will result in an error.
Note
The
no_cents
variable, defined in the preceding example as
NUMBER(3)
,would take the
default scale of zero. Thus it could store no digits to the right of the decimal, and all val-
ues will be rounded to the nearest whole number. Assigning it a value of
-123.45
would
result in it being rounded off to
-123
.
The
big_floating
variable, defined only as
NUMBER
, has no
precision
and
scale
speci-
fied in its declaration. Use this to define a floating-point value.
04 7982 ch02 11.8.00 11:22 AM Page 39
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The
shares_traded
variable is interesting because the example declared it with a nega-
tive scale, that is, as

0.0000016
, you would get
0.000002
. Because the precision is only
one, trying to assign a value of
0.00001
would result in an error. 0.00001 is 10 mil-
lionths, which in this case requires two digits of precision to store.
The
NUMBER
datatype is the only numeric datatype that is available both at the database
level and in PL/SQL. It is stored using a hardware-independent representation and
manipulated using hardware-independent code. Oracle guarantees portability of this
datatype across the various platforms supported by Oracle.
NUMBER
Subtypes
Oracle has defined several subtypes of
NUMBER
. Most of these have exactly the same
meaning as, and can be used interchangeably with, the keyword
NUMBER
. Table 2.2 shows
a complete list of
NUMBER
subtypes and describes their use.
T
ABLE
2.2
Subtypes of the NUMBER Datatype
Subtype Usage

NUMBER(38)
.
FLOAT
Same as
NUMBER
.
FLOAT(prec)
Same as
NUMBER(prec)
,but the precision is expressed in terms of binary
bits, not decimal digits. Binary precision can range from
1
through
126
.
40 Day 2
04 7982 ch02 11.8.00 11:22 AM Page 40
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing Declarations and Blocks 41
2
BINARY_INTEGER
The
BINARY_INTEGER
datatype is used for declaring signed integer variables. Compared
to the
NUMBER
datatype,
BINARY_INTEGER
variables are stored in binary format, which
takes less space. Calculations on binary integers can also run slightly faster because the

Things are different however, with the
INTEGER
subtype. Strange as it may seem, it’s
entirely possible to declare an integer variable and use it to store non-integer values.
For example, you can declare a variable as
INTEGER (5,2)
, and use it to store non-
integer values such as 123.45. In this respect,
INTEGER
is more like a synonym for
NUMBER
than a subtype of
NUMBER
. Please don’t use it that way though. If you’re going to use
the
INTEGER
subtype, use it only to declare integer variables.
S
YNTAX
If you are running PL/SQL version 2.3 or later, you have access to the new
PLS_INTEGER
datatype, which is optimized for fast calculations. For new
applications, use PLS_INTEGER instead of
BINARY_INTEGER
.
Tip
BINARY_INTEGER
Subtypes
Oracle has defined four subtypes for the
BINARY_INTEGER

but cannot be null.
NATURALN
Like
NATURAL
but cannot be null.
SIGNTYPE
Restricts a variable to only the values
-1
,
0
, and
1
. Oracle’s built-in
sign()
function returns values in this range depending on whether its argument is
negative, zero, or positive. (New for Oracle8.)
42 Day 2
The
BINARY_INTEGER
subtypes are constraining. There is no way, for example,
to define a
POSITIVE
in such a way as to still allow negative values.
Note
Why would you want to use these subtypes? One reason might be for purposes of docu-
mentation. A subtype might be more descriptive of the type of data you intend to store in
a variable, which can help prevent mistakes by other programmers who later work on the
code. Another reason might be for error detection. If the code is later modified to assign
the wrong type of value to a variable, a
VALUE_ERROR

18: current_date := TO_DATE (‘12-1-2000’,’mm-dd-yyyy’);
19:
20: --Show the effect of trying to set a negative age.
21: --Pretend it’s the year 2000 and we forgot to convert this code.
22: --Note that only the two digit year is retrieved.
23: current_year := TO_NUMBER(TO_CHAR(current_date,’yy’));
24: current_month := TO_NUMBER(TO_CHAR(current_date,’mm’));
25: current_day := TO_NUMBER(TO_CHAR(current_date,’dd’));
26:
27: --Oops! Only two digits allowed for birth year.
28: birth_year := TO_NUMBER(TO_CHAR(birth_date,’yy’));
29: birth_month := TO_NUMBER(TO_CHAR(birth_date,’mm’));
30: birth_day := TO_NUMBER(TO_CHAR(birth_date,’dd’));
31:
32: --Now make the actual computation.
33: IF current_month > birth_month THEN
34: age := current_year - birth_year;
35: ELSIF (current_month = birth_month) and (current_day >= birth_day) THEN
36: age := current_year - birth_year;
37: ELSE
38: age := current_year - birth_year - 1;
39: END IF;
40: END;
41: /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 33
Had the variable

UTPUT
A
NALYSIS
04 7982 ch02 11.8.00 11:22 AM Page 43
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The Syntax for the
PLS_INTEGER
Datatype
variable_name PLS_INTEGER;
In this syntax,
variable_name
is whatever name you want to give to the variable.
Here is a sample declaration:
my_integer PLS_INTEGER;
44 Day 2
S
YNTAX
Because of the performance advantage, Oracle recommends use of the
PLS_INTEGER
datatype over the
BINARY_INTEGER
datatype in all new applica-
tions.
Note
DATE
The
DATE
datatype is used to store date and time values. A better name might perhaps be
DATETIME
because the time component is always there whether you use it or not. The

YNTAX
I
NPUT
04 7982 ch02 11.8.00 11:22 AM Page 44
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Writing Declarations and Blocks 45
2
BOOLEAN
The
BOOLEAN
datatype is used to store
true
/
false
values. Its range is only the two val-
ues,
true
or
false
.
The Syntax for the
BOOLEAN
Datatype
variable_name BOOLEAN;
In this syntax,
variable_name
is the name that you want to give this variable.
Here are some examples:
hired_fired_same_day BOOLEAN;
birthday_is_today BOOLEAN;

occurrences in your program. It can also add to readability. With a variable
named
birthday_is_today
, you know why the comparison was made.
Tip
04 7982 ch02 11.8.00 11:22 AM Page 45
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
LONG
The
LONG
datatype in PL/SQL is just like
VARCHAR2
except that it can store a maximum
of 32760 bytes instead of 32767, which is actually 7 bytes less than the
VARCHAR2
type.
For this reason, you should usually use
VARCHAR2
instead.
The Syntax for the
LONG
Datatype
variable_name LONG(size);
In this syntax,
variable_name
is the name that you want to give this variable, and
size
is the size, in bytes, of the variable. This must be a number between 1 and 32760.
Here are some sample declarations:
emp_comment LONG(32760);

Note
RAW
The
RAW
datatype is used to store strings of byte-oriented data. The difference between a
RAW
and a
VARCHAR2
string is that Oracle does no character set translation on raw data.
Thus, if you are retrieving raw data from an Oracle server using ASCII to a machine
using the EBCDIC character set, no translation would be done.
04 7982 ch02 11.8.00 11:22 AM Page 46
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.