ACKNOWLEDGMENTS
Benjamin Rosenzweig: I would like to thank my coauthor, Elena Silvestrova Rakhimov, for
being a wonderful and knowledgeable colleague to work with. I would also like to thank
Douglas Scherer for giving me the opportunity to work on this book, as well as for providing
constant support and assistance through the entire writing process. I am indebted to the team at
Prentice Hall, which includes Trina MacDonald, Songlin Qiu, Todd Taber, Shahdad Moradi, and
Oleg Voskoboynikov. Their contributions, suggestions, and edits helped improve our original
manuscript and make the book what it is today. Finally, I would like to thank my many friends
and family, especially Edward Clarin and Edward Knopping, for helping me through the long
process of putting the whole book together, which included many late nights and weekends.
Elena Silvestrova Rakhimov: My contribution to this book reflects the help and advice of many
people. I am particularly indebted to my coauthor, Benjamin Rosenzweig, for making this project
a rewarding and enjoyable experience. Special thanks to Trina MacDonald, Songlin Qiu, Todd
Taber, and many others at Prentice Hall who diligently worked to bring this book to market.
Thanks to Shahdad Moradi and Oleg Voskoboynikov for their valuable comments and sugges-
tions. Most importantly, to my family, whose excitement, enthusiasm, inspiration, and support
encouraged me to work hard to the very end, and were exceeded only by their love.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

ABOUT THE AUTHORS
Benjamin Rosenzweig is a software development manager at Misys Treasury & Capital Markets,
where he has worked since 2002. Prior to that he was a principal consultant for more than three
years at Oracle Corporation in the Custom Development Department. His computer experience
ranges from creating an electronic Tibetan–English dictionary in Kathmandu, Nepal, to supporting
presentation centers at Goldman Sachs and managing a trading system at TIAA-CREF Rosenzweig
.
has been an instructor at the Columbia University Computer Technology and Application program
in New York City since 1998. In 2002 he was awarded the Outstanding Teaching Award from the
chair and director of the CTA program. He holds a B.A. from Reed College and a certificate in data-
base development and design from Columbia University. His previous books with Prentice Hall are
Oracle Forms Developer: The Complete Video Course (ISBN: 0-13-032124-9) and Oracle Web
Application Programming for PL/SQL Developers (ISBN: 0-13-047731-1).
Elena Silvestrova Rakhimov has more than 15 years of experience in database development
in a wide spectrum of enterprise and business environments, ranging from nonprofit organiza-
tions to Wall Street. She currently works at Alea Software, where she serves as Senior Developer
and Team Lead. Her determination to stay hands-on notwithstanding, Rakhimov has managed
to excel in the academic arena, having taught relational database programming at Columbia
University’s highly esteemed Computer Technology and Applications program. She was educated
in database analysis and design at Columbia University and in applied mathematics at Baku State
University in Azerbaijan. She currently resides in Vancouver, Canada.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

This page intentionally left blank
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

INTRODUCTION
PL/SQL New Features in Oracle 11g
Oracle 11g has introduced a number of new features and improvements for PL/SQL. This intro-
duction briefly describes features not covered in this book and points you to specific chapters
for features that are within scope of this book. The list of features described here is also available
in the “What’s New in PL/SQL?” section of the PL/SQL Language Reference manual offered as
part of Oracle help available online.
The new PL/SQL features and enhancements are as follows:
. Enhancements to regular expression built-in SQL functions
. SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE datatypes
. CONTINUE statement
. Sequences in PL/SQL expressions
. Dynamic SQL enhancements
. Named and mixed notation in PL/SQL subprogram invocations
. Cross-session PL/SQL function result cache
. More control over triggers
. Compound triggers
. Database resident connection pool
. Automatic subprogram inlining
. PL/Scope
. PL/SQL hierarchical profiler
. PL/SQL native compiler generates native code directly
Enhancements to Regular Expression Built-In SQL Functions
In this release Oracle has introduced a new regular expression built-in function,
REGEXP_COUNT. It returns the number of times a specified search pattern appears in a
source string.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

xviii Introduction
FOR EXAMPLE
SELECT
REGEXP_COUNT ('Oracle PL/SQL By Example Updated for Oracle 11g',
'ora', 1, 'i')
FROM dual;
REGEXP_COUNT('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G','ORA',1,'I')
--------------------------------------------------------------------
2
The REGEXP_COUNT function returns how many times the search pattern 'ora' appears in
the source string 'Oracle PL/SQL...' 1 indicates the position of the source string where the
search begins, and 'i' indicates case-insensitive matching.
The existing regular expression built-in functions, REGEXP_INSTR and REGEXP_SUBSTR, have
a new parameter called SUBEXPR. This parameter represents a subexpression in a search pattern.
Essentially it is a portion of a search pattern enclosed in parentheses that restricts pattern
matching, as illustrated in the following example.
FOR EXAMPLE
SELECT
REGEXP_INSTR ('Oracle PL/SQL By Example Updated for Oracle 11g',
'((ora)(cle))', 1, 2, 0, 'i')
FROM dual;
REGEXP_INSTR('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G',...)
------------------------------------------------------------
38
The REGEXP_INSTR function returns the position of the first character in the source string
'Oracle PL/SQL…' corresponding to the second occurrence of the first subexpression 'ora'
in the seach pattern (ora)(cle). 1 indicates the position of the source string where the search
begins, 2 indicates the occurrence of the subexpression in the source string, 0 indicates that the
position returned corresponds to the position of the first character where the match occurs, and
'i' indicates case-insensitive matching and REGEXP_SUBSTR.
SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE
Datatypes
These datatypes are predefined subtypes of the PLS_INTEGER, BINARY_FLOAT, and
BINARY_DOUBLE, respectively. As such, they have the same range as their respective base types.
In addition, these subtypes have NOT NULL constraints.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Introduction xix
These subtypes provide significant performance improvements over their respective base types
when the PLSQL_CODE_TYPE parameter is set to NATIVE. This is because arithmetic opera-
tions for these subtypes are done directly in the hardware layer. Note that when
PLSQL_CODE_TYPE is set to INTERPRETED (the default value), the performance gains are
significantly smaller. This is illustrated by the following example.
FOR EXAMPLE
SET SERVEROUTPUT ON
DECLARE
v_pls_value1 PLS_INTEGER := 0;
v_pls_value2 PLS_INTEGER := 1;
v_simple_value1 SIMPLE_INTEGER := 0;
v_simple_value2 SIMPLE_INTEGER := 1;
-- Following are used for elapsed time calculation
-- The time is calculated in 100th of a second
v_start_time NUMBER;
v_end_time NUMBER;
BEGIN
-- Perform calculations with PLS_INTEGER
v_start_time := DBMS_UTILITY.GET_TIME;
FOR i in 1..50000000 LOOP
v_pls_value1 := v_pls_value1 + v_pls_value2;
END LOOP;
v_end_time := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE ('Elapsed time for PLS_INTEGER: '||
(v_end_time - v_start_time));
-- Perform the same calculations with SIMPLE_INTEGER
v_start_time := DBMS_UTILITY.GET_TIME;
FOR i in 1..50000000 LOOP
v_simple_value1 := v_simple_value1 + v_simple_value2;
END LOOP;
v_end_time := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE ('Elapsed time for SIMPLE_INTEGER: '||
(v_end_time - v_start_time));
END;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.