Introduction
Application Overview
Software Requirements
Terminology
Configuring the Application
Running the Application
Sample Application Files
Additional References

Introduction

• Prerequisite
  
  
• Technical Overview

Prerequisite

• PL/SQL
  
  

Technical Overview

When a PL/SQL application requires to process an index-by table, at times the standard FOR loop can not be used to iterate through the values when is not sure that all the indexes exist on the table. Oracle Database 10g introduces support for the FORALL syntax with non-consecutive indexes in collections. The INDICES OF clause allows the FORALL syntax to be used with sparse collections, while the VALUE OF clause is used for collections of indexes pointing to other collections.


The INDICES OF keyword can be used in a situation where a collection of records is validated programmatically and invalid records are removed from the collection. This results in a sparse collection of valid elements that may then be bulk inserted into a table. By using the INDICES OF keyword, the exceptions for the missing records are not generated. INDICES OF clause can be used when the row numbers defined in another array are to be used.

The VALUES OF clause can be used when the values defined in another array are used in a DML operation. It only processes the rows with row numbers matching the content of a row in the driving array. Consider a scenario in which a collection of records must be copied to one or more collection variables, based upon some condition whereby certain records may or may not be copied, and then inserted into a table. This can be efficiently done by using the "VALUES OF" syntax and using a pointer array whose elements are pointers to the selected records within the original collection.

Application Overview

This sample application demonstrates the use of new clauses in FORALL statement, with a order processing scenario. When a customer creates a shopping cart and checks out the cart, the order data is stored in the ALL_ORDERS table. Assuming that the application needs to separate the newly created records and to be sent for order processing. Also the orders that are already processed and quite old require to be archived in a separate table. In this application all the orders are stored in ALL_ORDERS table. When the filter_records.sql is run, it fetches all the records into a collection and further processed to separate the newly created orders and orders that are already fulfilled. The criteria used here based on the order date and the order status. When this criteria is satisfied certain elements are removed from the collection. This results in a sparse collection which is used to insert orders into the NEW_ORDERS table. This part of the code demonstrates the use of "INDICES OF" clause. Also a collection which points to the index values of 'to be archived' records is created which is then used to insert the data in the ORDERS_ARCHIVE table. This demonstrates the use "VALUES OF" clause while using the FORALL statement.

Software Requirements

List the softwares required for configuring and running this sample application.

• Oracle Database 10g Release 1 (10.1.0.2)
  

Terminology

Term	Definition
<SAMPLE_HOME>	The directory where the sample is extracted

Configuring the Application

• Unzip the downloaded FORALLSample.zip. Extract the file contents into <SAMPLE_HOME> directory.
  This creates FORALLSample folder with all the files and folders.
  
  
• Open the command prompt and move to <SAMPLE_HOME>/FORALLSample/src folder by executing the following command,
  
cd <SAMPLE_HOME>/FORALLSample/src
  
  
• Open SQL prompt. Connect as SCOTT/TIGER and run the config.sql script from <SAMPLE_HOME>/FORALLSample/src folder. This will create the necessary tables and inserts the orders data into ALL_ORDERS table.
  Example,
  
  SQL>@config.sql
  

Running the Application

Follow the steps give below to run this application.

• From the SQL command prompt run the filter_orders.sql file. Example,
  
  SQL>@filter_orders.sql
  
  Running this will insert the filtered orders data into NEW_ORDERS and ORDERS_ARCHIVE tables. You can view this data by running the SELECT query on both the tables.
  
  Refer to the filter_orders.sql file for more details on how to use "INDICES OF" and "VALUES OF" clauses with the FORALL statement.
  

Sample Application Files 

This section provides a tabular listing of the sample application files and their descriptions.

Directory	File	Description
FORALLSample\doc	readme.html	This file
FORALLSample\src	config.sql	SQL file that creates necessary database objects
FORALLSample\src	filter_orders.sql	Used for filtering out new and old orders. This file uses the new clauses in the FORALL statement.

Additional References 

• Oracle 10g Adds More to FORALL
• PL/SQL Samples on OTN
  

Please enter your comments about this sample application here.