Data Dictionary Views for PL/SQL Developers: ALL_ARGUMENTS

The ALL_ARGUMENTS view contains information about all the parameters of packaged subprograms and schema level program units defined in your schema. You can use it to perform quality assurance checks on those parameters, checking to see, for example, if troublesome datatypes like CHAR or LONG have been used.

Oracle PL/SQL: Dynamic SQL

Use dynamic SQL to avoid hard-coding in your application and craft more generic, reusable programs.

Datetime and Interval Data Types : Datetime/Interval Arithmetic

Whenever possible, rely on straightforward date arithmetic to manipulate your dates and timestamps.

AUTHID (Invoker and Definer Rights): Using Template Objects or Synonyms for IR Subprograms

When you execute an invoker rights (IR) subprogram (one defined with the AUTHID CURRENT_USER clause), Oracle will use the privileges of the invoker (both directly granted and granted through roles) to resolve references to database objects like tables and views. When a program unit is compiled, however, only directly-granted privileges can be used to resolve references. So you must either create a "template" object (same structure as the invoker's object, with or without data) or a synonym that points to an invoker's object.

Declaring and Calling Subprograms: Recursive PL/SQL Subprograms

When you need to guarantee uniqueness of a string value, your algorithm should assume that it will take more than one attempt to get to a unique value. Recursion will often offer an elegant solution in these situations.

Oracle PL/SQL: Write No Unnecessary Code

Program units that contain unnecessary code (code that can be removed without changing the behavior of that program) are units inviting maintenance problems. You should always be careful to remove any code that is not needed. Use the compile-time warning to help you identify at least some of these occurrences.

Bulk Processing with FORALL and BULK COLLECT: BULK COLLECT - Multiple Row Selects

Avoid row-by-row querying whenever possible. Cursor FOR loops are automatically optimized to retrieve up to 100 rows with each fetch, and if that is not suitable, use BULK COLLECT. Finally, remember that if you are working with nested tables or varrays, you can fetch data from them by using the TABLE operator.

Numeric Data Types : NUMBER and its Subtypes

If you ever need to shift from displaying or working with integers, to working with numbers (having one or more significant digits to the right of the decimal point), make sure you find and change all elements of code that could explicitly or implicitly convert the number to an integer.