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

KUBO Takehiro kubo at jiubao.org
Tue Dec 11 11:37:02 EST 2007


Hi Liming,

On Dec 11, 2007 3:50 PM, Liming Lian <liming.lian at oracle.com> wrote:
> I have proposed the function prototype for bind_param_array. Please let
> me know what's your opinion on it.

Is this prototype is for PL/SQL binds or non-PL/SQL  binds?

If it is for PL/SQL binds,
> * max_array_size
>
> Maximum length for incoming array. If not specified, a default size will
> be used. (Need to discuss: how large the default value should be proper
> for max_array_size. The maximum number of rows allowed in an array DML
> statements is 4 gigabytes -1, but obviously we can't use that number here)

IMO, the default value should the length of var_array. If the parameter is used
only for input, var_array's length is the exact required size. If the
parameter is
used for output, we cannot know correct size. Users only know it.  Setting
proper size is a task of them.

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.

Consider a sample case inserting csv data to an Oracle table.

Without non-PL/SQL binds: execute a SQL per one line.
  conn = OCI8.new(user, pass)
  cursor = conn.parse('insert into csv_data values(:1, :2, :3)')
  cursor.bind_param(1, nil, String, 30)
  cursor.bind_param(2, nil, String, 30)
  cursor.bind_param(3, nil, String, 30)
  open('cvs_file', 'r') do |f|
    while line = f.gets
      col1, co2, col3 = line.split(',')
      cursor[1] = col1
      cursor[2] = col2
      cursor[3] = col3
      cursor.exec
    end
  end
  conn.commit

With non-PL/SQL binds: execute a SQL per 100 lines.
  conn = OCI8.new(user, pass)
  cursor = conn.parse('insert into csv_data values(:1, :2, :3)')
  max_array_size = 100
  cursor.bind_param_array(1, nil, String, 30, max_array_size)
  cursor.bind_param_array(2, nil, String, 30, max_array_size)
  cursor.bind_param_array(3, nil, String, 30, max_array_size)
  open('cvs_file', 'r') do |f|
    ary1 = Array.new(max_array_size)
    ary2 = Array.new(max_array_size)
    ary3 = Array.new(max_array_size)
    nitems = 0
    while line = f.gets
      col1, co2, col3 = line.split(',')
      ary1[nitems] = col1
      ary2[nitems] = col2
      ary3[nitems] = col3
      nitems += 1
      if nitems == max_array_size
        cursor[1] = ary1
        cursor[2] = ary2
        cursor[3] = ary3
        cursor.exec
        nitems = 0
      end
    end
    ... We have to call cursor.exec whose iteration count is nitems. ...
    ... But how to set the iteration count? ...
  end
  conn.commit

Hmm, it needs more time for us to think about this.


More information about the ruby-oci8-devel mailing list