Oracle PL/SQL note

Oralce PL/SQL
11g new features:
Compound Triggers: define statement triggers and row triggers at the same time
Ordered Triggers: specify "follows ..." to define the order (dependency)
disabled triggers
virtual column
result cache
continue support
straight sequence

The names for exceptions can be referenced in only two ways:
In a RAISE statement in the execution section of the program (to raise the exception),
as in:
RAISE invalid_company_id;
In the WHEN clauses of the exception section (to handle the raised exception), as
in:
WHEN invalid_company_id THEN

You can raise an exception of your own or a system exception. The RAISE
statement can take one of three forms:
RAISE exception_name;
RAISE package_name.exception_name;
RAISE; --Use this form when you want to re-raise (or propagate out) the same exception from within an exception handler
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- Use common package to record all the "context" information,
-- such as error code, program name, etc.
errlog.putline (company_id_in);
-- And now propagate NO_DATA_FOUND unhandled to the enclosing block.
RAISE;

The advantage to using RAISE_APPLICATION_ERROR instead of RAISE (which can
also raise an application-specific, explicitly declared exception) is that you can associate
an error message with the exception.

When this procedure is run, execution of the current PL/SQL block halts immediately,
and any changes made to OUT or IN OUT arguments (if present and without the
NOCOPY hint) will be reversed. Changes made to global data structures, such as
packaged variables, and to database objects (by executing an INSERT, UPDATE,
MERGE, or DELETE) will not be rolled back. You must execute an explicit ROLLBACK
to reverse the effect of DML operations.
The maximum length string that SQLERRM will return is 512 bytes (in some earlier
versions of Oracle, only 255 bytes). Because of this restriction, Oracle Corporation
recommends that you instead call DBMS_UTILITY.FORMAT_ERROR_STACK
to ensure that you see the full error message string (this built-in will not truncate
until 2,000 bytes).
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE --see the error line number
You should call the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function in
your exception handler.

In the PL/SQL language, errors of any kind are treated as exceptions—situations that
should not occur—in your program. When an error occurs in PL/SQL, whether it’s a system error or an application error,
an exception is raised. The processing in the current PL/SQL block’s execution section
halts, and control is transferred to the separate exception section of the current block,
if one exists, to handle the exception. You cannot return to that block after you finish
handling the exception. Instead, control is passed to the enclosing block, if any.
There are, in general, two types of exceptions:
System exception
An exception that is defined by Oracle and is usually raised by the PL/SQL runtime
engine when it detects an error condition. Some system exceptions have names,
such as NO_DATA_FOUND, while many others simply have numbers and
descriptions.
Programmer-defined exception
An exception that is defined by the programmer and is therefore specific to the
application at hand. You can associate exception names with specific Oracle errors
using the EXCEPTION_INIT pragma (a compiler directive, requesting a specific
behavior), or you can assign a number and description to that error using
RAISE_APPLICATION_ERROR.

Raise
Stops execution of the current PL/SQL block by notifying the runtime engine of
an error. The database itself can raise exceptions, or your own code can raise an
exception with either the RAISE or RAISE_APPLICATION_ERROR command.
Unhandled exception
An exception is said to go “unhandled” when it propagates without being handled
out of the outermost PL/SQL block. Control then passes back to the host execution
environment, at which point that environment/program determines how to respond
to the exception (roll back the transaction, display an error, ignore it, etc.).
Un-named or anonymous exception
An exception that has an error code and a description associated with it, but does
not have a name that can be used in a RAISE statement or in an exception handler
WHEN clause.
Named exception
An exception that has been given a name, either by Oracle in one of its built-in
packages or by a developer. You can also associate a name with this exception
through the use of the EXCEPTION_INIT pragma, or leave it defined only by its
name (which can be used to both raise and handle the exception).
You declare an exception by listing the name of the exception you want
to raise in your program followed by the keyword EXCEPTION:
exception_name EXCEPTION;



Associative arrays:
like hash table
Nested tables:
unbounded
become sparse through deletions
no inherent order to the elements
PL/SQL and database
varrays:
must specify the max number of elements
never sparse
element order is preserved
PL/SQL and database