ORACLE PL SQL

Table of Contents To start Name resolution Containers Nested /Inner Execution privilege Types Primitive Compile write to fs console Exception OUT Parameter feeding Manual handling Clean one dbms_utility.format_error_backtrace Records Collections BULK COLLECT FORALL Advanced keywords Transaction Dynamic SQL Bind Optimizing profiler hierchical profiler Misc Run-time reflection Currently executing code Call stack Functional programming

To start

First step

have a look on some reference An excellent guide (alas, in French)
be aware that PL/SQL being procedural does NOT trying to write clean code, see samples from Steve Feuerstein

Name resolution

Beware of shadowing between:

SQL column name and PL variable: "if a SQL statement references a name that belongs to both a column and either a local variable or formal parameter, then the column name takes precedence";

Named blocks case Full rules

Containers

Nested /Inner

Inner component Beware:

child has access to parent variables without passing them as parameter (side-effect risk)
they can't be used in SQL

Execution privilege

All executable PL/SQL objects (function and procedures):

inherit the executing user role for standard objects;
does not inherit the executing user role for system objects (eg. dba_tables).

Execution privilege (AUTHID):

DEFINER (default): Definer's rights (the user who compiled the package) ;
CURRENT_USER: Invoker's rights.

Invoker's right is used to make the same object executable by different users:

executable object resolution is done at compilation time;
table resolution is done at run time (using the identity of the person running the object).

Full rules

Types

Primitive

To handle signed integer, use PLS_INTEGER

Compile

Compiler optimize PL/SQL statements by default. This may rewrite the code (and then make some backtrace wrong).
This should be disabled for debugging, therefore debugging can be perceived as adding some overhead.

Optimization has levels:

0. Esoteric for some long-since-passed compatibility issues with release 9 and before
1. Basic code generation - Use for debuging
2. Global optimization (default)
3. Automatic inlining of local procedures

To change it for diagnostic/debugging purpose : ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = <LEVEL>;
More on this

write to fs

console

Easiest way is dbms_output.put_line('Hello, world !'); , but remnid to activate output before:

in you IDE
in sqlplus

Exception

OUT Parameter feeding

If an exception is raised, OUT parameters are not assigned. If you want to return the execution status or error message throught these paramaters, you'll not achieve your goal unless you use NOCOPY in parameter definition, ex p_error_message OUT NOCOPY VARCHAR2 . More on this here

prc_exception_without_nocopy - l_error_message:
prc_exception_with_nocopy - l_error_message: ORA-01403: no data found

Manual handling

Log details:

context: use $$PLSQL_LINE ($$PLSQL_UNIT only works with stand-alone component, NOT with packages)

dbms_output.put_line( 'this is line ' || $$PLSQL_LINE || ' of plsql unit ' || $$PLSQL_UNIT ); ;

error: error message SQLERRM .

Clean one

The best way is to :

put less exception-handling code;
while getting the more detais.

dbms_utility.format_error_stack does not show the full error stack with line numbers; SQLERRM acts in the same manner. Consider using dbms_utility.format_error_backtrace();

dbms_utility.format_error_backtrace

Note the backtrace go back only to the last RAISE in one's session, so take one of the following two approaches:

call it in the base component: call the backtrace function in the exception section of the block in which the error was raised. This way you have (and can log) that critical line number, even if the exception is re-raised further up in the stack.
call it from top component only: include no exception handlers in intermediate programs in your stack, and call the backtrace function in the exception section of the outermost program in your stack.

Records

Records are collections of table-based types. Quick overview

Collections

Iterating on data (usually trough a cursor) involve MANY context switches between PL and SQL, and that reduce performance. To avoid this, use BULK COLLECT to read data and FORALL to modify data. Steve Feuerstein

BULK COLLECT

Optimize performance for SQL actions based on collections (from database).

OPEN curs_source;
LOOP
    FETCH curs_source BULK COLLECT INTO collec_staging LIMIT block_size;
    FOR i IN collec_staging.FIRST..collec_staging.LAST LOOP
       NULL;
     END LOOP ;
     EXIT WHEN curs_source%NOTFOUND;
END LOOP;

FORALL

Optimize performance for SQL actions based on collections (to database).

FORALL i IN collec_source.FIRST..collect_source.LAST
INSERT INTO table_target VALUES collec_source(i);

Advanced keywords

Transaction

Show pending (uncomitted) transactions SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE';

Dynamic SQL

Bind

Bind variables are not allowed with DDL, will give 00903 - invalid table name

Optimizing

profiler

DBMS profiler

hierchical profiler

Table creation

Misc

Run-time reflection

Currently executing code

You can get some data about current executing unit with $$PLSQL_UNIT, $$PLSQL_LINE . $$PLSQL_UNIT give you the name of the stand-alone component (function, procedure) currently executing. In function, procedure within, it would give you the package name.