PL/SQL stands for “Procedural Language extensions to the Structured Query Language.” SQL is the now-ubiquitous language for both querying and updating—never mind the name—of relational databases. Oracle Corporation introduced PL/SQL to overcome some limitations in SQL and to provide a more complete programming solution for those who sought to build mission-critical applications to run against the Oracle database. This chapter introduces PL/SQL, its origins, and its various versions. It also offers a guide to additional resources for PL/SQL developers and some words of advice.
Oracle’s PL/SQL language has several defining characteristics:
- It is a highly structured, readable, and accessible language
If you are new to programming, PL/SQL is a great place to start. You will find that it is an easy language to learn and is rich with keywords and structure that clearly express the intent of your code. If you are experienced in other programming languages, you will very easily adapt to the new syntax.
- It is a standard and portable language for Oracle development
If you write a PL/SQL procedure or function to execute from within the Oracle database sitting on your laptop, you can move that same procedure to a database on your corporate network and execute it there without any changes (assuming compatibility of Oracle versions, of course!). “Write once, run everywhere” was the mantra of PL/SQL long before Java appeared. For PL/SQL, though, “everywhere” means “everywhere there is an Oracle database.”
- It is an embedded language
PL/SQL was not designed to be used as a standalone language, but instead to be invoked from within a host environment. So, for example, you can run PL/SQL programs from within the database (through, say, the SQL*Plus interface). Alternatively, you can define and execute PL/SQL programs from within an Oracle Developer form or report (this approach is called client-side PL/SQL). You cannot, however, create a PL/SQL executable that runs all by itself.
- It is a high-performance, highly integrated database language
These days, you have a number of choices when it comes to writing software to run against the Oracle database. You can use Java and JDBC; you can use Visual Basic and ODBC; you can go with Delphi, C++, and so on. You will find, however, that it is easier to write highly efficient code to access the Oracle database in PL/SQL than it is in any other language. In particular, Oracle offers certain PL/SQL-specific enhancements such as the FORALL statement that can improve database performance by an order of magnitude or more.
Oracle has a history of leading the software industry in providing declarative, non-procedural approaches to designing both databases and applications. The Oracle Server technology is among the most advanced, powerful, and stable relational databases in the world. Its application development tools, such as Oracle Forms, offer high levels of productivity by relying heavily on a “paint-your-screen” approach in which extensive default capabilities allow developers to avoid heavy customized programming efforts.
In Oracle’s early years, the declarative approach of SQL, combined with its groundbreaking relational technology, was enough to satisfy developers. But as the industry matured, expectations rose, and requirements became more stringent. Developers needed to get “under the skin” of the products. They needed to build complicated formulas, exceptions, and rules into their forms and database scripts.
In 1988, Oracle Corporation released Oracle Version 6, a major advance in its relational database technology. A key component of that version was the so-called “procedural option” or PL/SQL. At roughly the same time, Oracle released its long-awaited upgrade to SQL*Forms Version 2.3 (the original name for the product now known as Oracle Forms or Forms Developer). SQL*Forms V3.0 incorporated the PL/SQL engine for the first time on the tools side, allowing developers to code their procedural logic in a natural, straightforward manner.
This first release of PL/SQL was very limited in its capabilities. On the server side, you could use PL/SQL only to build “batch-processing” scripts of procedural and SQL statements. You could not construct a modular application or store business rules in the server. On the client side, SQL*Forms V3.0 did allow you to create procedures and functions, although support for functions was not documented, and was therefore not used by many developers for years. In addition, this release of PL/SQL did not implement array support and could not interact with the operating system (for input or output). It was a far cry from a full-fledged programming language.
But for all its limitations, PL/SQL was warmly, even enthusiastically, received in the developer community. The hunger for the ability to code a simple IF statement inside SQL*Forms was strong. The need to perform multi-SQL statement batch processing was overwhelming.
What few developers realized at the time was that the original motivation and driving vision behind PL/SQL extended beyond the desire for programmatic control within products like SQL*Forms. Very early in the life cycle of Oracle’s database and tools, Oracle Corporation had recognized two key weaknesses in their architecture: lack of portability and problems with execution authority.
The concern about portability might seem odd to those of us familiar with Oracle Corporation’s marketing and technical strategies. One of the hallmarks of the Oracle solution from the early 1980s was its portability. At the time that PL/SQL came along, the C-based RDBMS ran on many different operating systems and hardware platforms. SQL*Plus and SQL*Forms adapted easily to a variety of terminal configurations. Yet for all that coverage, there were still many applications that needed the more sophisticated and granular control offered by such host languages as COBOL, C, and FORTRAN. As soon as a developer stepped outside the port-neutral Oracle tools, the resulting application would no longer be portable.
The PL/SQL language was (and is) intended to widen the range of application requirements that can be handled entirely in operating system-independent programming tools. Today, Java and other programming languages offer similar portability. Yet PL/SQL stands out as an early pioneer in this field and, of course, it continues to allow developers to write highly portable application code.
An even more fundamental issue than portability was execution authority. The RDBMS and the SQL language let you tightly control access to, and changes in, any particular database table. For example, with the GRANT command, you can make sure that only certain roles and users can perform an UPDATE on a given table. On the other hand, this GRANT statement can’t ensure that the full set of UPDATEs performed by a user or application is done correctly. In other words, the database can’t guarantee the integrity of a transaction that spans more than one table, as is common with most business transactions.
The PL/SQL language provided tight control and management over logical transactions. One way PL/SQL does this is with the implementation of execution authority. Instead of granting to a role or user the authority to update a table, you grant privleges only to execute a procedure, which controls and provides access to the underlying data structures. The procedure is owned by a separate Oracle RDBMS account (the “definer” of the program), which, in turn, is granted the actual update privileges on those tables needed to perform the transaction. The procedure therefore becomes the “gatekeeper” for the transaction. The only way that a program (whether it’s an Oracle Forms application or a Pro*C executable) can execute the transfer is through the procedure. In this way, the overall application transaction integrity is guaranteed.
In Oracle8i Database, Oracle added considerable flexibility to the execution authority model of PL/SQL by offering the AUTHID clause. With AUTHID, you can continue to run your programs under the definer rights model described earlier, or you can choose AUTHID CURRENT_USER (invoker rights), in which case the programs run under the authority of the invoking (current) schema. Invoker rights is just one example of how PL/SQL has matured and become more flexible over the years.
As powerful as SQL is, it simply does not offer the flexibility and power developers need to create full-blown applications. Oracle’s PL/SQL language ensures that we can stay entirely within the operating system-independent Oracle environment and still write highly efficient applications that meet our users’ requirements.
PL/SQL has come a long way from its humble beginnings. With PL/SQL 1.0, it was not uncommon for a developer to have to tell his or her manager, “You can’t do that with PL/SQL.” Today, that statement has moved from fact to excuse. If you are ever confronted with a requirement and find yourself saying, “There’s no way to do that,” please don’t repeat it to your manager. Instead, dig deeper into the language, or explore the range of PL/SQL packages offered by Oracle. It is extremely likely that PL/SQL today will, in fact, allow you to do pretty much whatever you need to do.
Over the years, Oracle has demonstrated its commitment to PL/SQL, its flagship proprietary programming language. With every new release of the database, Oracle has also made steady, fundamental improvements to the PL/SQL language itself. It has added a great variety of supplied (or built-in) packages that extend the PL/SQL language in numerous ways and directions. It has introduced object-oriented capabilities, implemented a variety of array-like data structures, enhanced the compiler to both optimize our code and provide warnings about possible quality and performance issues, and in general improved the breadth and depth of the language.
Oracle Database 10g PL/SQL is impressive, but not particularly because it offers many new language features and extended capabilities. Instead, it demonstrates Oracle’s continuing strong commitment to supporting this language and the millions of PL/SQL developers and DBAs around the world who use it.
The PL/SQL development team spent over five years redesigning the compiler to support three critical capabilities:
- Automatic, transparent optimization of code
Starting with Oracle Database 10g Release 1, Oracle automatically restructures our code to improve its performance. After extensive testing and benchmarking, Oracle estimates that the non-SQL portions of your PL/SQL program can run twice as fast as they did in Oracle9i Database.
- Compile-time warnings
Starting with Oracle Database 10g Release 1, the compiler can detect potential runtime problems with your code, such as identifying lines of code that will never be run. This process, also known as lint checking, has long been desired by PL/SQL developers.
- Preprocessor support
Starting with Oracle Database 10g Release 2, within your PL/SQL programs you can now specify conditional logic that is processed as your code is compiled. This “ifdef” feature allows you to, among other things, specify different code to be compiled for different versions of Oracle (which will come in handy primarily in future releases of Oracle).
These topics, as well as other new PL/SQL features in Oracle Database 10g, are described briefly later in this chapter and more fully in the appropriate chapters of the book.
In the next section, we take a look at some examples of PL/SQL programs that will familiarize you with the basics of PL/SQL programming.
If you are completely new to programming or to working with PL/SQL (or even SQL, for that matter), learning PL/SQL may seem an intimidating prospect. If this is the case, don’t fret! We are confident that you will find it easier than you think. There are two reasons for our optimism:
Computer languages in general are not that hard to learn, at least compared to a second or third “human language.” The reason? It’s simply that computers are not particularly smart (they “think”—perform operations—rapidly, but not at all creatively). We must rely on a very rigid syntax in order to tell a computer what we want it to do. So the resulting language is also rigid (no exceptions!) and therefore easier for us to pick up.
PL/SQL truly is an easy language, compared to other programming languages. It relies on a highly structured “block” design with different sections, all identified with explicit, self-documenting keywords.
Let’s look at a few examples that demonstrate some key elements of both PL/SQL structure and functionality.
One of the most important aspects of PL/SQL is its tight integration with SQL. You don’t need to rely on any intermediate software “glue” such as ODBC (Open DataBase Connectivity) or JDBC (Java DataBase Connectivity) to run SQL statements in your PL/SQL programs. Instead, you just insert the UPDATE or SELECT into your code, as shown here:
1 DECLARE 2 l_book_count INTEGER; 3 4 BEGIN 5 SELECT COUNT(*) 6 INTO l_book_count 7 FROM books 8 WHERE author LIKE '%FEUERSTEIN, STEVEN%'; 9 9 10 DBMS_OUTPUT.PUT_LINE ( 11 'Steven has written (or co-written) ' || 12 l_book_count || 13 ' books.'); 14 15 -- Oh, and I changed my name, so... 16 UPDATE books 17 SET author = REPLACE (author, 'STEVEN', 'STEPHEN') 18 WHERE author LIKE '%FEUERSTEIN, STEVEN%'; 19 END;
Let’s take a more detailed look at this code in the following table:
Line(s) | Description |
|---|---|
1-3 | This is the declaration section of this so-called “anonymous” PL/SQL block, in which I declare an integer variable to hold the number of books that I have authored or coauthored. (I’ll say much more about the PL/SQL block structure in Chapter 3.) |
4 | The BEGIN keyword indicates the beginning of my execution section—the code that will be run when I pass this block to SQL*Plus. |
5-8 | I run a query to determine the total number of books I have authored or coauthored. Line 6 is of special interest: the INTO clause shown here is actually not part of the SQL statement but instead serves as the “bridge” from the database to local PL/SQL variables. |
10-13 | I use the DBMS_OUTPUT.PUT_LINE built-in procedure (i.e., a procedure in the DBMS_OUTPUT package supplied by Oracle) to display the number of books. |
15 | This single-line comment explains the purpose of the UPDATE. |
16-18 | I have decided to change the spelling of my first name to “Stephen”, so I issue an update against the books table. I take advantage of the built-in REPLACE function to locate all instances of “STEVEN” and replace them with “STEPHEN”. |
PL/SQL offers a full range of statements that allow us to very tightly control which lines of our programs execute. These statements include:
- IF and CASE statements
These implement conditional logic; for example, “If the page count of a book is greater than 1000, then ... “.
- A full complement of looping or iterative controls
These include the FOR loop, the WHILE loop, and the simple loop.
- The GOTO statement
Yes, PL/SQL even offers a GOTO that allows you to branch unconditionally from one part of your program to another. That doesn’t mean, however, that you should actually use it.
Here is a procedure (a reusable block of code that can be called by name) that demonstrates some of these features:
1 CREATE OR REPLACE PROCEDURE pay_out_balance ( 2 account_id_in IN accounts.id%TYPE) 3 IS 4 l_balance_remaining NUMBER; 5 BEGIN 6 LOOP 7 l_balance_remaining := account_balance (account_id_in); 8 9 IF l_balance_remaining < 1000 10 THEN 11 EXIT; 12 ELSE 13 apply_balance (account_id_in, l_balance_remaining); 14 END IF; 15 END LOOP; 16 END pay_out_balance;
Let’s take a more detailed look at this code in the following table:
Line(s) | Description |
|---|---|
1-2 | This is the header of a procedure that pays out the balance of an account to cover outstanding bills. Line 2 is the parameter list of the procedure, in this case consisting of a single incoming value (the identification number of the account). |
3-4 | This is the declaration section of the procedure. Notice that instead of using a DECLARE keyword, as in the previous example, the keyword IS (or AS) is used to separate the header from the declarations. |
6-15 | Here is an example of a simple loop. This loop relies on an EXIT statement (see line 11) to terminate the loop; FOR and WHILE loops specify the termination condition differently. |
7 | Here I call the account_balance function to retrieve the balance for this account. This is an example of a call to a reusable program within another reusable program. Line 13 demonstrates the calling of another procedure within this procedure. |
9-14 | Here is an IF statement that can be interpreted as follows: if the account balance has fallen below $1,000, stop allocating funds to cover bills. Otherwise, apply the balance to the next charge. |
The PL/SQL language offers a powerful mechanism for both raising and handling errors. In the following procedure, I obtain the name and balance of an account from its ID. I then check to see if the balance is too low; if it is, I explicitly raise an exception, which stops my program from continuing:
1 CREATE OR REPLACE PROCEDURE check_account ( 2 account_id_in IN accounts.id%TYPE) 3 IS 4 l_balance_remaining NUMBER; 5 l_balance_below_minimum EXCEPTION; 6 l_account_name accounts.name%TYPE; 7 BEGIN 8 SELECT name 9 INTO l_account_name 10 FROM accounts 11 WHERE id = account_id_in; 12 13 l_balance_remaining := account_balance (account_id_in); 14 15 DBMS_OUTPUT.put_line ( 16 'Balance for ' || l_account_name || 17 ' = ' || l_balance_remaining); 18 19 IF l_balance_remaining < 1000 20 THEN 21 RAISE l_balance_below_minimum; 22 END IF; 23 24 EXCEPTION 25 WHEN NO_DATA_FOUND 26 THEN 27 -- No account found for this ID 28 log_error (...); 29 30 WHEN l_balance_below_minimum 31 THEN 32 log_error (...); 33 RAISE; 34 END;
Let’s take a more detailed look at the error-handling aspects of this code in the following table:
Chapter 6 takes you on an extensive tour of PL/SQL’s error-handling mechanisms.
There is, of course, much more that can be said about PL/SQL—which is why you have hundreds more pages of material to study in this book! These initial examples should, however, give you a good feel for the kind of code you will write with PL/SQL, some of its most important syntactical elements, and the ease with which one can write—and read—PL/SQL code.
Each version of the Oracle database comes with its own corresponding version of PL/SQL. As you use more up-to-date versions of PL/SQL, an increasing array of functionality will be available to you. One of our biggest challenges as PL/SQL programmers is simply “keeping up.” We need to constantly educate ourselves about the new features in each version—figuring out how to use them and how to apply them to our applications, and determining which new techniques are so useful that we should modify existing applications to take advantage of them.
Table 1-1 summarizes the major elements in each of the versions (past and present) of PL/SQL in the database. It offers a very high-level glimpse of the new features available in each version. Following the table, you will find more detailed descriptions of “what’s new” in PL/SQL in the latest Oracle version, Oracle Database 10g.
Tip
The Oracle Developer product suite also comes with its own version of PL/SQL, and it generally lags behind the version available in the Oracle RDBMS itself. This chapter (and the book as whole) concentrates on server-side PL/SQL programming.
Table 1-1. Oracle database and corresponding PL/SQL versions
Oracle Database release | PL/SQL version | Characteristics |
|---|---|---|
6.0 | 1.0 | Initial version of PL/SQL, used primarily as a scripting language in SQL*Plus (it was not yet possible to create named, reusable, and callable programs) and also as a programming language in SQL*Forms 3. |
7.0 | 2.0 | Major upgrade to PL/SQL 1.0. Added support for stored procedures, functions, packages, programmer-defined records, PL/SQL tables, and many package extensions. |
7.1 | 2.1 | Supported programmer-defined subtypes, enabled the use of stored functions inside SQL statements, and offered dynamic SQL with the DBMS_SQL package. With PL/SQL 2.1, you could execute SQL DDL statements from within PL/SQL programs. |
7.3 | 2.3 | Enhanced functionality of PL/SQL tables, offered improved remote dependency management, added file I/O capabilities to PL/SQL with the UTL_FILE package, and completed the implementation of cursor variables. |
8.0 | 8.0 | The new version number reflected Oracle’s effort to synchronize version numbers across related products. PL/SQL 8.0 is the version of PL/SQL that supported enhancements of Oracle8 Database, including large objects (LOBs), object-oriented design and development, collections (VARRAYs and nested tables), and Oracle/AQ (the Oracle/Advanced Queuing facility). |
8.1 | 8.1 | The first of Oracle’s i series, the corresponding release of PL/SQL offered a truly impressive set of added functionality, including a new version of dynamic SQL, support for Java in the database, the invoker rights model, the execution authority option, autonomous transactions, and high-performance “bulk” DML and queries. |
9.1 | 9.1 | Oracle9i Database Release 1 came fairly quickly on the heels of its predecessor. The first release of this version included support for inheritance in object types, table functions and cursor expressions (allowing for parallelization of PL/SQL function execution), multilevel collections and the CASE statement and CASE expression. |
9.2 | 9.2 | Oracle9i Database Release 2 put a major emphasis on XML (Extensible Markup Language) but also had some treats for PL/SQL developers, including associative arrays that can be indexed by VARCHAR2 strings in addition to integers, record-based DML (allowing you to perform an insert using a record, for example), and many improvements to UTL_FILE (which allows you to read/write files from within a PL/SQL program). |
10.1 | 10.1 | Oracle Database 10g Release 1 was unveiled in 2004 and focused on support for grid computing, with an emphasis on improved/automated database management. From the standpoint of PL/SQL, the most important new features are transparently available to developers: an optimized compiler and compile-time warnings. |
10.2 | 10.2 | Oracle Database 10g Release 2, coming out in 2005, offers a small number of new features for PL/SQL developers, most notably support for preprocessor syntax that allows you to conditionally compile portions of your program, depending on Boolean expressions you define. |
As we mentioned earlier, Oracle Database 10g PL/SQL offers three very powerful and important new areas of functionality:
Automatic, transparent optimization of code
Compile-time warnings
Preprocessor support
In Oracle Database 10g Release 1, PL/SQL also extends the flexibility of collections, both in FORALL statements and for high-level set processing of nested tables. These and other new capabilities are described briefly in the following subsections, and more thoroughly in the appropriate chapters.
PL/SQL’s optimizing compiler, introduced in Oracle Database 10g Release 1 can improve runtime performance dramatically, imposing a relatively slight overhead at compile time. Fortunately, the benefits of optimization apply both to interpreted and to natively compiled PL/SQL (a feature introduced in the previous release) because optimizations are applied by analyzing patterns in source code.
The optimizing compiler is enabled by default. However, you may want to alter its behavior—either by lowering its aggressiveness or by disabling it entirely. For example, if, in the course of normal operations, your system must perform recompilation of many lines of code, or if an application generates many lines of dynamically executed PL/SQL, the overhead of optimization may be unacceptable. Keep in mind, though, that Oracle’s tests show that the optimizer doubles the runtime performance of computationally intensive PL/SQL.
To change the optimizer settings for the entire database, set the database parameter PLSQL_OPTIMIZE_LEVEL. Valid settings are:
These settings are also modifiable for the current session; for example:
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;
Oracle retains optimizer settings on a module-by-module basis. When you recompile a particular module with nondefault settings, the settings will “stick,” allowing you to recompile later on using REUSE SETTINGS. For example:
ALTER PROCEDURE bigproc COMPILE PLSQL_OPTIMIZE_LEVEL = 0;
and then:
ALTER PROCEDURE bigproc COMPILE REUSE SETTINGS;
Starting with Oracle Database 10g Release 1 you can enable additional compile-time warnings to help make your programs more robust. These warnings highlight potential problems that are not severe enough to raise an exception but may result in runtime errors or poor performance.
To enable these warnings, you need to set the database initialization parameter PLSQL_WARNINGS. This parameter can be set globally in the SPFILE, in your session with the ALTER SESSION command, or with the built-in procedure DBMS_WARNING.
For example, to enable all warnings in your session, execute:
ALTER SESSION SET plsql_warnings = 'enable:all'
Introduced in Oracle Database 10g Release 1, conditional compilation allows the compiler to compile selected parts of a program based on conditions you provide with the $IF directive. Conditional compilation can come in very handy when you need to write a program that automatically takes advantage of version-specific features; you can, for example, run special code during test and debug phases.
PL/SQL’s conditional compilation feature allows you to insert conditional logic that is processed before sending the code to the compiler. The following block shows the use of the $IF selection directive to test the values of preset flags to determine if tracing logic should be compiled into the procedure:
CREATE OR REPLACE PROCEDURE calculate_totals IS
BEGIN
$IF $$oe_debug AND $$oe_trace_level >= 5
$THEN
DBMS_OUTPUT.PUT_LINE ('Tracing at level 5 or higher');
$END
NULL;
END calculate_totals;You can use collections to improve the performance of SQL operations executed iteratively by using bulk binds. Bulk binding with FORALL reduces the number of context switches between the PL/SQL engine and the SQL engine. With previous releases, the collections used with FORALL had to be densely filled (all rows between the first and last defined). However, starting with Oracle Database 10g Release 1, if there are nonconsecutive index values because of deletions, you can use the INDICES OF syntax to skip over the deleted elements:
FORALL i IN INDICES OF inactives
DELETE FROM ledger WHERE acct_no = inactives(i);In addition, with Oracle Database 10g, if you are interested in the values of a sparse collection of integers instead of the indices, you can use the VALUES OF syntax:
FORALL i IN VALUES OF inactives_list
DELETE FROM ledger WHERE acct_no = inactives(i);Oracle provides a variety of datatypes to store 32-bit whole numbers: BINARY_INTEGER, INTEGER, INT, SMALLINT, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, and PLS_INTEGER. Prior to Oracle Database 10g, all of these except PLS_INTEGER were manipulated using the same C-language arithmetic library as the NUMBER datatype. PLS_INTEGER, though, uses machine arithmetic, which is up to three times faster than library arithmetic.
In Oracle Database 10g, the distinction among these datatypes has been eliminated, and all these whole number datatypes now use the speedier machine arithmetic. Binary integer datatypes store signed integers in the range of −231 + 1 to 231 − 1. The subtypes include NATURAL (0 through 231) and POSITIVE (1 through 231) together with the NOT NULL variations NATURALN and POSITIVEN. SIGNTYPE is restricted to three values (−1, 0, 1). PLS_INTEGER is an unconstrained subtype (alias) of BINARY_INTEGER.
Oracle Database 10g Release 1 introduced IEEE 754 compliant floating-point numbers to both SQL and PLSQL. These subtypes are the single-precision BINARY_FLOAT and the double-precision BINARY_DOUBLE. These datatypes require less memory and use native machine arithmetic, thus performing better for scientific or engineering applications that are compute-intensive or that require comparison to infinity or NaN (Not a Number). These two datatypes have binary precision instead of the decimal precision used in the NUMBER family. So, financial applications that are concerned with rounding errors or that require decimal precision should probably not use these floating-point datatypes.
Oracle Database 10g Release 1 added the FORMAT_ERROR_BACKTRACE function to the DBMS_UTILITY package. This function fills a long-standing and very frustrating hole in PL/SQL: when handling an error, how can you find the line number on which the error was originally raised?
In earlier releases, the only way to do this was to allow your exception to go unhandled and then view the full error trace stack. Now, you can (and should) call DBMS_UTILITY.FORMAT_ERROR_BACKTRACE instead to obtain that stack and manipulate it programmatically within your program. Here is a very simple example demonstrating a call to this function:
CREATE OR REPLACE PROCEDURE my_procedure IS
BEGIN
run_some_logic;
EXCEPTION
WHEN OTHERS THEN
Write_to_log (
DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
/The SQL language has long offered the ability to apply set operations (UNION, INTERSECT, and MINUS) to the result sets of queries. You can now use similar high-level, very powerful operators against nested tables (and only nested tables) in your PL/SQL programs. You can also perform equality comparisons between nested tables.
The following block of code offers a quick example of many of these new features:
DECLARE
TYPE nested_type IS TABLE OF NUMBER;
nt1 nested_type := nested_type(1,2,3);
nt2 nested_type := nested_type(3,2,1);
nt3 nested_type := nested_type(2,3,1,3);
nt4 nested_type := nested_type(1,2,4);
answer nested_type;
BEGIN
answer := nt1 MULTISET UNION nt4; -- (1,2,3,1,2,4)
answer := nt1 MULTISET UNION nt3; -- (1,2,3,2,3,1,3)
answer := nt1 MULTISET UNION DISTINCT nt3; -- (1,2,3)
answer := nt2 MULTISET INTERSECT nt3; -- (3,2,1)
answer := nt3 MULTISET EXCEPT nt2; -- (3)
answer := SET(nt3); -- (2,3,1)
IF 3 MEMBER OF (nt3 MULTISET EXCEPT nt2) THEN
dbms_output.put_line('3 is in the answer set');
END IF;
END;Oracle Database 10g supports the use of regular expressions inside PL/SQL code via four new built-in functions: REGEXP_LIKE, REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE. Regular expression support is a very exciting development for PL/SQL; the REGEXP_REPLACE function adds especially helpful functionality.
Using REGEXP_SUBSTR, you can do such things as extract text that matches a phone number pattern from a string. Using REGEXP_REPLACE, you can reformat that phone number in place. The following code block illustrates both functions:
DECLARE
my_string VARCHAR2(60)
:= 'The phone number 999-888-7777 is not ours.';
phone_number VARCHAR2(12);
phone_number_pattern VARCHAR2(60)
:= '([[:digit:]]{3})-([[:digit:]]{3}-[[:digit:]]{4})';
BEGIN
--Extract and display the phone number, if there is one.
phone_number := REGEXP_SUBSTR(my_string, phone_number_pattern);
DBMS_OUTPUT.PUT_LINE(phone_number);
--Reformat the phone number
my_string := REGEXP_REPLACE(
my_string, phone_number_pattern, '(\1) \2');
--Show the newly formatted string
DBMS_OUTPUT.PUT_LINE(my_string);
END;
/The output from this code block is:
999-888-7777
The phone number (999) 888-7777 is not ours.Oracle has done innovative work here! We have seen regular expression implementations in other database products, and typically such implementations don’t extend beyond the ability to use regular expressions for searching, such as in the LIKE predicate of a SQL SELECT statement. Oracle lets us do more than just search, and we expect to see many creative solutions involving these new regular expression features.
Starting with Oracle Database
10g Release 1, you can define your own
quoting mechanism for string literals in both SQL and PL/SQL. Use
the characters q' (q followed by a single quote) to note
the programmer-defined delimiter for your string literal. Oracle
interprets the character following the q' as the delimiter for your string.
NCHAR and NVARCHAR delimiters are preceded with the letter
nq as in nq'^nchar string^'.
This technique can simplify your code when single quotes
appear within a string, such as the literals in a SQL statement.
If you define your delimiter with one of the four bracketing
characters, ( [ { <, you
need to use the right-side version of that bracketing character as
the closing delimiter. For example q'[ needs to be closed with ]'.
The following table compares how you would quote certain strings using the old and new mechanisms:
Oracle continues to add many new built-in or supplied packages with each release of the database. Here are some of the most significant packages in Oracle Database 10g for PL/SQL developers:
- DBMS_SCHEDULER
Represents a major update to DBMS_JOB. DBMS_SCHEDULER provides much improved functionality for scheduling and executing jobs defined via stored procedures.
- DBMS_CRYPTO
Offers the ability to encrypt and decrypt common Oracle datatypes, including RAWs, BLOBs, and CLOBs. It also provides globalization support for encrypting data across different character sets.
- DBMS_MONITOR
Provides an API to control additional tracing and statistics gathering of sessions.
- DBMS_WARNING
Provides an API into the PL/SQL compiler warnings module, allowing you to read and change settings that control which warnings are suppressed, displayed, or treated as errors.
With the release of the fourth edition, we are celebrating the tenth anniversary of Oracle PL/SQL Programming! O’Reilly published the first edition of this book back in 1995. At that time, Oracle PL/SQL Programming made quite a splash. It was the first independent (i.e., not emanating from Oracle) book on PL/SQL, and it fulfilled a clear and intensely felt need of developers around the world. Since that time, resources—books, development environments, utilities, and web sites—for PL/SQL programmers have proliferated. (Of course, this book is still by far the most important and valuable of these resources!)
The following sections describe very briefly many of these resources. By taking full advantage of these resources, many of which are available either free or at a relatively low cost, you will greatly improve your development experience (and resulting code).
Over the years, the Oracle PL/SQL series from O’Reilly has grown to include quite a long list of books. Here we’ve summarized the books currently in print. Please check out the Oracle area of the O’Reilly web site (http://oracle.oreilly.com) for much more complete information.
- Oracle PL/SQL Programming, by Steven Feuerstein with Bill Pribyl
The 1,200-page tome you are reading now. The deskside companion of a great many professional PL/SQL programmers, this book is designed to cover every feature in the core PL/SQL language. The current version covers through Oracle Database 10g Release 2.
- Learning Oracle PL/SQL, by Bill Pribyl with Steven Feuerstein
A comparatively gentle introduction to the language, ideal for new programmers and those who know a language other than PL/SQL.
- Oracle PL/SQL Best Practices, by Steven Feuerstein
A relatively short book that describes more than 100 best practices that will help you produce high-quality PL/SQL code. Having this book is kind of like having a “lessons learned” document written by an in-house PL/SQL expert. Although written originally for Oracle8i Database, virtually all the advice in this book is applicable to newer versions as well.
- Oracle PL/SQL Developer’s Workbook, by Steven Feuerstein with Andrew Odewahn
Contains a series of questions and answers intended to help PL/SQL programmers develop and test their understanding of the language. Current through Oracle8i Database.
- Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz
A reference guide to the prebuilt packages that Oracle supplies with the core database server. The use of these packages can sometimes simplify the difficult and tame the impossible. This book is current only through Oracle8 Database, but the discussion of the included packages will still be very helpful. For a more up-to-date summary of package specification syntax, check out Oracle in a Nutshell, by Rick Greenwald and David C. Kreines.
- Oracle PL/SQL Language Pocket Reference, by Steven Feuerstein, Bill Pribyl, and Chip Dawes
A small but very useful quick-reference book that might actually fit in your coat pocket. Summarizes the syntax of the core PL/SQL language through Oracle Database 10g.
- Oracle PL/SQL Built-ins Pocket Reference, by Steven Feuerstein, John Beresniewicz, and Chip Dawes
Another helpful and concise guide summarizing built-in functions and packages through Oracle8 Database.
- Oracle PL/SQL CD Bookshelf
Contains an electronic version of most of the previous books. Current through Oracle8i Database.
There are also some excellent web sites available to PL/SQL programmers:
- Oracle Technology Network
Join the Oracle Technology Network (OTN), which “provides services and resources that developers need to build, test, and deploy applications” based on Oracle technology. Boasting membership in the millions, OTN is a great place to download Oracle software, documentation, and lots of sample code. URL: http://otn.oracle.com.
- Quest Pipelines
Quest Software offers “a free Internet portal community...designed to inform, educate, and inspire IT professionals around the world.” Originally the PL/SQL Pipeline, the Quest Pipelines now offer discussion forums, monthly tips, downloads, and, in essence, free consulting for developers and DBAs around the world on multiple database systems, including Oracle, DB2, SQL Server, and MySQL. URL: http://www.quest-pipelines.com.
- PL/Net.org
PLNet.org is a repository of open source software, maintained by Bill Pribyl, that is written in PL/SQL or is otherwise for the benefit of PL/SQL developers. You can read more about the project’s background or check out the FAQ (Frequently Asked Questions). You will also be directed to a number of utilities, such as utPLSQL, the unit-testing framework for PL/SQL developers. URL: http://plnet.org.
- Open Directory Project
Courtesy of the “dmoz” (Directory Mozilla) project, here you can find a choice set of links to PL/SQL sites. There is also a subcategory called “Tools” with a fairly comprehensive set of links to both commercial and noncommercial developer tools. URL: http://dmoz.org/Computers/Programming/Languages/PL-SQL/.
- Steven Feuerstein’s Oracle PL/SQL Programming site
This web site offers trainings, downloads, and other resources for PL/SQL developers, mostly produced by Steven Feuerstein. You can download all his seminar materials, plus supporting code. Examples from this book are also available there. URL: http://www.oracleplsqlprogramming.com.
- Ounit and utPLSQL
utPLSQL is an open source unit testing framework for PL/SQL developers; Ounit offers a graphical interface on top of utPLSQL. Use these tools to standardize the way you unit test programs and automate execution of those tests. URLs: http://www.utplsql.com and http://www.ounit.com.
- Qnxo
Qnxo (Quality In, Excellence Out) is an active mentoring product designed by Steven Feuerstein that offers a wide-ranging, searchable repository of reusable code and templates for PL/SQL development. It also features a flexible code generator, a PL/SQL error manager and a graphical interface to utPLSQL. URL: http://www.qnxo.com.
Since 1995, when the first edition of this book was published, we have had the opportunity to train, assist, and work with tens of thousands of PL/SQL developers. In the process, we have learned an awful lot from our students and readers, and have also gained some insights into the way we all do our work in the world of PL/SQL. We hope that you will not find it too tiresome if we share some advice with you on how you can work more effectively with this powerful programming language.
We are almost always working under tight deadlines, or playing catch-up from one setback or another. We have no time to waste, and lots of code to write. So let’s get right to it—right?
Wrong. If we dive too quickly into the depths of code construction, slavishly converting requirements to hundreds, thousands, or even tens of thousands of lines of code, we will end up with a total mess that is almost impossible to debug and maintain. Don’t respond to looming deadlines with panic; you are more likely to meet those deadlines if you do some careful planning.
We strongly encourage you to resist these time pressures and make sure to do the following before you start a new application, or even a specific program in an application:
- Construct test cases and test scripts before you write your code
You should determine how you want to verify a successful implementation before you write a single line of a program. By doing this, you are more likely to get the interface of your programs correct, and be able to thoroughly identify what it is your program needs to do.
- Establish clear rules for how developers will write the SQL statements in the application
In general, we recommend that individual developers not write a whole lot of SQL. Instead, those single-row queries and inserts and updates should be “hidden” behind prebuilt and thoroughly tested procedures and functions (this is called data encapsulation). These programs can be optimized, tested, and maintained much more effectively than SQL statements (many of them redundant) scattered throughout your code.
- Establish clear rules for how developers will handle exceptions in the application
Best of all, create a single error-handling package that hides all the details of how an error log is kept, determines how exceptions are raised and propagated up through nested blocks, avoids hardcoding of application-specific exceptions (those -20,NNN errors), and more. Make sure that all developers use this package and do not write their own complicated, time-consuming, and error-prone error-handling code.
- Use “stepwise refinement” (a.k.a. top-down design) to limit the complexity of the requirements you must deal with at any given time
If you use this approach, you will find that the executable sections of your modules are shorter and easier to understand, which makes your code easier to maintain and enhance over time. Local or nested modules play a key role in following this design principle.
These are just a few of the important things to keep in mind before you start writing all that code. Just remember: in the world of software development, haste not only makes waste, it virtually guarantees a generous offering of bugs and lost weekends.
Chances are, if you are a software professional, you are a fairly smart individual. You studied hard, you honed your skills, and now you make a darn good living writing code. You can solve almost any problem you are handed, and that makes you proud.
Unfortunately, your success can also make you egotistical, arrogant, and reluctant to seek out help when you are stumped. This dynamic is one of the most dangerous and destructive aspects of software development.
Software is written by human beings; it is important, therefore, to recognize that human psychology plays a key role in software development. The following is an example.
Joe, the senior developer in a team of six, has a problem with his program. He studies it for hours, with increasing frustration but cannot figure out the source of the bug. He wouldn’t think of asking any of his peers to help because they all have less experience then he does. Finally, though, he is at wits’ end and “gives up.” Sighing, he picks up his phone and touches an extension: “Sandra, could you come over here and take a look at my program? I’ve got a problem I can’t figure out.” Sandra stops by and, with the quickest glance at Joe’s program, points out what should have been obvious to him long ago. Hurray! The program is fixed, and Joe expresses gratitude, but in fact he is secretly embarrassed.
Thoughts like “Why didn’t I see that?” and “If I’d only spent another five minutes doing my own debugging I would have found it” run though Joe’s mind. This is understandable but also very thick-headed. The bottom line is that we are often unable to identify our own problems because we are too close to our own code. Sometimes, all we need is a fresh perspective, the relatively objective view of someone with nothing at stake. It has nothing to do with seniority, expertise, or competence.
We strongly suggest that you establish the following guidelines in your organization:
- Reward admissions of ignorance
Hiding what you don’t know about an application or its code is very dangerous. Develop a culture that welcomes questions and requests for help.
- Ask for help
If you cannot figure out the source of a bug in 30 minutes, immediately ask for help. You might even set up a “buddy system,” so that everyone is assigned a person who is expected to be asked for assistance. Don’t let yourself (or others in your group) go for hours banging your head against the wall in a fruitless search for answers.
- Set up a formal peer code review process
Don’t let any code go to QA or production without being read and critiqued (in a positive, constructive manner) by one or more other developers in your group.
We all tend to fall into ruts, in almost every aspect of our lives. People are creatures of habit: you learn to write code in one way; you assume certain limitations about a product; you turn aside possible solutions without serious examination because you just know it can’t be done. Developers become downright prejudiced about their own programs, and often not in positive ways. They are often overheard saying things like:
“It can’t run any faster than that; it’s a pig.”
“I can’t make it work the way the user wants; that’ll have to wait for the next version.”
“If I were using X or Y or Z product, it would be a breeze. But with this stuff, everything is a struggle.”
But the reality is that your program can almost always run a little faster. And the screen can, in fact, function just the way the user wants it to. And although each product has its limitations, strengths, and weaknesses, you should never have to wait for the next version. Isn’t it so much more satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses, and crafted a solution?
How do you do this? Break out of the confines of your hardened views and take a fresh look at the world (or maybe just your cubicle). Reassess the programming habits you’ve developed. Be creative—step away from the traditional methods, from the often limited and mechanical approaches constantly reinforced in our places of business.
Try something new: experiment with what may seem to be a radical departure from the norm. You will be surprised at how much you will learn and grow as a programmer and problem solver. Over the years, I have surprised myself over and over with what is really achievable when I stopped saying “You can’t do that!” and instead simply nodded quietly and murmured “Now, if I do it this way ... "
Get Oracle PL/SQL Programming, 4th Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
