Ruby-OCI8 2.0 New Feature: Array DML Support

1, Introduction To Array DML

Oracle Call Interface provides an Array Interface. This array interface significantly reduces round trips to the database when you are updating or inserting a large volume of data. This reduction can lead to considerable performance gains in a busy client/server environment. For example, consider an application that needs to insert 100 rows into the database. Calling OCIStmtExecute() 100 times with single values results in 100 network round trips to insert all the data. The same result is possible with a single call to OCIStmtExecute() using an input array, which involves only one network round trip.


2, Ruby-OCI8 v2.0 Installation

svn co http://ruby-oci8.rubyforge.org/svn/trunk/ruby-oci8

And then follow the Ruby-OCI8 install instruction.

3, Usage

Three new instance methods are added to class OCI8::Cursor for supporting array dml:

1) max_array_size = val

Set the maximum array size for bind_param_array. This method should be called before bind_param_array.

example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(:name, ['mike', 'john', 'danny'])
cursor.bind_param_array (:age, [20, 21, 22])
cursor.exec_array

When calling this method, all the binds will be clean from cursor if instance variable max_array_size of cursor is set before.

example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(:name, ['mike', 'john', 'danny'])
cursor.bind_param_array(:age, [20, 21, 22])
cursor.max_array_size = 4
cursor.exec_array

Above example will generate an exception because you don't re-bind the params after calling max_array_size.

2) bind_param_array(key, var_array, type = nil, max_item_length = nil)

Bind array explicitly. This method is used to bind an array of values to a placeholder embedded in the prepared statement which is to be executed with "exec_array".

When key is number, it binds by position, which starts from 1. When key is string, it binds by the name of placeholder.

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name)")
cursor.max_array_size = 3
cursor.bind_param_array(:name, ['mike', 'john', 'danny']) #bind by name
... or ...
cursor.bind_param_array(1, ['mike', 'john', 'danny']) #bind by position

The size of arrays bound through bind_param_array should not be greater than the size set by method max_array_size = val.

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name)")
cursor.max_array_size = 2
cursor.bind_param_array(:name, ['mike', 'john', 'danny'])
cursor.exec_array

Above example will generate an exception because the size of array passed to bind_param_array is 3, which is greater than the size 2 set by max_array_size.

If you are binding more than one parameters, all the arrays are required the same size.

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(:name, ['mike', 'john', 'danny'])
cursor.bind_param_array(:age, [20, 21])
cursor.exec_array

Above example will generate an exception becase the name parameter array and age parameter array are not the same size.

The array dml also supports setting values through method "[key] = val".

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(1, nil, String)
cursor.bind_param_array(2, nil, Fixnum)
cursor[1] = ['mike', 'john', 'danny']
cursor[2] = [20, 21, 22]
cursor.exec_array

3) exec_array

Executes the SQL statement assigned the cursor with array binding. This implementation currently only supports non-data returning statements (INSERT, UPDATE, DELETE but not SELECT). All binding arrays should be the same size and this size will be used as iteration count for OCIStmtExecute().

Example:

cursor = conn.parse("INSERT INTO test VALUES(:name, :age)")
cursor.max_array_size = 10
cursor.bind_param_array(1, nil, String)
cursor.bind_param_array(2, nil, Fixnum)
cursor[1] = ['mike', 'john', 'danny']
cursor[2] = [20, 21, 22]
cursor.exec_array

In above example, three rows are inserted to the table though max_array_size is set 10.