Examining Bulk Binding with the INDICES OF and VALUES OF Keywords

PL/SQL Conditional Compilation

Applications often contain debugging or tracing code that is written specifically for testing purposes during the development cycle. You don’t want this code to run when the application runs in production. In earlier releases, you had either to remove this code completely for production (hindering subsequent debugging) or to surround it with run-time IF tests (incurring a run-time cost in both program size and execution speed). Now you can enable debugging or tracing functionality in the development environment and disable that functionality in the production application.

Applications often contain self-checking code, to comply with good programming practices. Such code raises an exception if an invariant on which the program design depends is violated. Such exceptions ideally should never be raised in the production environment. However these self-tests incur a run-time cost especially when they occur inside deeply nested loops. But to remove them all, after system testing is done and before the application goes live, would compromise the ability of the debugging team to diagnose bugs that were first seen in the production environment. Few development shops maintain parallel copies of an application’s source: one for debugging and one for production. PL/SQL conditional compilation supports a new best practice: by selectively compiling the self-checks, the diagnosing benefit is retained without cost for the production environment.

Packages usually contain private "helper" subprograms and, of course, these need to be tested during development. But because they're private, they can't be tested by an external test harness. By declaring these "private" subprograms within a conditional directive in the package specification, you can call these programs from outside the package during development and testing and then conceal them in the production environment.

Application code often calls “service” subprograms. Such a service subprogram might return a PL/SQL record describing an employee when called with the employee's unique identifier. Best practice dictates that this service routine raises a well-defined exception if the supposedly unique key finds more than one record. (This would occur in the event of data corruption, caused by a different, and buggy, component of the total system.) Best practice further dictates that the application code that calls the service subprogram must behave correctly when this exception is raised, for example by showing the user a polite and relatively non-specific “problem occurred” message and by logging all the relevant information to specify the context in which the exception was raised to a trace file. Of course, this robustness feature of the application code must be tested during the development cycle. It's often tricky and time-consuming to contrive the corrupt data that would cause the service subprogram to raise the exception. Conditional compilation lets you achieve the same effect cheaply by conditionally including code in the service subprogram to raise the given exception at will.

During development, programmers often create two different versions of a compilation unit that accomplish the same result. Both versions have to be tested to determine the most efficient version. Usually, the two different versions are substantially textually identical but differ in small critical spots distributed over the whole source file. In such cases it's easier, and less prone to human error, to write one program containing both versions rather than to write two separate programs. Conditional compilation comfortably supports this approach where earlier a hand-crafted scheme for generating the two versions of the source code would have been needed.

Some applications must be written to run on not only the latest version of Oracle Database but also on earlier versions. Typically, the development shop wants to maintain only a single body of source code. This has forced developers to avoid using the new and powerful features (exposed by new PL/SQL syntax) in the latest release which has penalized users who do deploy the code on the latest release. Conditional compilation can be used to select the optimal version of the program source code when the program is installed on the latest release and a fallback when it is installed on an earlier release.

Perform the Installing Oracle Database 10g on Windows tutorial.

Download and unzip the plsql.zip file into your working directory (i.e. c:\wkdir).

Open a terminal window and execute the following commands:

cd\wkdir
sqlplus hr/hr

Create the package STATIC_CONSTANTS to declare package constants that can be used in conditional compilation. From your SQL*Plus session, execute the following script:

@static_constants

The static_constants.sql script contains the following:

CREATE OR REPLACE PACKAGE static_constants is
debug CONSTANT BOOLEAN := FALSE; 
trace CONSTANT BOOLEAN := FALSE;
END ; 
/

Create two procedures CHECK_DEBUG which checks whether the value of the package constant debug is TRUE and CHECK_TRACE which checks if the value of the package trace is TRUE. From your SQL*Plus session, execute the following script:

@debug_trc

The debug_trc.sql script contains the following:

CREATE OR REPLACE PROCEDURE check_Debug IS
BEGIN
  $IF static_constants.debug $THEN DBMS_OUTPUT.put_line('Debugging ON'); 
  $ELSE DBMS_OUTPUT.put_line('Debugging OFF'); $END
END;
/
CREATE or REPLACE PROCEDURE Check_trace IS
BEGIN
  $IF static_constants.trace $THEN DBMS_OUTPUT.put_line('Tracing ON'); 
  $ELSE DBMS_OUTPUT.put_line('Tracing OFF'); $END
END;
/

Set serveroutput on  using the new SIZE UNLIMITED syntax available in Oracle Database 10g Release 2. Now execute both the procedures. You will see that it displays that both tracing and debugging are tuned off.

Note: You can copy paste each line individually into sqlplus however do not copy and paste the 3 statements together.

set serveroutput on size unlimited
exec check_debug
exec check_trace

Now you will change the values of the package constants. From your SQL*Plus session, execute the following script:

@reset_const

The reset_const.sql script contains the following:

CREATE OR REPLACE PACKAGE static_constants is
debug CONSTANT BOOLEAN := TRUE; 
trace CONSTANT BOOLEAN := TRUE;
END ; 
/

Now execute the two procedures again. You will see that both debugging and tracing are on automatically. When a package is recompiled all dependent objects become invalid and get recompiled at the next execution. Therefore the new values of the package constants apply to all the dependent objects immediately.

exec check_debug 
exec check_trace

This can be extended to any number of dependent programs. This method is most useful when a large number of programs need to be controlled with a single mechanism. Other examples of use cases are changing tax by state for the same application or changing software features based on licensing options and so on.

Alter the session to show all compiler warnings. Execute the following from your SQL*Plus session:

ALTER SESSION SET  plsql_warnings = 'enable:all';

Note that all conditional compilation directive have been removed.

Create the SEND_MESSAGE_TO_DBA and CHECK_UNIQUE procedures. The CHECK_UNIQUE procedure contains two different pieces of code, one that is used in development for testing and the other that is deployed in the final production environment. The development code uses conditional compilation directives and the production code uses a traditional IF-THEN-ELSE logic to check for duplicate records. From your SQL*Plus session, execute the following scripts:

@dba_email
@chk_unq
show errors

The dba_email.sql script contains the following:

CREATE OR REPLACE PROCEDURE send_message_to_DBA(emp_id number)
IS
    mailhost    VARCHAR2(64) := 'mailhost.fictional-domain.com';
    sender      VARCHAR2(64) := 'HR_APP@fictional-domain.com';
    recipient   VARCHAR2(64) := 'DBA@fictional-domain.com';
    mail_conn  utl_smtp.connection;
BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, sender);
    utl_smtp.rcpt(mail_conn, recipient);
-- open_data(), write_data(), and close_data() into a single call to data().
    utl_smtp.open_data(mail_conn);
    utl_smtp.write_data(mail_conn, 'A primary key violation has occurred for record '||
   emp_id || 'in the EMPLOYEES table. This is an automatically generated e-mail 
   message. Please do not respond to this, this is an alert.' || chr(13));
    utl_smtp.write_data(mail_conn, 'This is line 2.' || chr(13));
    utl_smtp.close_data(mail_conn);
    utl_smtp.quit(mail_conn);
    EXCEPTION
        WHEN OTHERS THEN       
           NULL;
END;
/

The chk_unq.sql script contains the following:

Create or replace Procedure check_unique(emp_id NUMBER) is
v_num number;
force_pk_violation exception;
Begin
Select count(*) into v_num from employees where employee_id = emp_id;
-- production code
         If v_num > 1 
         then SEND_MESSAGE_TO_DBA(emp_id);
              raise force_pk_violation;        
         END IF ;
-- Development code 
         $if $$FORCE     
         $then SEND_MESSAGE_TO_DBA(emp_id);
               raise force_pk_violation;
   $end
END;    
/

Now alter the session to set the value of $$force to FALSE. Execute the following command from SQL*Plus.

ALTER SESSION SET PLSQL_CCFLAGS='force:FALSE';

Now execute chk_unq.sql again.

The warning does not appear as the variable $$force now has a value of FALSE;

Create the GET_RECORD procedure. This procedure accepts an employee id and returns the employee record. If a duplicate exists as checked by the CHECK_UNIQUE procedure a friendly message is displayed . Execute the following script in your SQL*Plus session:

@get_record

The get_record.sql script contains the following:

Create or replace procedure get_record(emp_id IN NUMBER)
as
emp_record  employees%rowtype;
Begin
check_unique(emp_id);
Select * into emp_record from employees where employee_id =emp_id;
Dbms_output.put_line ( 'Name: '|| emp_record.first_name||' 
                       '||emp_record.last_name||' '||'Hiredate: 
                       ' ||emp_record.hire_date||'
                       '||'Job: '||' '||emp_record.job_id);
Exception
When others then 
DBMS_OUTPUT.PUT_LINE('We are unable to process your request at this time. 
  Please try again later. We apologize for any inconvenience');
End;
/

Now execute the GET_RECORD procedure passing in 100 as the argument.

exec GET_RECORD(100)      

Since there are no duplicates the procedure executes successfully .

Now to test whether the message is displayed when there are duplicates, alter the session and set the value of $$force to TRUE and execute GET_RECORD again. Execute the following commands from your SQL*Plus session.

ALTER SESSION SET PLSQL_CCFLAGS='force:TRUE';
exec GET_RECORD(100) 

The procedure is not affected as it has not been recompiled.

Recompile the CHECK_UNIQUE procedure using ALTER...COMPILE statement and provide the new value of the PLSQL_CCFLAGS parameter. Use the REUSE SETTINGS option. Then execute GET_RECORD again for a value of 100. Execute the following commands from your SQL*Plus session.

ALTER PROCEDURE CHECK_UNIQUE compile 
  plsql_ccflags = 'force:TRUE' REUSE SETTINGS;
exec GET_RECORD(100) 

The PLSQL_CCFLAGS value now is in effect and therefore the program issues the message. This method can be used to affect specific programs within a session as is post production debugging. However each program that needs the new setting has to be recompiled either using CREATE OR REPLACE or ALTER...COMPILE statements.

1.

Execute the following command to see the actual source code used to compile a valid PL/SQL program: 

EXEC DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE('PROCEDURE', 'HR', 'CHECK_UNIQUE');

Note that all conditional compilation directive have been removed.

1.

Issue the ALTER SESSION to set flags to choose between the two versions. First you are choosing to use the NUMBER datatype. From your SQL*Plus session, execute the following command:

ALTER SESSION SET PLSQL_CCFLAGS = ' numversion:TRUE';

2.

You want to see which code executes faster. You will create the CALC_CIRCLE procedure, which calculates the area and circumference of a circle for a given radius. From your SQL*Plus session, execute the following script:

@num_bdbl

The num_bdbl.sql script contains the following:

CREATE or REPLACE PROCEDURE Calc_circle( RADIUS $IF $$NUMVERSION $THEN NUMBER 
$ELSE BINARY_DOUBLE $END)
as 
SUBTYPE my_real IS
    $IF $$numversion $THEN 
         NUMBER; 
    $ELSE                               
         BINARY_DOUBLE;
    $END
 num_circ my_real;
 num_area my_real;
 BDBL_circ my_real;
 BDBL_AREA my_real;
BEGIN
 num_CIRC:=  (3.14016408289008292431940027343666863227 * 2 * RADIUS); 
 NUM_AREA := (3.14016408289008292431940027343666863227*radius*radius);
 DBMS_OUTPUT.PUT_LINE('The circumference is: '||num_circ);
 DBMS_OUTPUT.PUT_LINE('The area is: '||num_area);
END ;
/

3.

Execute the CALC_CIRCLE procedure passing in the number 1234567890 as the radius

set timing on
exec calc_circle(1234567890)

4.

Change the value of $$numversion to FALSE using ALTER COMPILE command to compile CALC_CIRCLE and execute CALC_CIRCLE again with the same argument.

ALTER PROCEDURE  calc_circle COMPILE plsql_ccflags = 'numversion :false' REUSE SETTINGS; 
exec calc_circle(1234567890)
set timing off

You can see the difference in performance. This example illustrates that it is possible to test two different versions of code selectively within the same program using conditional compilation.

Now you can test the version of the Oracle Database. Execute the following script from your terminal window:

@check_version

The check_version.sql script contains the following:

CREATE OR REPLACE PROCEDURE check_version AS
BEGIN
 -- some code which performs transaction processing ...
$if DBMS_DB_VERSION.VER_LE_10_2 $then 
-- traditional commit
COMMIT;
DBMS_OUTPUT.PUT_LINE ('The transaction has been successfully committed.');
$else 
-- faster COMMIT supported in 10.2
COMMIT WRITE IMMEDIATE NOWAIT;
DBMS_OUTPUT.PUT_LINE ('The transaction has been successfully committed.');
$end
END;
/

Now execute the check_version procedure by executing the following command in your SQL*Plus window.

exec check_version
quit

The output of check_version shows that regardless of the version the procedure completes successfully and is transparent to the end user. Therefore there is no reason to have different program units in different versions of the database.

You must first create a NEW_EMPLOYEES table from the existing EMPLOYEES table (in the hr schema) and create a unique index on the EMPLOYEE_ID column. This generates an exception when inserting a duplicate record. Execute the following commands from a terminal window:

sqlplus hr/hr
@setup

The setup.sql script contains the following:

drop table new_employees;
create table new_employees as select * from employees where 1=2;
create unique index new_employees_employee_id 
  on new_employees (employee_id);

You next create the P_BULK_BIND package. This package contains various procedures that create the sparse collection and bulk insert the records into the NEW_EMPLOYEES table using three different approaches.

@cr_p

Click cr_p.sql to see what is contained in this script.

To see how bulk binding works in Oracle Database 10g, we first look at how it was done in Oracle9i. The FORALL statement uses the SAVE EXCEPTIONS syntax to suppress exceptions raised due to missing records, which are later printed in the EXCEPTION block. Execute the following commands:

set serverout on size 100000
exec p_bulk_bind.Bulk_Insert_Pre_10g (i_make_sparse => true)

-- this code is located in the Bulk_Insert_Pre_10g procedure
...
forall j in g_emp_recs.First()..g_emp_recs.Last()
save exceptions
  insert into new_employees values g_emp_recs(j);

exception when bulk_errors then
  for j in 1..sql%bulk_exceptions.Count()
  loop
    Dbms_Output.Put_Line ( 'Error from element #' ||
    To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||
    Sqlerrm(SQL%bulk_exceptions(j).error_code) );
  end loop;
...

From the output, you see that each missing record has generated an exception (ORA-22160). As mentioned earlier, even though the remaining records are processed via the SAVE EXCEPTIONS syntax, this results in a performance degradation if the number of deleted records is fairly high.

The next step is to use the Oracle Database 10g INDICES OF syntax to ignore the missing records and thereby obtain much better performance. Execute the following commands:

exec p_bulk_bind.Bulk_Insert_With_Indices_Of (i_cause_exception => false)

In this case, the Boolean input value is used to ignore a block of code that is responsible for creating an exception condition (as you will see in the next step).

-- this code is located in the Bulk_Insert_With_Indices_Of 
-- procedure
...
forall j in indices of g_emp_recs
save exceptions
  insert into new_employees values g_emp_recs(j);
...

This time there are no exceptions raised because of the missing records.

The preceding procedure can also be run with the input parameter set to true. This causes an exception while inserting that must be trapped (by SAVE EXCEPTIONS) and later processed. The condition causing the exception is simulated via a procedure call, which nulls the EMAIL columns for the employees with the last name of "Ernst" and "Urman." Because the EMAIL column has a NOT NULL restriction, this causes an exception when the two corresponding records are inserted. The exceptions are later processed from the SQL%BULK_EXCEPTIONS collection. Execute the following commands:

exec p_bulk_bind.Bulk_Insert_With_Indices_Of (i_cause_exception => true)

-- this code is located in the exception area
-- in the Bulk_Insert_With_Indices_Of procedure
...
forall j in indices of g_emp_recs
-- between g_emp_recs.First() and g_emp_recs.Last() 
-- optional
save exceptions
  insert into new_employees values g_emp_recs(j);

exception when bulk_errors then
  declare
    v_iteration pls_integer;
    n pls_integer;
    k pls_integer;
  begin
    for j in 1..sql%bulk_exceptions.Count()
    loop
      v_iteration := SQL%bulk_exceptions(j).error_index;
      Dbms_Output.Put_Line (
        'Error on the ' || To_Char(v_iteration) 
         || 'th iteration' );
 ...
         

Note: The error is reported for the iteration number. This is new to the exception handling in Oracle Database 10g.

Another approach is to use the VALUES OF clause to create a second collection of pointers to the elements of the original array. Execute the following commands:

exec p_bulk_bind.Bulk_Insert_With_Values_Of -
   (i_null_email => true, i_violate_pk => false)

The Point_To_Sparse procedure is used to create a collection of pointers based on the same logic as before. That is, it points to the elements with the first occurrence of each letter in the last name. It is called from the Bulk_Insert_With_Values_Of procedure.

-- this code is located in the Bulk_Insert_With_Values_Of 
-- procedure
...
forall j in values of g_values_of_tab
save exceptions
  insert into new_employees values g_emp_recs(j);
...

The result is the same as in step 5: An exception is raised for the fifth and nineteenth iterations.

To see the effect of reporting the iteration number (or pointer element number) as opposed to the index of the erroneous record, you can run the preceding procedure with i_violate_pk => true. To simulate the error condition, the Cause_Exception_For_Values_Of procedure is used to insert a duplicate index entry from the bound collection (G_EMP_RECS) into the pointer collection (G_VALUES_OF_TAB). Because of this, the unique key constraint (created in step 1) is violated and the exception is raised upon bulk insert into the NEW_EMPLOYEES table. Execute the following commands:

exec p_bulk_bind.Bulk_Insert_With_Values_Of -
   (i_null_email => false, i_violate_pk => true)

-- this code is located in the Bulk_Insert_With_Values_Of 
-- procedure
...
exception when bulk_errors then
  declare
     v_iteration pls_integer;
   begin
     for j in 1..sql%bulk_exceptions.Count()
     loop
       v_iteration := SQL%bulk_exceptions(j).error_index;
       Dbms_Output.Put_Line (
         'Error on the ' || To_Char(v_iteration) ||
         'th iteration' );

-- Find the index of the offending element 
-- from the iteration number
       Dbms_Output.Put_Line (
         'last_name for error element: ' ||
          g_emp_recs(g_values_of_tab(v_iteration)).last_name );

       Dbms_Output.Put_Line (
         'Error was: ' || 
          Sqlerrm(SQL%bulk_exceptions(j).error_code) );
     end loop;
   end;
...