A PL/SQL block consists of up to four parts, the header, declaration section, execution section and the exception section. When an exception is raised within the execution section of the PL/SQL block, control passes to the exception section. Pl/SQL will then scan the exception section to see if the exception is handled.

The exception is handled is there is an exception that is named in a WHEN clause that matches the exception raised. If a match is made then the executable statements associated with the exception are executed. If no match is found but there exists a WHEN OTHERS exception handler then the executable statements associated with this exception are executed.

A customer asked that we check out his intranet site, which was used by the company's employees and customers. This was part of a larger security review, and though we'd not actually used SQL injection to penetrate a network before, we were pretty familiar with the general concepts. We were completely successful in this engagement, and wanted to recount the steps taken as an illustration.

"SQL Injection" is subset of the an unverified/unsanitized user input vulnerability ("buffer overflows" are a different subset), and the idea is to convince the application to run SQL code that was not intended. If the application is creating SQL strings naively on the fly and then running them, it's straightforward to create some real surprises.

We'll note that this was a somewhat winding road with more than one wrong turn, and others with more experience will certainly have different -- and better -- approaches. But the fact that we were successful does suggest that we were not entirely misguided.

In Oracle/PLSQL, the NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

The syntax for the NVL2 function is:

NVL2( string1, value_if_NOT_null, value_if_null )

string1 is the string to test for a null value.

value_if_NOT_null is the value returned if string1 is not null.

value_if_null is the value returned if string1 is null.

Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer.

Oracle has a standard set of exceptions already named as follows:

PL/SQL uses cursors for all database information access statements. The language supports the use of both implicit and explicit cursors. Implicit cursors are those established for which explicit cursors are not declared. You must use explicit cursors or cursor FOR loops in all queries that return multiple rows. You define cursors in the variable definition area of PL/SQL subprograms using the CURSOR name IS statement, as shown :

CURSOR c_line_item IS

To use a cursor for manipulating data, you must use the statement OPEN name to execute the query and identify all rows that meet the select criteria. Subsequent retrieval of rows is accomplished with the FETCH statement. Once all information is processed, the CLOSE statement terminates all activity associated with the opened cursor.