[ruby-oci8-devel] Proposal for bind_param_array function prototype

KUBO Takehiro kubo at jiubao.org
Tue Dec 18 09:16:15 EST 2007


Hi Liming,

Sorry too late to reply you.

Liming Lian <liming.lian at oracle.com> writes:

> Hi,
>
> I have a workaround for the problem of execution count. Following is my 
> proposal.
>
> We can add a function OCI8::Cursor#set_batch_size to set the iteration 
> count for statement execution.  This function needs a parameter "size" 
> like following: set_batch_size(size). When users want to bind array, 
> they *must* call OCI8::Cursor#set_batch_size first. And they must also 
> make sure the size of the binding array is equal to the size declared at 
> set_batch_size. Otherwise, an error will be raised to prompt users 
> incoming array with incorrect size. Besides set_batch_size, like the 
> implementation of array DML in Perl DBD::ORACLE, we should also add 
> another function OCI8::Cursor#exec_array. Bind_param_array and 
> bind_param cannot be mixed in the same statement execution, and 
> bind_param_array *must* be used with OCI8::Cursor#exec_array; using 
> bind_param_array will have no effect for OCI8::Cursor#exec.
>
> Users can call set_batch_size many times during Cursor process. But 
> every time at calling set_batch_size, if the new size isn't equal to the 
> old one, all the binds are clear from the Cursor. In that situation, 
> they have to re-bind all the parameter array.

How about the following proposal?
---------------

1. OCI8::Cursor#max_array_size = positive_number

  Users can call max_array_size= many times during Cursor process. But
  every time at calling max_array_size= all the binds are clear from the
  Cursor. In that situation, they have to re-bind all the parameter array.

2. OCI8::Cursor#bind_param_array(key, var_array, type = nil, max_item_length = nil)

  If batch_size= is not called, an error is raised.

  If the size of var_array is less than max_array_size, the rest values
  are NULL.

  If the size of var_arary is greater than max_array_size, an error is
  raised.

3. OCI8::Cursor[key] = var_array

  If bind_param_array(key, ...) is not called, an error is raised.

  If the size of var_array is less than max_array_size, the rest values
  are not changed.

  If the size of var_arary is greater than max_array_size, an error is
  raised.

4. OCI8::Cursor#exec_array(iteration_count = nil)

  If iteration_count is nil, max_array_size is used instead.
  If iteration_count > max_array_size, an error is raised.

---------------

What I mind about your proposal is:
> every time at calling set_batch_size, if the new size isn't equal to the 
> old one, all the binds are clear from the Cursor. In that situation, 
> they have to re-bind all the parameter array.

I imagine what time users execute array DML. IMO, it is when inserting
many rows from CSV files or from other database tables. Getting all
rows and inserting them at once is a bad way. It takes large memory
for many rows. I think fetching 100 rows or so and inserting them
repeatedly will be most efficient. But the last execution's array
size will not equal to the size of set_batch_size. Thus, I want to add
a parameter iteration_count to exec_array.
set_batch_size, bind_param_array and [key]= are changed as a result of
a change in exec_array.

> Look at following usage scenarios for demonstration.
>
> 1) Correct usage
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.set_batch_size(3)
> cursor.bind_param_array(1, ["test1", "test2", "test3"])
> cursor.bind_param_array(2, [20, 21, 22])
> cursor.exec_array

1) Correct usage

cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(1, ["test1", "test2", "test3"])
cursor.bind_param_array(2, [20, 21, 22])
cursor.exec_array

> 2) Correct usage
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.set_batch_size(3)
> cursor.bind_param_array(1, nil, String)
> cursor.bind_param_array(2, nil, Fixnum)
> cursor[1] = ["test1", "test2", "test3"]
> cursor[2] = [20, 21, 22]
> cursor.exec_array

2) Correct usage

cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(1, nil, String)
cursor.bind_param_array(2, nil, Fixnum)
cursor[1] = ["test1", "test2", "test3"]
cursor[2] = [20, 21, 22]
cursor.exec_array

> 3) Correct usage
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.set_batch_size(3)
> cursor.bind_param_array(1, ["test1", "test2", "test3"])
> cursor.bind_param_array(2, [20, 21, 22])
> cursor.set_batch_size(2)
> cursor.bind_param_array(1, ["test4", "test5"])
> cursor.bind_param_array(2, [23, 24])
> cursor.exec_array

3) Correct usage

cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(1, ["test1", "test2", "test3"])
cursor.bind_param_array(2, [20, 21, 22])
cursor.max_array_size = 2
cursor.bind_param_array(1, ["test4", "test5"])
cursor.bind_param_array(2, [23, 24])
cursor.exec_array

> 4) Incorrect usage
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.bind_param_array(1, ["test1", "test2"])
> cursor.bind_param_array(2, [20, 21])
> cursor.exec_array
>
>  == Error raised: should call set_batch_size first

4) Incorrect usage

cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
cursor.bind_param_array(1, ["test1", "test2"])
cursor.bind_param_array(2, [20, 21])
cursor.exec_array

 == Error raised: should call max_array_size first


> 5) Incorrect usage
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.set_batch_size(3)
> cursor.bind_param_array(1, ["test1", "test2"])
> cursor.bind_param_array(2, [20, 21])
> cursor.exec_array
>
>  == Error raised: incorrect incoming bound array size

5) Correct usage (different with yours)

cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(1, ["test1", "test2"])
cursor.bind_param_array(2, [20, 21])
cursor.exec_array(2)

> 6) Incorrect usage
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.set_batch_size(3)
> cursor.bind_param_array(1, ["test1", "test2"])
> cursor.bind_param_array(2, [20, 21])
> cursor.exec
>
>  == Error raised: should call exec_array to process array DML
>

6) Incorrect usage

cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(1, ["test1", "test2"])
cursor.bind_param_array(2, [20, 21])
cursor.exec

 == Error raised: should call exec_array to process array DML

> 7) Incorrect usage
>
> cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
> cursor.set_batch_size(3)
> cursor.bind_param_array(1, ["test1", "test2"])
> cursor.bind_param_array(2, [20, 21])
> cursor.set_batch_size(2)
> cursor.exec_array
>
>  == Error raised: OCIError: ORA-01008: not all variables bound
>  == Reason: didn't re-bind the array after calling set_batch_size with a 
> new batch size
>

7) Incorrect usage

cursor = conn.parse("INSERT INTO test_table VALUES(:name, :age)")
cursor.max_array_size = 3
cursor.bind_param_array(1, ["test1", "test2"])
cursor.bind_param_array(2, [20, 21])
cursor.max_array_size = 2
cursor.exec_array

 == Error raised: OCIError: ORA-01008: not all variables bound
 == Reason: didn't re-bind the array after calling set_batch_size with a 
new batch size

-- 
KUBO Takehiro <kubo at jiubao.org>


More information about the ruby-oci8-devel mailing list