PL/SQL stuff
Native compilation
Some new stuff has been introduced to PL/SQL native compilation with Oracle 11g. As one might guess, native compiled code runs faster than interpreted code; but before Oracle 11g this required some additional preconditions, as e.g. a separate compiler. Not that is built-in.
Though native compilation takes a little longer to complete, code is usually compiled once and then executed many times, so the improvement in execution time very soon makes up for additional time spent at compiling. So how is it achieved?
ALTER SESSION SET plsql_code_type=native;
All PL/SQL compiled after issuing this statement will be native, easy to guess. To revert back:
ALTER SESSION SET plsql_code_type=interpreted;
Just need it temporarily – say, to compile a single package?
ALTER PACKAGE mypackage
COMPILE BODY
PLSQL_CODE_TYPE=native
PLSQL_OPTIMIZE_LEVEL=2
REUSE SETTINGS;
Optimization level
In above statement I also used the parameter PLSQL_OPTIMIZE_LEVEL
.
This was introduced with Oracle 10g (levels 0, 1, 2) and extended with Oracle
11g (level 3). And here comes what those levels basically mean:
- no optimizations at all
- elementary optimizations only (remove irrelevant code pieces, such as unused variables)
- standard optimizations (some code refactoring and advanced optimization)
- high optimizations (additionally to level 2, all called functions will be inlined; bigger code but faster execution)
The default optimization level can be set system-wide or for the current session:
ALTER <SESSION | SYSTEM> SET plsql_optimize_level=3;
UPSERT
Sometimes one cannot use MERGE
for several reasons. But always first figure
out whether to update using a SELECT
would mean to always have two queries
(first the SELECT
, and then either an UPDATE
or an INSERT
). Wouldn't it
be smart to save some overhead for better performance, introducing an "UPSERT"?
UPDATE … SET … WHERE …; -- pretend it is there and just needs to be updated
IF SQL%NOTFOUND THEN
INSERT INTO … VALUES (…);
END IF;
Cursor Attributes
Currently (Oracle 11gR2) there still seem to be but four of them:
SQL%ISOPEN
- whether the cursor is currently opened (
OPEN CURSOR mycursor
) SQL%FOUND
- whether the cursor contains another row
SQL%NOTFOUND
- whether the cursor contains no more rows
SQL%ROWCOUNT
- how many rows already have been processed (do not confuse this with how many rows the cursor matches – such attribute is still missing)
Exceptions
The only one known by almost all developers, and partially extensively misused:
WHEN OTHERS THEN NULL;
which simply suppresses all errors. But for useful error handling, PL/SQL already
has some fine exceptions defined:
Predefined Exceptions
NO_DATA_FOUND
- Speaks for itself, and is raised e.g. by some
SELECT … INTO
TOO_MANY_ROWS
- Just the opposite: e.g. some
SELECT … INTO
where theSELECT
returns more than one row, but theINTO
variable is a scalar INVALID_CURSOR
- Tried to close a cursor that was not open?
ZERO_DIVIDE
- "Do not want to share with nobody"? Cannot divide by Zero (x/0)
DUP_VAL_ON_INDEX
- attempted to store duplicate values in a column that is constrained by a unique index
OTHERS
- Everything else.
The OTHERS
exception is not primary intended for above mentioned THEN NULL
,
but the developer here can catch errors without any special pre-defined
exception, e.g. using the SQLERRNO
and SQLERRM
variables:
WHEN OTHERS THEN
mypackage.logger('…',SQLERRNO,SQLERRM);
RAISE;
or:
WHEN OTHERS THEN
IF SQLERRNO = … THEN
-- handle specific error here
END IF;
User Defined Exceptions
It is also possible to define some exceptions:
PROCEDURE myproc IS
e_myException EXCEPTION;
BEGIN
…
EXCEPTION
WHEN e_myException THEN …
END myproc;