|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
Questions are a burden to others, answers a prison to oneself. —The Prisoner
This appendix contains some frequently asked questions about PL/SQL, and their answers. Where appropriate, the answers link to detailed explanations elsewhere in this book.
Another good source of questions and answers about PL/SQL is the Web site
http://asktom.oracle.com/. A good source of examples that illustrate the detailed workings of PL/SQL is the PL/SQL area of the Oracle Technology Network site at
When you imbed an
SELECT SQL statement directly in your PL/SQL code, PL/SQL turns the variables in the
VALUES clauses into bind variables automatically. Oracle can reuse these SQL statement each time the same code is executed. To run similar statements with different variable values, you can save parsing overhead by calling a stored procedure that accepts parameters, then issues the statements with the parameters substituted in the right places.
You do need to specify bind variables with dynamic SQL, in clauses like
VALUES where you normally use variables. Instead of concatenating literals and variable values into a single string, replace the variables with the names of bind variables (prefixed by a colon) and specify the corresponding PL/SQL variables with the
USING clause. Using the
USING clause, instead of concatenating the variables into the string, reduces parsing overhead and lets Oracle reuse the SQL statements.
When building up a single SQL statement in a string, do not include any semicolon at the end (inside the quotation marks).
When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block. You will have a semicolon right before the end of the string literal, and another right after the closing single quotation mark.
You can search for regular expressions using the SQL operator
You can test or manipulate strings using the built-in functions
Oracle's regular expression features use characters like '.', '*', '^', and '$' that you might be familiar with from UNIX or Perl programming. For multi-language programming, there are also extensions such as '[:lower:]' to match a lowercase letter, instead of '[a-z]' which does not match lowercase accented letters.
By default, you put an exception handler at the end of a subprogram to handle exceptions that are raised anywhere inside the subprogram. To continue executing from the spot where an exception happens, enclose the code that might raise an exception inside another
BEGIN-END block with its own exception handler. For example, you might put separate
BEGIN-END blocks around groups of SQL statements that might raise
NO_DATA_FOUND, or around arithmetic operations that might raise
DIVIDE_BY_ZERO. By putting a
BEGIN-END block with an exception handler inside a loop, you can continue executing the loop even if some loop iterations raise exceptions.
The PL/SQL term for these things is "object types". To do object-oriented programming, you use a mix of SQL and PL/SQL. You create the types themselves and the tables to hold them using SQL. You write the bodies of the methods in PL/SQL, and you can also manipulate tables of objects and call object methods through PL/SQL.
The best source of information for object-oriented programming with PL/SQL is the book Oracle Database Application Developer's Guide - Object-Relational Features.
PL/SQL lets you issue a query and return a result set using a cursor variable, also known as a
REF CURSOR. See "Using Cursor Variables (REF CURSORs)".
Dot notation is used for identifying record fields, object attributes, and things inside packages or other schemas. When you combine these things, you might need to use expressions with multiple levels of dots, where it isn't always clear what each dot refers to. Here are some of the combinations.
schema_name.table_name schema_name.procedure_name() schema_name.type_name.member_name()
You can create object types by issuing the SQL statement
CREATE TYPE using dynamic SQL (the
EXECUTE IMMEDIATE statement).
You can use objects to group related values, as you would do with records, with the added advantage that you can store objects directly in the database. You can also create varrays, nested tables, and associative arrays of objects.
You can write PL/SQL procedures and functions that accept objects as parameters. You can pass objects as parameters to these procedures and functions. You can also return objects from functions.
You can write member procedures and functions for an object type in PL/SQL.
You can create overloaded PL/SQL procedures and functions by defining different versions that accept different subtypes of the same supertypes as parameters.
You can write a PL/SQL application in a completely object-oriented way.
You can write a PL/SQL application in a mostly procedural way, using objects and object types to work around limitations on PL/SQL variables and parameters.
There are several ways to create different kinds of PL/SQL procedures and functions:
CREATE PROCEDURE and
CREATE FUNCTION statements in SQL.
CREATE TRIGGER statement in SQL.
CREATE PACKAGE and
CREATE PACKAGE BODY statements in SQL. The package body can contain both procedures and functions.
CREATE TYPE and
CREATE TYPE BODY statements in SQL. The type body can contain both procedures and functions.
Tip: When using these SQL statements, you might find the
CREATE OR REPLACE forms more convenient to allow frequent updates without having to drop the original versions.
PROCEDURE procedure_name IS... or
FUNCTION function_name RETURN return_type IS... within the
DECLARE section of an anonymous block. These procedures and functions only exist for the life of the anonymous block, and are only visible within the block. This technique makes sense if you have a long-running or extensive anonymous block.
PROCEDURE procedure_name IS... or
FUNCTION function_name RETURN return_type IS... within the declarative section of another procedure or function. These procedures and functions are only visible within the outer procedure, so you can use this technique to avoid cluttering up the namespace and prevent unwanted calls from other subprograms.
Most PL/SQL I/O is through SQL statements, to store data in database tables or query those tables.
All other PL/SQL I/O is done through APIs that interact with other programs. For example, the
DBMS_OUTPUT package has procedures such as
PUT_LINE. To see the result outside of PL/SQL requires another program, such as SQL*Plus, to read and display the data passed to
DBMS_OUTPUT. (SQL*Plus does not display
DBMS_OUTPUT data unless you issue the command
SET SERVEROUTPUT ON first.)
Other PL/SQL APIs for doing I/O are
HTP (for displaying output on a web page),
DBMS_PIPE (for passing information back and forth between PL/SQL and operating-system commands),
UTL_FILE (for reading and writing operating-system files),
UTL_HTTP (for communicating with web servers),
UTL_SMTP (for communicating with mail servers), and
TEXT_IO (for displaying text from Oracle Forms).
Although some of these APIs can accept input as well as output, there is no built-in language facility for accepting data directly from the keyboard. For that, you can use the
ACCEPT commands in SQL*Plus:
You may be familiar with workarounds that allow a query to match
VARCHAR2 values regardless of uppercase and lowercase. For example, you can use the
LOWER() function on the column value, and build a function-based index on the column. Or you can add another column to the table, with a trigger to store an all-uppercase or all-lowercase copy of another column value, and then query this new column.
Now you can make queries case-insensitive (or even accent-insensitive) without any code changes at all. Add
_CI to the usual value of the
NLS_SORT initialization parameter, and queries are case-insensitive. Add
_CI to the usual value of the
NLS_SORT parameter, and queries are accent-insensitive.