FORALL and Every Version of PLSQL

Question:
I love the FORALL feature that Oracle added to PL/SQL in Oracle8i Database! I use it whenever possible to implement high-speed DML processing. I am also writing code that must work on Oracle9i Database and Oracle Database 10g. I was very excited to see that Oracle Database 10g offers the INDICES OF and VALUES OF clauses to allow me to use FORALL with sparsely filled collections. I'd love to take advantage of FORALL, but I don't want to maintain two different sets of code, one for Oracle9i Database and the other for Oracle Database 10g. How would you suggest I do that?
Answer:
This question was answered in the July/August 2006 issue of Oracle Magazine.
Take full advantage of every version of PL/SQL. Yes, FORALL is quite wonderful, isn't it? And you should take maximum advantage of every nuance of that feature—but managing multiple sets of code is a real chore.
Sounds like a job for conditional compilation, along with the new DBMS_DB_VERSION package. This package contains a set of constants that provide both absolute and relative information about the version of Oracle Database in which you are executing your code.
For example, in Oracle Database 10g Release 2, this package is defined as shown in Listing 4.
Code Listing 4: DBMS_DB_VERSION package definition
CREATE OR REPLACE package dbms_db_version is
version constant pls_integer := 10; -- RDBMS version number
release constant pls_integer := 2; -- RDBMS release number
ver_le_9_1 constant boolean := FALSE;
ver_le_9_2 constant boolean := FALSE;
ver_le_9 constant boolean := FALSE;
ver_le_10_1 constant boolean := FALSE;
ver_le_10_2 constant boolean := TRUE;
ver_le_10 constant boolean := TRUE;
end dbms_db_version;
/
Now let's apply this package to help us write one program that will automatically use the INDICES OF clause of FORALL if it is available.
First, I create a table and a package specification that contains a procedure that facilitates the bulk insert of rows into the table:
CREATE TABLE otn_demo (
num NUMBER, name VARCHAR2(100))
/

CREATE OR REPLACE PACKAGE
otn_demo_insert
IS
TYPE otn_demo_aat
IS TABLE OF otn_demo%ROWTYPE
INDEX BY PLS_INTEGER;
PROCEDURE insert_rows (
rows_in IN otn_demo_aat);
END otn_demo_insert;
/
Next, the package body: The insert_rows procedure uses INDICES OF if I am running Oracle Database 10g or higher. If I am running Oracle9i Database Release 2 or earlier, I copy the contents of my possibly sparse collection over to a densely filled collection. In both cases, I take advantage of record-level inserts, as shown in Listing 5.
Code Listing 5: OTN_DEMO_INSERT package body
CREATE OR REPLACE PACKAGE BODY otn_demo_insert
IS
PROCEDURE insert_rows ( rows_in IN otn_demo_aat )
IS
BEGIN
$IF DBMS_DB_VERSION.VER_LE_9_2
$THEN
DECLARE
l_dense otn_demo_aat;
l_index PLS_INTEGER := rows_in.FIRST;
BEGIN
WHILE (l_index IS NOT NULL)
LOOP
l_dense (l_dense.COUNT + 1) := rows_in (l_index);
l_index := rows_in.NEXT (l_index);
END LOOP;

FORALL indx IN 1 .. l_dense.COUNT
INSERT INTO otn_demo VALUES l_dense (indx);
END;
$ELSE
FORALL indx IN INDICES OF rows_in
INSERT INTO otn_demo VALUES rows_in (indx);
$END
END insert_rows;
END otn_demo_insert;
/
So I have one program unit to maintain but a code base that automatically takes maximum advantage of the available set of features. Nice stuff!
Let's look at one more example. Oracle Database 10g introduced new floating-point datatypes, BINARY_FLOAT and BINARY_DOUBLE. They offer improved performance for heavy number crunching. The following program automatically declares a local variable to be BINARY_FLOAT if the datatype is available. Otherwise, it settles for the traditional NUMBER.
CREATE OR REPLACE PROCEDURE
crunch_numbers
IS
n $IF DBMS_DB_VERSION.VER_LE_9_2
$THEN NUMBER;
$ELSE BINARY_FLOAT;
$END
BEGIN
$IF DBMS_DB_VERSION.VER_LE_9_2
$THEN n := 1.0;
$ELSE n := 1.0f;
$END
DBMS_OUTPUT.put_line ( n );
END crunch_numbers;
/
Note that in this case, I use conditional compilation to interrupt a regular statement: The selection of the datatype occurs within the declaration of the variable n. I don't have to use conditional compilation only to choose between two or more intact, executable statements