Data Type Conversion: Implicit Conversion

At run time, PL/SQL converts between different data types implicitly (automatically) if necessary. For example, if you assign a PLS_INTEGER variable to a NUMBER variable, then PL/SQL converts the PLS_INTEGER value to a NUMBER value (because the internal representations of the values differ). Oracle recommends that you avoid implicit conversion and instead either ensure that you work with variables of the same datatype (avoiding the need for any kind of conversion) or you perform explicit conversions with programs like TO_CHAR or TO_DATE.

Guidelines for Optimizing Performance: Avoid execution of infinite loops

Infinite loops are the bane of a programmer's existence, and an embarrassment, too. Avoid them through careful code review, proper loop construction and, just in case, inclusion of "loop killer" code to short-cut runaway execution.

Character Functions Returning Number Values: ASCII

Whenever you need to associate numeric values with letters, consider using the ASCII and/or CHR functions (if appropriate, given your character set). And, more generally, when seeking to implement subprograms for your application, avoid creating unnecessary structures and code. Utilize fully what is provided to you in the base language(s).

Edition Based Redefinition: Instantiation of Editioned and Non-editioned Packages

The behavior of editioned and non-editioned packages differ when a session switches its current edition during its life: while a non-editioned package is instantiated once per session, as usual, an editioned package will be instantiated separately under each current edition under which it is accessed, that is, it will have a separate package state maintained for each edition.

SUBSTR: Negative Start Location in SUBSTR

Whenever you need to get a sub-string from the right side of a string, use a negative starting location. That's much easier than writing your own algorithm!

INSERT Statement: Inserting When Columns Have Default Values

If a column in a table has a default value, you do not have to provide an expression for that column in your INSERT statement - but only if you list explicitly the names of the columns before the VALUES clause.

FORALL: Restrictions on use of FORALL

The classic use case for FORALL is a loop that contains one or more DML statements. More generally, look for scenarios where a DML statement is executed repeatedly, only changing its bind variable values with each execution. You cannot, however, use FORALL with remote tables; with dynamic SQL whose table or column name changes with each iteration; with DDL statements.

DBMS_SQL.DESCRIBE_COLUMNS: Type Numbers Returned by DBMS_SQL.DESCRIBE_COLUMNS

When you use DBMS_SQL.DESCRIBE_COLUMNS (and its sister procedures, DESCRIBE_COLUMNS2 and DESCRIBE_COLUMNS3) to get information about columns, remember that the column type is an integer value and that value is the same for "N" datatypes (like NVARCHAR2) and their non-"N" variation (such as VARCHAR2).