Examining Bulk Binding with the INDICES OF and VALUES OF Keywords

Completed the Installing Oracle Database 10g on Windows Using Real Application Clusters (RAC) and Automated Storage Management (ASM) tutorial

OR

Completed the Installing Oracle Database 10g on Linux tutorial

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:

cd /home/oracle/wkdir
sqlplus hr/hr
@setup.sql

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.sql

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;
... 

From your SQL*Plus session, execute the following commands:

sqlplus system/oracle
grant debug any procedure to hr;
grant debug connect session to hr;

Click the JDeveloper icon that you created in your panel.

Click the Connections tab in the Connections - Navigator window. Then right-click Database and select New Database Connection.

On the Welcome page, click Next.

Enter o10g1 for the connection name, and then click Next.

Enter hr as both the username and the password. Then click Next.

Enter raclinux1.us.oracle.com for Host Name, 1521 for JDBC Port, and O10G1 for SID. Then click Next.

Test your connection by clicking Test Connection.

When you see the "Success!" message, click Finish.

Your connection has been created.

From the Connections - Navigator window, select Connections > Database > o10g1 > HR > Packages and then right-click the P_BULK_BIND package body. Select Open to view the PL/SQL code.

You must compile this package body. Right-click the P_BULK_BIND package body again and select Compile.

If the toggle numbers before each line of code is not yet displayed, right-click in the Code Editor margin and select Toggle Line Numbers.

To set the breakpoint, scroll down to the statement forall j in indices of g_emp_recs (at approximately line 171) and click the line number.

To start debugging, right-click in the Code Editor window and select Debug.

The Debug PL/SQL window appears, which displays the three global procedures within the package. Select the BULK_INSERT_WITH_INDICES_OF procedure. This procedure takes one input parameter, which is the Boolean flag I_CAUSE_EXCEPTION. The value for this parameter is set to TRUE. In the PL/SQL block area, change

I_CAUSE_EXCEPTION := NULL;

to

I_CAUSE_EXCEPTION := TRUE; 

Then click OK.

The debugger executes the statements up to the first breakpoint. At this point, the breakpoint statement to be executed next is highlighted. The Stack window displays the procedure being executed. Click the Data tab to display the variables and their values in the current procedure.

Expand Package body variables of P_BULK_BIND. You see two variables: G_EMP_RECS and G_VALUES_OF_TAB. G_EMP_RECS is the PL/SQL table used by the application to bulk insert data into the NEW_EMPLOYEES table.

Expand G_EMP_RECS > _values > [25] > _value. Notice that _value shows the actual record values and that the EMAIL column is null for this record. To observe the effect, click the Resume button (green button on the main toolbar).

The procedure is now executed to the end. The Log window displays program output, at the end of which are the error messages for the two records for which insert failed.

To debug the problem, you can start the debugger with a conditional breakpoint. The goal is to halt execution in the exception block for the erroneous record. For this, set a breakpoint at the line if n = v_iteration. Click line number 194.

Select View > Debugger > BreakPoints.

Right-click the breakpoint $Oracle.PackageBody.HR.P_BULK_BIND.pls 194. Then select Edit.

Click the Conditions tab.

In the Condition field, enter n = v_iteration. Only when this condition is evaluated to true does the debugger stop at the breakpoint. Then click OK.

Start the debugger again. Right-click in the Code Editor window and select Debug.

In the Debug PL/SQL window, select the BULK_INSERT_WITH_INDICES_OF procedure. I_CAUSE_EXCEPTION should still be set to TRUE. Click OK.

Execution halts when the erroneous record is reached (while processing the exceptions). Expand G_EMP_RECS > _values > [25] > _value. Notice that _value shows the actual record values and that the EMAIL column is null for this record. Right-click the EMAIL column and select Modify Value.

Enter BERNST as the New Value and click OK.

Click Resume.

The second breakpoint is reached. Click Resume.

The results are displayed. Notice that the record with the last name ERNST is no longer an error because the EMAIL column is not null any longer.