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

[Liming Lian]

Hi Kubo,
> Is this prototype is for PL/SQL binds or non-PL/SQL  binds?
>   
Sorry for forgetting to declare it, this is for non-PL/SQL binds. 
Another update for parameter "type", if it isn't specified, it should 
use the first *non-nil* element's type, not just the first element's.
> If it is for non-PL/SQL binds, that's another story.
> All max_array_size should be same for one OCI8::Cursor.
> That's why one of the values is passed to OCIStmtExecute()'s iters
> argument.
>   

Maybe we have different views on the attribute "max_array_size". In my 
proposal, it is a pre-defined value for the limit of maximum array size. 
Users can bind arrays with sizes not greater than max_array_size freely. 
At the same time they should make sure all the bound arrays in an cursor 
are the same size.  But I think you may treat this attribute as number 
of array elements for a single statement execution.

It is hard to implement array insert if we have to call cursor.exec 
whose iteration count is fixed value: max_array_size. This is because, 
once user has bound an array with size less than max_array_size and call 
cursor.exec, the iteration count will be greater than the bound array 
size. This will lead an error: "ORA-12899: value too large for column 
xxx." So in my opinion, the iteration count for cursor.exec should be 
equal to the size of the array user has bound, not the max_array_size.

Then the problem comes to me: how can I know the exact size of the array 
user has bound at the calling of cursor.exec? I think this is a little 
bit difficult because OCI8::Cursor support method OCI8::Cursor[key] = val.

I have managed to insert arrays through OCI8 with following assumption 
and modifications. (I don't mean this is a proper and reasonable 
approach to implement array DML for OCI8. I only want to address the 
difficult point more clear through this example.)

*** Assumption

Users should bind incoming array only by bind_param_array and should 
never use "Cursor[Key] = val" later to update the array binding.

*** Modifications

I added an instance variable "exec_iters" to OCI8::Cursor with default 
value "nil". When binding normal parameter through bind_param, we keep 
the exec_iters nil. Once user calls bind_param_array, we assign the 
array's size to the exec_iters. Since we have assumed that user should 
never call "Cursor[Key] = val" after bind_param_array, the exec_iters 
should exactly indicate the iteration count for cursor.exec.

Another modification is, I added a new parameter "VALUE exec_iters" to 
function oci8_stmt_execute in stmt.c.  The updated function 
oci8_stmt_execute in stmt.c  is:

static VALUE oci8_stmt_execute(VALUE self, VALUE exec_iters)
{
     .......
     if (oci8_get_ub2_attr(&stmt->base, OCI_ATTR_STMT_TYPE) == 
INT2FIX(OCI_STMT_SELECT)) {
        iters = 0;
        mode = OCI_DEFAULT;
     } else {
         if(!NIL_P(exec_iters))
         {
             iters=NUM2INT(exec_iters);
         }
         else
        {
             iters = 1;
        }
        mode = svcctx->is_autocommit ? OCI_COMMIT_ON_SUCCESS : OCI_DEFAULT;
    }
    ....
}

Meanwhile, the updated function OCI8::Cursor#exec in oci8.rb is:

  def exec(*bindvars)
      bind_params(*bindvars)
      __execute(@exec_iters)
      ........
  end

I haven't  fully tested the above modifications but I can insert String 
arrays without any problem.  The "Cursor[Key] = val" doesn't cause 
problem with bind_param, because the iteration count for a normal insert 
is always 1. In case of array insert, the iteration count changes with 
calling of "Cursor[Key] = val". Obviously it is unrealistic to make 
assumption as what I mentioned above. So I am thinking other ways to get 
the exact size of the array user has bound at the calling of cursor.exec.

Hope I am clear. Any ideas?