1.4 PL/SQL Language Fundamentals

This section summarizes the fundamental components of the PL/SQL language: characters, identifiers, literals, delimiters, use of comments and pragmas, and construction of statements and blocks.

1.4.1 PL/SQL Character Set

The PL/SQL language is constructed from letters, digits, symbols, and whitespace, as defined in the following table:

A-Z, a-z

0-9

~!@#$%^&*(  )_-+=|[ ]{ }:;"'< >,.?/ ^

space, tab, newline, carriage return

Characters are grouped together into four lexical units: identifiers, literals, delimiters, and comments.

1.4.2 Identifiers

Identifiers are names for PL/SQL objects such as constants, variables, exceptions, procedures, cursors, and reserved words. Identifiers have the following characteristics:

• Can be up to 30 characters in length
• Cannot include whitespace (space, tab, carriage return)
• Must start with a letter
• Can include a dollar sign ($), an underscore ( _ ), and a pound sign (#)
• Are not case-sensitive

In addition, you must not use PL/SQL’s reserved words as identifiers. For a list of those words, see the table in the final section in this book, Section 1.20.

If you enclose an identifier within double quotes, then all but the first of these rules are ignored. For example, the following declaration is valid:

DECLARE
   "1 ^abc"  VARCHAR2(100);
BEGIN
   IF "1 ^abc" IS NULL THEN ...
END;

1.4.3 Boolean, Numeric, and String Literals

Literals are specific values not represented by identifiers. For example, TRUE, 3.14159, 6.63E-34, `Moby Dick’, and NULL are all literals of type Boolean, number, or string. There are no complex datatype literals as they are internal representations. Unlike the rest of PL/SQL, literals are case-sensitive. To embed single quotes within a string literal, place two single quotes next to each other. See the following table for examples:

'That''s Entertainment!'

'"The Raven"'

'TZ=''CDT6CST'''

''''

'''hello world'''

''''''

1.4.4 Datetime Interval Literals (Oracle9i)

The datetime interval datatypes are new with Oracle9i. These datatypes represent a chronological interval expressed in terms of either years and months or days, hours, minutes, seconds, and fractional seconds. Literals of these datatypes require the keyword INTERVAL followed by the literal and format string(s). The interval must go from a larger field to a smaller one, so YEAR TO MONTH is valid, but MONTH TO YEAR is not. See the following table for examples:

1.4.5 Delimiters

Delimiters are symbols with special meaning, such as := (assignment operator), || (concatenation operator), and ; (statement delimiter). The following table lists the PL/SQL delimiters:

1.4.6 Comments

Comments are sections of the code that exist to aid readability. The compiler ignores them.

A single-line comment begins with a double hyphen (—) and ends with a new line. The compiler ignores all characters between the — and the new line.

A multiline comment begins with slash asterisk (/*) and ends with asterisk slash (*/). The /* */ comment delimiters can also be used for a single-line comment. The following block demonstrates both kinds of comments:

DECLARE
   -- Two dashes comment out only the physical line.
   /* Everything is a comment until the compiler
      encounters the following symbol */

You cannot embed multiline comments within a multiline comment, so be careful during development if you comment out portions of code that include comments. The following code demonstrates this issue:

DECLARE
   /* Everything is a comment until the compiler
      /* This comment inside another WON'T work!*/
      encounters the following symbol. */

   /* Everything is a comment until the compiler
      -- This comment inside another WILL work!
      encounters the following symbol. */

1.4.7 Pragmas

The PRAGMA keyword is used to give instructions to the compiler. There are four types of pragmas in PL/SQL:

EXCEPTION_INIT

Tells the compiler to associate the specified error number with an identifier that has been declared an EXCEPTION in your current program or an accessible package. See Section 1.10 for more information on this pragma.

RESTRICT_REFERENCES

Tells the compiler the purity level of a packaged program. The purity level is the degree to which a program does not read/write database tables and/or package variables. See Section 1.15 for more information on this pragma.

SERIALLY_REUSABLE

Tells the runtime engine that package data should not persist between references. This is used to reduce per-user memory requirements when the package data is only needed for the duration of the call and not for the duration of the session. See Section 1.14 for more information on this pragma.

AUTONOMOUS_TRANSACTION

Starting in Oracle8i, tells the compiler that the function, procedure, top-level anonymous PL/SQL block, object method, or database trigger executes in its own transaction space. See Section 1.8 for more information on this pragma.

1.4.8 Statements

A PL/SQL program is composed of one or more logical statements. A statement is terminated by a semicolon delimiter. The physical end-of-line marker in a PL/SQL program is ignored by the compiler, except to terminate a single-line comment (initiated by the — symbol).

1.4.9 Block Structure

Each PL/SQL program is a block consisting of a standard set of elements, identified by keywords (see Figure 1-1). The block determines the scope of declared elements, and how exceptions are handled and propagated. A block can be anonymous or named. Named blocks include functions, procedures, packages, and triggers.

Figure 1-1. The PL/SQL block structure

Here is an example of an anonymous block:

DECLARE
   today DATE DEFAULT SYSDATE;
BEGIN
   -- Display the date.
   DBMS_OUTPUT.PUT_LINE ('Today is ' || today);
END;

Here is a named block that performs the same action:

CREATE OR REPLACE PROCEDURE show_the_date
IS
   today DATE DEFAULT SYSDATE;
BEGIN
   -- Display the date.
   DBMS_OUTPUT.PUT_LINE ('Today is ' || today);
END show_the_date;

The following table summarizes the sections of a PL/SQL block:

OOOOOOFFFBBBBBBRRR

where:

OOOOOO is the object number.

FFFF is the absolute or relative file number.

BBBBBBBB is the block number within the file.

RRRR is the row number within the block.

1.7 Loops

The LOOP construct allows you to execute a sequence of statements repeatedly. There are three kind of loops: simple (infinite), FOR, and WHILE.

You can use the EXIT statement to break out of LOOP and pass control to the statement following the END LOOP.

1.7.1 Simple Loop

LOOP
   executable_statement(s)
END LOOP;

The simple loop should contain an EXIT or EXIT WHEN unless you want it to execute infinitely. Use the simple loop when you want the body of the loop to execute at least once. For example:

LOOP
   FETCH company_cur INTO company_rec;
   EXIT WHEN company_cur%ROWCOUNT > 5 OR
      company_cur%NOTFOUND;
   process_company(company_cur);
END LOOP;

1.7.2 Numeric FOR Loop

FOR loop_index IN [REVERSE] lowest_number..highest_number
LOOP
   executable_statement(s)
END LOOP;

The PL/SQL runtime engine automatically declares the loop index a PLS_INTEGER variable; never declare a variable with that name yourself. The lowest_number and highest_number ranges can be variables, but are evaluated only once—on initial entry into the loop. The REVERSE keyword causes PL/SQL to start with the highest_number and decrement down to the lowest_number. For example, this code:

BEGIN
   FOR counter IN 1 .. 4
   LOOP
      DBMS_OUTPUT.PUT(counter);
   END LOOP;
   DBMS_OUTPUT.NEW_LINE;

   FOR counter IN REVERSE 1 .. 4
   LOOP
      DBMS_OUTPUT.PUT(counter);
   END LOOP;
   DBMS_OUTPUT.NEW_LINE;END;

yields the following output:

1234
4321

1.7.3 Cursor FOR Loop

FOR record_index IN [cursor_name | (SELECT statement)]
LOOP
   executable_statement(s)
END LOOP;

The PL/SQL runtime engine automatically declares the loop index a record of cursor_name%ROWTYPE; never declare a variable with that name yourself.

The cursor FOR loop automatically opens the cursor, fetches all rows identified by the cursor, and then closes the cursor. You can embed the SELECT statement directly in the cursor FOR loop. For example:

FOR emp_rec IN emp_cur
LOOP
   IF emp_rec.title = 'Oracle Programmer'
   THEN
      give_raise(emp_rec.emp_id,30)
   END IF;
END LOOP;

1.7.4 WHILE Loop

WHILE condition
LOOP
   executable_statement(s)
END LOOP;

Use the WHILE loop in cases where you may not want the loop body to execute even once:

WHILE NOT end_of_analysis
LOOP
   perform_analysis;
   get_next_record;
   IF analysis_cursor%NOTFOUND AND next_step IS NULL
   THEN
      end_of_analysis := TRUE;
   END IF;
END LOOP;

1.7.5 REPEAT UNTIL Loop Emulation

PL/SQL does not directly support a REPEAT UNTIL construct, but a modified simple loop can emulate one. The syntax for this emulated REPEAT UNTIL loop is:

LOOP
   executable_statement(s)
   EXIT WHEN Boolean_condition;
END LOOP;

Use the emulated REPEAT UNTIL loop when executing iterations indefinitely before conditionally terminating the loop.

1.7.6 EXIT Statement

EXIT [WHEN condition];

If you do not include a WHEN clause in the EXIT statement, it will terminate the loop unconditionally. Otherwise, the loop terminates only if the Boolean condition evaluates to TRUE. The EXIT statement is optional and can appear anywhere in the loop.

1.7.7 Loop Labels

Loops can be optionally labeled to improve readability and execution control, as we showed earlier in the discussion of the GOTO statement. The label must appear immediately in front of the statement that initiates the loop.

The following example demonstrates the use of loop labels to qualify variables within a loop and also to terminate nested and outer loops:

<<year_loop>>
FOR yearind IN 1 .. 20
LOOP
   <<month_loop>>
   LOOP
      ...
      IF year_loop.yearind > 10
      THEN
         EXIT year_loop;
      END IF;
   END LOOP month_loop;
END LOOP year_loop;

1.8 Database Interaction

PL/SQL is tightly integrated with the underlying SQL layer of the Oracle database. You can execute SQL statements (UPDATE, INSERT, DELETE, MERGE, and SELECT) directly in PL/SQL programs. You can also execute Data Definition Language (DDL) statements through the use of dynamic SQL. In addition, you can manage transactions with COMMIT, ROLLBACK, and other Data Control Language (DCL) statements.

1.8.1 Transaction Management

The Oracle RDBMS provides a transaction model based on a unit of work. The PL/SQL language supports most, but not all, of the database model for transactions (you cannot, for example, specify ROLLBACK FORCE). A transaction begins with the first change to data and ends with either a COMMIT or a ROLLBACK. Transactions are independent of PL/SQL blocks. Transactions can span multiple PL/SQL blocks, or there can be multiple transactions in a single PL/SQL block. The PL/SQL-supported transaction statements include COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, and LOCK TABLE, described in the following sections.

1.8.1.1 COMMIT

COMMIT [WORK] [COMMENT text];

COMMIT makes the database changes permanent and visible to other database sessions. The WORK keyword is optional and only aids readability—it is rarely used. The COMMENT text is optional and can be up to 50 characters in length. It is only germane to in-doubt distributed (two-phase commit) transactions. The database statement COMMIT FORCE, also for distributed transactions, is not supported in PL/SQL.

1.8.1.2 ROLLBACK

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name];

ROLLBACK undoes the changes made in the current transaction either to the beginning of the transaction or to a savepoint. A savepoint is a named processing point in a transaction, created with the SAVEPOINT statement. Rolling back to a savepoint is a partial rollback of a transaction, wiping out all changes (and savepoints) that occurred later than the named savepoint.

1.8.1.3 SAVEPOINT

SAVEPOINT savepoint_name;

SAVEPOINT establishes a savepoint in the current transaction. savepoint_name is an undeclared identifier—you do not declare it. More than one savepoint can be established within a transaction. If you reuse a savepoint name, that savepoint is moved to the later position and you will not be able to roll back to the initial savepoint position.

1.8.1.4 SET TRANSACTION

SET TRANSACTION READ ONLY;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION USE ROLLBACK SEGMENT rbseg_name;

SET TRANSACTION has three transaction control functions:

READ ONLY

Marks the beginning of a read-only transaction. This indicates to the RDBMS that a read-consistent view of the database is to be enforced for the transaction (the default is for the statement). This read-consistent view means that only changes committed before the transaction begins are visible for the duration of the transaction. The transaction is ended with either a COMMIT or a ROLLBACK. Only LOCK TABLE, SELECT, SELECT INTO, OPEN, FETCH, CLOSE, COMMIT, or ROLLBACK statements are permitted during a read-only transaction. Issuing other statements, such as INSERT or UPDATE, in a read-only transaction results in an ORA-1456 error.

ISOLATION LEVEL SERIALIZABLE

Similar to a READ ONLY transaction in that transaction-level read consistency is enforced instead of the default statement-level read consistency. Serializable transactions do allow changes to data, however.

USE ROLLBACK SEGMENT

Tells the RDBMS to use the specifically named rollback segment rbseg_name. This statement is useful when only one rollback segment is large, and a program knows that it needs to use the large rollback segment, such as during a month-end close operation. For example, if we know that our large rollback segment is named rbs_large, we can tell the database to use it by issuing the following statement before our first change to data:

SET TRANSACTION USE ROLLBACK SEGMENT rbs_large;

1.8.1.5 LOCK TABLE

LOCK TABLE table_list IN lock_mode MODE [NOWAIT];

This statement bypasses the implicit database row-level locks by explicitly locking one or more tables in the specified mode. The table_list is a comma-delimited list of tables. The lock_mode is one of the following: ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE, SHARE, SHARE ROW EXCLUSIVE, or EXCLUSIVE. The NOWAIT keyword specifies that the RDBMS should not wait for a lock to be released. If there is a lock when NOWAIT is specified, the RDBMS raises the exception “ORA-00054: resource busy and acquire with NOWAIT specified.” The default RDBMS locking behavior is to wait indefinitely.

1.8.2 Autonomous Transactions

Autonomous transactions, introduced in Oracle8i, execute within a block of code as separate transactions from the outer (main) transaction. Changes can be committed or rolled back in an autonomous transaction without committing or rolling back the main transaction. Changes committed in an autonomous transaction are visible to the main transaction, even though they occur after the start of the main transaction. Those changes committed in an autonomous transaction are visible to other transactions as well. The RDBMS suspends the main transaction while the autonomous transaction executes:

PROCEDURE main IS
BEGIN
   UPDATE ... -- Main transaction begins here.
   DELETE ...
   at_proc;   -- Call the autonomous transaction.
   SELECT ...
   INSERT ...
   COMMIT;    -- Main transaction ends here.
END;

PROCEDURE at_proc IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN         -- Main transaction suspends here.
   SELECT ...
   INSERT ... -- Autonomous transaction begins here.
   UPDATE ...
   DELETE ...
   COMMIT;    -- Autonomous transaction ends here.
END;          -- Main transaction resumes here.

So, changes made in the main transaction are not visible to the autonomous transaction, and if the main transaction holds any locks that the autonomous transaction waits for, a deadlock occurs. Using the NOWAIT option on UPDATE statements in autonomous transactions can help to minimize this kind of deadlock. Functions and procedures (local program, standalone, or packaged), database triggers, top-level anonymous PL/SQL blocks, and object methods can be declared autonomous via the compiler directive PRAGMA AUTONOMOUS_TRANSACTION. In addition, there must be a commit or a rollback at each exit point in the autonomous program.

OPEN cursor_name [(argument [,argument …])];

FETCH cursor_name INTO record_or_variable_list;

CLOSE cursor_name;

cursor_name%attribute

DECLARE
   caller_rec caller_pkg.caller_cur%ROWTYPE;
BEGIN
   OPEN caller_pkg.caller_cur;
   LOOP
      FETCH caller_pkg.caller_cur into caller_rec;
      EXIT WHEN caller_pkg.caller_cur%NOTFOUND
               OR
               caller_pkg.caller_cur%ROWCOUNT > 10;

      UPDATE call
         SET caller_id = caller_rec.caller_id
         WHERE call_timestamp < SYSDATE;
   END LOOP;
   CLOSE caller_pkg.caller_cur;
END;

BEGIN
   UPDATE activity SET last_accessed := SYSDATE
   WHERE UID = user_id;

   IF SQL%NOTFOUND THEN
      INSERT INTO activity_log (uid,last_accessed)
      VALUES (user_id,SYSDATE);
   END IF
END;

BEGIN
   UPDATE activity SET last_accessed := SYSDATE
   WHERE UID = user_id
   RETURNING last_accessed, cost_center
   INTO timestamp, chargeback_acct;

SELECT ...
  FROM ...
   FOR UPDATE [OF column_reference] [NOWAIT];

DECLARE
   CURSOR hounds_in_stock_cur IS
      SELECT pet.stock_no, pet.breeder, dog.size
        FROM dog_breeds dog ,inventory pet
       WHERE dog.breed = pet.breed
         AND dog.class = 'HOUND'
         FOR UPDATE OF pet.stock_no, pet.breeder;
BEGIN

[UPDATE | DELETE ] ...
   WHERE CURRENT OF cursor_name;

DECLARE
   CURSOR wip_cur IS
      SELECT acct_no, enter_date FROM wip
       WHERE enter_date < SYSDATE - 7
         FOR UPDATE;
BEGIN
   FOR wip_rec IN wip_cur
   LOOP
      INSERT INTO acct_log (acct_no, order_date)
         VALUES (wip_rec.acct_no, wip_rec.enter_
            date);
      DELETE FROM wip
         WHERE CURRENT OF wip_cur;
   END LOOP;
END;

EXECUTE IMMEDIATE sql_statement
  [INTO {variable [,variable …] | record}]
  [USING [IN | OUT | IN OUT] bind_argument
    [,[IN | OUT | IN OUT] bind_argument …] ]
  [{RETURNING | RETURN} INTO bind_argument   [,bind_argument]…];

EXECUTE IMMEDIATE 'TRUNCATE TABLE foo';
EXECUTE IMMEDIATE 'GRANT SELECT ON '|| tabname_v ||
   ' TO ' || grantee_list;

DECLARE
   TYPE cv_typ IS REF CURSOR;
   cv cv_typ;
   laccount_no NUMBER;
   lbalance NUMBER;
BEGIN
   OPEN cv FOR
      'SELECT account_no, balance
         FROM accounts
        WHERE balance < 500';
   LOOP
      FETCH cv INTO laccount_no, lbalance;
      EXIT WHEN cv%NOTFOUND;
      -- Process the row.
   END LOOP;
   CLOSE cv;
END;

EXECUTE IMMEDIATE 'INSERT INTO hr.regions
  (region_id, region_name) VALUES (:r_id, :r_name)'
  USING  id, name;

TYPE ref_cursor_name IS REF CURSOR
   [RETURN record_type];

DECLARE
   -- Create a cursor type based on the company's
      table.
   TYPE company_curtype IS REF CURSOR
      RETURN companies%ROWTYPE;

   -- Create the variable based on the REF CURSOR.
   company_cur company_curtype;

   -- And now the weak, general approach.
   TYPE any_curtype IS REF CURSOR;
   generic_curvar any_curtype;

OPEN cursor_name FOR SELECT_statement;

CURSOR (subquery)

DECLARE
TYPE refcursor IS REF CURSOR;
CURSOR order_cur IS
   SELECT o.order_date ,o.order_status
         ,CURSOR(SELECT p.translated_name
                       ,i.unit_price
                       ,i.quantity
                 FROM oe.order_items i
                     ,oe.product_descriptions p
                 WHERE i.product_id = p.product_id
                  AND  i.order_id = o.order_id)
   FROM oe.orders o
   WHERE order_date BETWEEN TO_DATE('01-Jan-03')
                        AND TO_DATE('31-Jan_03');
odate   oe.orders.order_date%TYPE;
ostatus oe.orders.order_status%TYPE;
od_cur  refcursor;
tname   oe.product_descriptions.translated_name%TYPE;
price   oe.order_items.unit_price%TYPE;
qty     oe.order_items.quantity%TYPE;
BEGIN
   OPEN order_cur;
   LOOP
      FETCH order_cur INTO odate, ostatus, od_cur;
      EXIT WHEN order_cur%NOTFOUND;
      LOOP
         FETCH od_cur INTO tname, price, qty;
         EXIT WHEN od_cur%NOTFOUND;
         DBMS_OUTPUT.PUT_LINE(odate||','||ostatus
             ||','||tname||','||price||','||qty);
      END LOOP;
   END LOOP;
   CLOSE order_cur;
END;

1.10 Exception Handling

PL/SQL allows developers to raise and handle errors (exceptions) in a very flexible and powerful way. Each PL/SQL block can have its own exception section in which exceptions can be trapped and handled (resolved or passed on to the enclosing block).

When an exception occurs (is raised) in a PL/SQL block, its execution section immediately terminates. Control is passed to the exception section.

Every exception in PL/SQL has an error number and error message; some exceptions also have names.

1.10.1 Declaring Exceptions

Some exceptions (see the following table) have been pre-defined by Oracle in the STANDARD package or other built-in packages, such as UTL_FILE. You can also declare your own exceptions as follows:

DECLARE
   exception_name EXCEPTION;

An exception can be declared only once in a block, but nested blocks can declare an exception with the same name as an outer block. If this multiple declaration occurs, scope takes precedence over name when handling the exception. The inner block’s declaration takes precedence over a global declaration.

When you declare your own exception, you must RAISE it explicitly. All declared exceptions have an error code of 1 and the error message “User-defined exception,” unless you use the EXCEPTION_INIT pragma.

You can associate an error number with a declared exception with the PRAGMA EXCEPTION_INIT statement using the following syntax:

DECLARE
   exception_name EXCEPTION;
   PRAGMA EXCEPTION_INIT (exception_name,
      error_number);

where error_number is a literal value (variable references are not allowed). This number can be an Oracle error, such as -1855, or an error in the user-definable -20000 to -20999 range.

1.10.2 Raising Exceptions

An exception can be raised in three ways:

• By the PL/SQL runtime engine
• By an explicit RAISE statement in your code
• By a call to the built-in function RAISE_APPLICATION_ERROR

The syntax for the RAISE statement is:

RAISE exception_name;

where exception_name is the name of an exception that you have declared, or an exception that is declared in the STANDARD package. If you use the RAISE statement inside an exception handler, you can omit the exception name to re-raise the current exception:

RAISE;

This syntax is not valid outside the exception section.

The RAISE_APPLICATION_ERROR built-in function has the following header:

RAISE_APPLICATION_ERROR (
   num BINARY_INTEGER,
   msg VARCHAR2,
   keeperrorstack BOOLEAN DEFAULT FALSE);

where num is the error number (an integer between -20999 and -20000), msg is the associated error message, and keeperrorstack controls the contents of the error stack.

1.10.3 Scope

The scope of an exception section is that portion of the code that is “covered” by the exception section. An exception handler will only handle or attempt to handle exceptions raised in the executable section of the PL/SQL block. Exceptions raised in the declaration or exception sections are automatically passed to the outer block. Any line or set of PL/SQL code can be placed inside its own block and given its own exception section. This allows you to limit the propagation of an exception.

1.10.4 Propagation

Exceptions raised in a PL/SQL block propagate to an outer block if they are unhandled or re-raised in the exception section. When an exception occurs, PL/SQL looks for an exception handler that checks for the exception (or is the WHEN OTHERS clause) in the current block. If a match is not found, then PL/SQL propagates the exception to the enclosing block or calling program. This propagation continues until the exception is handled or propagated out of the outermost block, back to the calling program. In this case, the exception is “unhandled” and (1) stops the calling program, and (2) causes an automatic rollback of any outstanding transactions.

Once an exception is handled, it will not propagate upward. If you want to trap an exception, display a meaningful error message, and have the exception propagate upward as an error, you must re-raise the exception. The RAISE statement can re-raise the current exception or raise a new exception, as shown here:

PROCEDURE delete_dept(deptno_in IN NUMBER)
DECLARE
   still_have_employees EXCEPTION
   PRAGMA EXCEPTION_INIT(still_have_employees.
      -2292)
BEGIN
DELETE FROM dept
WHERE deptno = deptno_in;
EXCEPTION
   WHEN still_have_employees
   THEN
      DBMS_OUTPUT.PUT_LINE
('Please delete employees in dept first');
   ROLLBACK;
   RAISE;  /* Re-raise the current exception. */
END;

1.10.4.1 WHEN OTHERS clause

Use the WHEN OTHERS clause in the exception handler as a catch-all to trap any exceptions that are not handled by specific WHEN clauses in the exception section. If present, this clause must be the last exception handler in the exception section. You specify this clause as follows:

EXCEPTION
   WHEN OTHERS
   THEN
      ...

1.10.4.2 SQLCODE and SQLERRM

SQLCODE and SQLERRM are built-in functions that provide the SQL error code and message for the current exception. Use these functions inside the exception section’s WHEN OTHERS clause to handle specific errors by number. The EXCEPTION_INIT pragma allows you to handle errors by name. For example, the following code:

CREATE TABLE err_test
   (widget_name   VARCHAR2(100)
   ,widget_count  NUMBER
   ,CONSTRAINT no_small_numbers CHECK
      (widget_count > 1000));
BEGIN
   INSERT INTO err_test (widget_name, widget_count)
   VALUES ('Athena',2);
EXCEPTION
   WHEN OTHERS THEN
   IF SQLCODE = -2290
      AND SQLERRM LIKE '%NO_SMALL_NUMBERS%'
   THEN
      DBMS_OUTPUT.PUT_LINE('widget_count is too
         small');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Exception not handled,'
         ||'SQLcode='||SQLCODE);
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
   END IF;
END;

produces this output:

widget_count is too small

The built-in package DBMS_UTILITY’s FORMAT_ERROR_STACK and FORMAT_CALL_STACK procedures can be used to capture the full error stack and call stack. See the book Oracle Built-in Packages for more information on DBMS_UTILITY.

1.10.4.3 Exceptions and DML

When an exception is raised in a PL/SQL block, it does not roll back your current transaction, even if the block itself issued an INSERT, UPDATE, or DELETE. You must issue your own ROLLBACK statement if you want to clean up your transaction as a result of the exception.

If your exception goes unhandled (propagates out of the outermost block), however, most host environments will then force an automatic, unqualified rollback of any outstanding changes in your session.

DECLARE
   -- Declare table-based record for company table.
   comp_rec  company%ROWTYPE

   CURSOR comp_summary_cur IS
      SELECT C.company_id,SUM(S.gross_sales) gross
        FROM company C ,sales S
       WHERE C.company_id = S.company_id;

   -- Declare a cursor-based record.
   comp_summary_rec  comp_summary_cur%ROWTYPE;

DECLARE
   TYPE name_rectype IS RECORD(
      prefix       VARCHAR2(15)
      ,first_name  VARCHAR2(30)
      ,middle_name VARCHAR2(30)
      ,sur_name    VARCHAR2(30)
      ,suffix      VARCHAR2(10) );

   TYPE employee_rectype IS RECORD (
      emp_id       NUMBER(10) NOT NULL
      ,mgr_id      NUMBER(10)
      ,dept_no     dept.deptno%TYPE
      ,title       VARCHAR2(20)
      ,name        empname_rectype
      ,hire_date   DATE := SYSDATE
      ,fresh_out   BOOLEAN );

   -- Declare a variable of this type.
   new_emp_rec employee_rectype;
BEGIN

record_name.field_name 

employee.first_name

BEGIN
   insurance_start_date := new_emp_rec.hire_date +
      30;
   new_emp_rec.fresh_out := FALSE;
   ...

shipto_address_rec := customer_address_rec

IF shipto_address_rec = customer_address_rec
THEN
   ...
END IF;

DECLARE
   -- Define a record.
   TYPE phone_rectype IS RECORD (
      area_code  VARCHAR2(3),
      exchange   VARCHAR2(3),
      phn_number VARCHAR2(4),
      extension  VARCHAR2(4));

   -- Define a record composed of records.
   TYPE contact_rectype IS RECORD (
      day_phone#  phone_rectype,
      eve_phone#  phone_rectype,
      cell_phone# phone_rectype);

-- Declare a variable for the nested record.
 auth_rep_info_rec contact_rectype;
BEGIN

1.12 Named Program Units

PL/SQL allows you to create a variety of named program units, or containers for code. These include:

Procedure

A program that executes one or more statements

Function

A program that returns a value

Package

A container for procedures, functions, and data structures

Trigger

A program that executes in response to database changes

Object type

Oracle’s version of an object-oriented class; object types can contain member procedures and functions

1.12.1 Procedures

Procedures are program units that execute one or more statements and can receive or return zero or more values through their parameter lists. The syntax of a procedure is:

CREATE [OR REPLACE] PROCEDURE name
   [ (parameter [,parameter]) ]
   [AUTHID { CURRENT_USER | DEFINER } ]
   [DETERMINISTIC]
{ IS | AS }
   declaration_section
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A procedure is called as a standalone executable PL/SQL statement:

apply_discount(new_company_id, 0.15);

1.12.2 Functions

Functions are program units that execute zero or more statements and return a value through the RETURN clause. Functions can also receive or return zero or more values through their parameter lists. The syntax of a function is:

CREATE [OR REPLACE] FUNCTION name
   [ (parameter [,parameter]) ]
   RETURN return_datatype
   [AUTHID { CURRENT_USER | DEFINER } ]
   [DETERMINISTIC]
   [PARALLEL_ENABLE]
   [PIPELINED]
   [AGGREGATE USING]
{ IS | AS }
   [declaration_section]
BEGIN
   executable_section
[EXCEPTION
   exception_section]
END [name];

A function must have at least one RETURN statement in the execution section. The RETURN clause in the function header specifies the datatype of the returned value.

See Section 1.12.3.9 for information on the keywords OR REPLACE, AUTHID, DETERMINISTIC, PARALLEL_ENABLE, PIPELINED, and AGGREGATE USING. See Section 1.12.3.11 for additional information on AUTHID.

A function can be called anywhere that an expression of the same type can be used. You can call a function:

• In an assignment statement:

  sales95 := tot_sales(1995,'C');

• To set a default value:

  DECLARE
     sales95 NUMBER DEFAULT tot_sales(1995,'C');
  BEGIN

• In a Boolean expression:

  IF tot_sales(1995,'C') > 10000
  THEN
  ...

• In a SQL statement:

  SELECT first_name ,surname
     FROM sellers
  WHERE tot_sales(1995,'C') > 1000;

• As an argument in another program unit’s parameter list.

Here, for example, max_discount is a programmer-defined function and SYSDATE is a built-in function:

apply_discount(company_id, max_discount(SYSDATE));

1.12.3 Parameters

Procedures, functions, and cursors may have a parameter list. This list contains one or more parameters that allow you to pass information back and forth between the sub-program and the calling program. Each parameter is defined by its name, datatype, mode, and optional default value. The syntax for a parameter is:

parameter_name [mode] [NOCOPY] datatype
   [ { := | DEFAULT } value]

1.12.3.1 Datatype

The datatype can be any PL/SQL or programmer-defined datatype, but cannot be constrained by a size (NUMBER is valid, NUMBER(10) is not valid). The actual size of the parameter is determined from the calling program or via a %TYPE constraint.

CREATE OR REPLACE PROCEDURE empid_to_name
(in_id           emp.emp_id%TYPE -- Compiles OK.
,out_last_name   VARCHAR2        -- Compiles OK.
,out_first_name  VARCHAR2(10)    -- Won't compile.
) IS
...

The lengths of out_last_name and out_first_name are determined by the calling program:

DECLARE
   surname     VARCHAR2(10);
   first_name  VARCHAR2(10);
BEGIN
   empid_to_name(10, surname, first_name);
END;

1.12.3.2 Mode

The mode of a parameter specifies whether the parameter can be read from or written to, as shown in the following table:

If the mode is not explicitly defined, it defaults to IN.

OUT parameters are not the same as IN OUT parameters. When running the called program, the runtime engine ignores (sets to NULL) any argument value you supply for an OUT parameter; it preserves the value provided for an IN OUT. If an exception is raised during execution of a procedure or function, assignments made to OUT or IN OUT parameters get rolled back unless the parameter includes the NOCOPY option.

The NOCOPY compiler hint for parameters makes the parameter a call by reference instead of a call by value. Normally, PL/SQL passes IN/OUT parameters by value—a copy of the parameter is created for the sub-program. When parameter items get large, as collections and objects do, the copy can eat memory and slow down processing. NOCOPY directs PL/SQL to pass the parameter by reference, using a pointer to the single copy of the parameter.

The disadvantage of NOCOPY is that when an exception is raised during execution of a program that has modified an OUT or IN OUT parameter, the changes to the actual parameters are not “rolled back” because the parameters were passed by reference instead of being copied.

1.12.3.3 Default values

IN parameters can be given default values. If an IN parameter has a default value, then you do not need to supply an argument for that parameter when you call the program unit. It automatically uses the default value. For example:

CREATE OR REPLACE PROCEDURE hire_employee
   (emp_id       IN VARCHAR2
   ,hire_date    IN DATE := SYSDATE
   ,company_id   IN NUMBER := 1
   )
IS
   ...

Here are some example calls to the above procedure:

-- Use two default values.
hire_employee(new_empno);
-- Use one default value.
hire_employee(new_empno,'12-Jan-1999');
-- Use non-trailing default value, named notation.
hire_employee(emp_id=>new_empno, comp_id=>12);

1.12.3.4 Parameter-passing notations

Formal parameters are the names that are declared in the header of a procedure or function. Actual parameters (arguments) are the values or expressions placed in the parameter list when a procedure or function is called. In the empid_to_name example shown earlier in Section 1.12.3.1, the actual parameters to the procedure are in_id, out_last_name, and out_first_name. The formal parameters used in the call to this procedure are 10, surname, and first_name.

PL/SQL lets you use either of two styles for passing arguments in parameter lists: positional notation or named notation.

Positional notation

The default. Each value in the list of arguments supplied in the program call is associated with the parameter in the corresponding position.

Named notation

Explicitly associates the argument value with its parameter by name (not position). When you use named notation, you can supply the arguments in any order and you can omit IN arguments that have default values.

The call to the empid_to_name procedure is shown here with both notations:

BEGIN
   -- Implicit positional notation.
   empid_to_name(10, surname, first_name);

   -- Explicit named notation.
   empid_to_name(in_id=>10
      ,out_last_name=>surname
      ,out_first_name=>first_name);
END;

You may combine positional and named notation, as long as positional arguments appear to the left of any named notation arguments; for example:

empid_to_name(10, surname, out_first_name => first_name);

When calling stored functions from SQL, named notation is not supported.

1.12.3.5 Local programs

A local program is a procedure or function that is defined in the declaration section of a PL/SQL block. The declaration of a local program must appear at the end of the declaration section, after the declarations of any types, records, cursors, variables, and exceptions. A program defined in a declaration section may only be referenced within that block’s executable and exception sections. It is not defined outside that block.

The following program defines a local procedure and function:

PROCEDURE track_revenue
IS
   l_total NUMBER;

   PROCEDURE calc_total (year_in IN INTEGER) IS
   BEGIN
      calculations here ...
   END;

   FUNCTION below_minimum (comp_id IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      ...
   END;
BEGIN
   ...main procedure logic here
END;

Local programs may be overloaded with the same restrictions as overloaded packaged programs.

1.12.3.6 Program overloading

PL/SQL allows you to define two or more programs with the same name within any declaration section, including a package specification or body. This is called overloading. If two or more programs have the same name, they must be different in some other way so that the compiler can determine which program should be used.

Here is an example of overloaded programs in a built-in package specification:

PACKAGE DBMS_OUTPUT
IS
   PROCEDURE PUT_LINE (a VARCHAR2);
   PROCEDURE PUT_LINE (a NUMBER);
   PROCEDURE PUT_LINE (a DATE);
END;

Each PUT_LINE procedure is identical, except for the datatype of the parameter. That is enough difference for the compiler.

To overload programs successfully, one or more of the following conditions must be true:

• Parameters must differ by datatype family (number, character, datetime, or Boolean).
• The program type must be different (you can overload a function and a procedure of the same name and identical parameter list).
• The numbers of parameters must be different.

You cannot overload programs if:

• Only the datatypes of the functions’ RETURN clauses are different.
• Parameter datatypes are within the same family (CHAR and VARCHAR2, NUMBER and INTEGER, etc.).
• Only the modes of the parameters are different.

1.12.3.7 Forward declarations

Programs must be declared before they can be used. PL/SQL supports mutual recursion , in which program A calls program B, whereupon program B calls program A. To implement this mutual recursion, you must use a forward declaration of the programs. This technique declares a program in advance of the program definition, thus making it available for other programs to use. The forward declaration is the program header up to the IS/AS keyword:

PROCEDURE perform_calc(year_in IN NUMBER)
IS
   /* Forward declaration for total_cost
      function. */
   FUNCTION total_cost (...) RETURN NUMBER;

   /* The net_profit function can now use
      total_cost. */
   FUNCTION net_profit(...) RETURN NUMBER
   IS
   BEGIN
      RETURN total_sales(...) - total_cost(...);
   END;

   /* The Total_cost function calls net_profit. */
   FUNCTION total_cost (...) RETURN NUMBER
   IS
   BEGIN
      IF net_profit(...) < 0
      THEN
         RETURN 0;
         ELSE
         RETURN...;
      END IF;
   END;
BEGIN /* procedure perform_calc */
   ...
END perform_calc;

1.12.3.8 Table functions

Table functions take a collection or REF CURSOR (set of rows) as input and return a collection of records (set of rows) as output. The PIPE ROW command is used to identify the input and output streams. This streamlined nature allows you to pipeline table functions together, eliminating the need to stage tables between transformations. Table functions typically appear in the FROM clause of SQL statements. For example:

CREATE FUNCTION pet_family
(dad_in IN pet_t, mom_in IN pet_t)
RETURN pet_nt PIPELINED IS
   l_count PLS_INTEGER;
   retval pet_nt := pet_nt ( );

BEGIN
   PIPE ROW (dad_in);  -- identify streaming input
   PIPE ROW (mom_in);  -- identify streaming input

   IF mom_in.breed = 'RABBIT' THEN l_count := 12;
   ELSIF mom_in.breed = 'DOG' THEN l_count := 4;
   ELSIF mom_in.breed = 'KANGAROO' THEN l_count := 1;
   END IF;

   FOR indx IN 1 .. l_count
   LOOP
      -- stream the results into the ouput pipeline
      PIPE ROW (pet_t ('BABY' || indx, mom_in.breed
                       ,SYSDATE));
   END LOOP;

   RETURN;

 END;

1.12.3.9 Compiling stored PL/SQL programs

The following keywords are available when creating Oracle9i stored programs:

OR REPLACE

Used to rebuild an existing program unit, preserving privileges granted on it.

AUTHID

Defines whether the program will execute with the privileges of, and resolve names like, the object owner (DEFINER), or as the user executing the function (CURRENT_USER). Prior to Oracle8i, only the built-in packages DBMS_SQL and DBMS_UTILITY executed as CURRENT_USER. The default AUTHID is DEFINER.

DETERMINISTIC

Required for function-based indexes. A function is DETERMINISTIC if it always returns the same value when called with the same parameters. Deterministic functions do not meaningfully reference package variables or the database. The built-in INITCAP is deterministic, but SYSDATE is not.

PARALLEL_ENABLED [(PARTITION in_parm BY {ANY HASH | RANGE}) ]

Tells the optimizer that a function is safe for parallel execution. The PARTITION BY clause is only available to functions that have a REF CURSOR IN parameter. This clause is used with table functions and tells the optimizer how the input can be partitioned.

PIPELINED (Oracle9i)

Used with table functions. Specifies that the results of this table function should be returned iteratively via the PIPE ROW command. A pipelined function can start to return data as it is generated instead of all at once after processing is complete.

AGGREGATE USING (Oracle9i)

Required for aggregate functions. Tells Oracle that the function evaluates a group of rows and returns a single result. For example, the built-in function AVG is an aggregate function.

1.12.3.10 Native compilation of PL/SQL (Oracle9i)

With Oracle9i you can speed up many of your PL/SQL programs by compiling the stored programs natively. Oracle will translate your PL/SQL program into C code and compile it into a shared library (DLL on NT). You must have a supported C compiler on your database server machine to use native compilation. To compile natively, you must follow these steps:

• Edit the makefile, spnc_makefile.mk, which you should find in the $ORACLE_HOME/plsql subdirectory.
• Set the initialization parameter PLSQL_COMPILER_FLAGS = `NATIVE’. Individual developers may alter the value of PLSQL_COMPILER_FLAGS using the ALTER SESSION statement.
• The following parameters many also need to be set: PLSQL_NATIVE_C_COMPILER, PLSQL_NATIVE_LINKER, PLSQL_NATIVE_LIBRARY_DIR, PLSQL_NATIVE_MAKE_UTILITY, and PLSQL_NATIVE_MAKE_FILE_NAME. The DBA can set these parameters in the Oracle initialization file or using an ALTER SYSTEM statement.
• Create or replace your stored programs.
• Verify the native compilation by querying the data dictionary view USER_STORED_SETTINGS and also by locating the shared library or DLL in the database server’s file system.

1.12.3.11 Privileges and stored PL/SQL

Stored SQL supports two models for addressing privileges at runtime. The default is definer rights, which tells Oracle that the privileges of the owner or definer of the program should be used. With the definer rights model, the owner of the program must have the required privileges granted directly to him—he cannot inherit the privileges from a role.

With invoker rights, the user who executes the program does so using his own privileges. Anonymous PL/SQL blocks always execute with invoker rights. To create a program that uses the invoker rights model, include the keywords AUTHID CURRENT_USER in your program’s declaration.

1.13 Triggers

Triggers are programs that execute in response to changes in table data or certain database events. There is a predefined set of events that can be “hooked” with a trigger, enabling you to integrate your own processing with that of the database. A triggering event fires or executes the trigger.

There are three types of triggering events:

• DML events fire when an INSERT, UPDATE, or DELETE statement executes.
• DDL events fire when a CREATE, ALTER, or DROP statement executes.
• Database events fire when one of the predefined database-level events occurs.

Complete lists of these events are included in later sections.

1.13.1 Creating Triggers

The syntax for creating a trigger on a DML event is:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } trigger_event
   ON {table_or_view_reference |
     NESTED TABLE nested_table_column OF view}
     [REFERENCING [OLD AS old] [NEW AS new]
       [PARENT AS parent]]
[FOR EACH ROW ][WHEN trigger_condition]
trigger_body;

The syntax for creating a trigger on a DDL or database event is:

CREATE [OR REPLACE] TRIGGER trigger_name
{ BEFORE | AFTER } trigger_event
   ON [ DATABASE | schema ]
 [WHEN trigger_condition]
trigger_body;

Trigger events are listed in the following table:

Triggers can fire BEFORE or AFTER the triggering event. AFTER data triggers are slightly more efficient than BEFORE triggers.

The REFERENCING clause is allowed only for the data events INSERT, UPDATE, and DELETE. It lets you give a non-default name to the old and new pseudo-records. These pseudo-records give the program visibility to the pre- and post-change values in row-level triggers. These records are defined like %ROWTYPE records, except that columns of type LONG or LONG RAW cannot be referenced. They are prefixed with a colon in the trigger body, and referenced with dot notation. Unlike other records, these fields can only be assigned individually—aggregate assignment is not allowed. All old fields are NULL within INSERT triggers, and all new fields are NULL within DELETE triggers.

FOR EACH ROW defines the trigger to be a row-level trigger. Row-level triggers fire once for each row affected. The default is a statement-level trigger, which fires only once for each triggering statement.

The WHEN trigger_condition specifies the conditions that must be met for the trigger to fire. Stored functions and object methods are not allowed in the trigger condition.

The trigger body is a standard PL/SQL block. For example:

CREATE OR REPLACE TRIGGER add_tstamp
   BEFORE INSERT ON emp
   REFERENCING NEW as new_row
   FOR EACH ROW
   BEGIN
      -- Automatically timestamp the entry.
      SELECT CURRENT_TIMESTAMP
      INTO :new_row.entry_timestamp
      FROM dual;
END add_tstamp;

Triggers are enabled on creation, and can be disabled (so they do not fire) with an ALTER statement, issued with the following syntax:

ALTER TRIGGER trigger_name { ENABLE | DISABLE };

ALTER TABLE table_name { ENABLE | DISABLE } ALL
   TRIGGERS;

1.13.2 Trigger Predicates

When using a single trigger for multiple events, use the trigger predicates INSERTING, UPDATING, and DELETING in the trigger condition to identify the triggering event, as shown in this example:

CREATE OR REPLACE TRIGGER emp_log_t
   AFTER INSERT OR UPDATE OR DELETE ON emp
   FOR EACH ROW
DECLARE
   dmltype  CHAR(1);
BEGIN
   IF INSERTING THEN
      dmltype := 'I';
      INSERT INTO emp_log (emp_no, who, operation)
         VALUES (:new.empno, USER, dmltype);
   ELSIF UPDATING  THEN
      dmltype := 'U';
      INSERT INTO emp_log (emp_no, who, operation)
         VALUES (:new.empno, USER, dmltype);
   END IF;
END;

1.13.3 DML Events

The DML events include INSERT, UPDATE, and DELETE statements on a table or view. Triggers on these events can be statement-level triggers (table only) or row-level triggers and can fire BEFORE or AFTER the triggering event. BEFORE triggers can modify the data in affected rows, but perform an additional logical read. AFTER triggers do not perform this additional logical read, and therefore perform slightly better, but are not able to change the :new values. AFTER triggers are thus better suited for data validation functionality. Triggers cannot be created on SYS-owned objects. The order in which these triggers fire, if present, is as follows:

BEFORE statement-level trigger

For each row affected by the statement:

BEFORE row-level trigger

The triggering statement

AFTER row-level trigger

AFTER statement-level trigger

1.13.4 DDL Events

The DDL events are CREATE, ALTER, and DROP. These triggers fire whenever the respective DDL statement is executed. DDL triggers can apply to either a single schema or the entire database.

1.13.5 Database Events

The database events are SERVERERROR, LOGON, LOGOFF, STARTUP, and SHUTDOWN. Only BEFORE triggers are allowed for LOGOFF and SHUTDOWN events. Only AFTER triggers are allowed for LOGON, STARTUP, and SERVERERROR events. A SHUTDOWN trigger will fire on a SHUTDOWN NORMAL and a SHUTDOWN IMMEDIATE, but not on a SHUTDOWN ABORT.

CREATE [OR REPLACE] PACKAGE package_name
[ AUTHID { CURRENT_USER | DEFINER } ]
{ IS | AS }

   [definitions of public TYPEs
   ,declarations of public variables, types, and
      objects
   ,declarations of exceptions
   ,pragmas
   ,declarations of cursors, procedures, and
      functions
   ,headers of procedures and functions]

END [package_name];

CREATE [OR REPLACE] PACKAGE BODY package_name
   { IS | AS }

   [definitions of private TYPEs
   ,declarations of private variables, types, and
      objects
   ,full definitions of cursors
   ,full definitions of proced