Class: OCI8::Cursor

Inherits:
OCIHandle show all
Defined in:
lib/oci8/cursor.rb,
ext/oci8/stmt.c

Overview

The instance of this class corresponds to cursor in the term of Oracle, which corresponds to java.sql.Statement of JDBC and statement handle $sth of Perl/DBI.

Don’t create the instance by calling ‘new’ method. Please create it by calling OCI8#exec or OCI8#parse.

Instance Method Summary collapse

Constructor Details

#initialize(conn, sql = nil) ⇒ Cursor

Note:

Don’t use this constructor. Use OCI8#parse instead.

Returns a new instance of Cursor.

Parameters:

  • conn (OCI8) —

    connection

  • sql (String) (defaults to: nil) —

    SQL statement



21
22
23
24
25
26
27
28
29
30
31
32
33
 
# File 'lib/oci8/cursor.rb', line 21

def initialize(conn, sql = nil)
  @bind_handles = {}
  @define_handles = []
  @column_metadata = []
  @names = nil
  @con = conn
  @max_array_size = nil
  @fetch_array_size = nil
  @rowbuf_size = 0
  @rowbuf_index = 0
  __initialize(conn, sql) # Initialize the internal C structure.
  self.prefetch_rows = conn.instance_variable_get(:@prefetch_rows)
end

Instance Method Details

#[](key) ⇒ Object

Gets the value of the bind variable.

When bind variables are explicitly bound by #bind_param, the subscript key must be same with the parameter key passed to #bind_param.

When they are implicitly bound by OCI8#exec or #exec, the subscript key is the position which starts from one.

Examples:

explicitly bind by name

cursor = conn.parse("BEGIN :out := 'BAR'; END;")
cursor.bind_param(:out, 'FOO') # bind by name
p cursor[:out] # => 'FOO'  - The subscript must be :out.
cursor.exec()
p cursor[:out] # => 'BAR'

explicitly bind by position

cursor = conn.parse("BEGIN :out := 'BAR'; END;")
cursor.bind_param(1, 'FOO') # bind by position
p cursor[1] # => 'FOO'  - The subscript must be 1.
cursor.exec()
p cursor[1] # => 'BAR'

implicitly bind

cursor = conn.exec("BEGIN :out := 'BAR'; END;", 'FOO')
# 1st bind variable is bound as String with width 3. Its initial value is 'FOO'
# After execute, the value become 'BAR'.
p cursor[1] # => 'BAR'

Parameters:

Returns:

  • (Object) —

    the value of the bind variable



211
212
213
214
 
# File 'lib/oci8/cursor.rb', line 211

def [](key)
  handle = @bind_handles[key]
  handle && handle.send(:get_data)
end

#[]=(key, val) ⇒ Object

Changes the bind variable value.

When bind variables are explicitly bound by #bind_param, the subscript key must be same with the parameter key passed to #bind_param.

When they are implicitly bound by OCI8#exec or #exec, the subscript key is the position which starts from one.

Examples:

# Inserts three rows whose values are 'FOO', 'BAR' and 'BAZ.'
cursor = conn.parse("INSERT INTO test(col1) VALUES(:1)")
begin
  cursor.bind_params(1, nil, String, 3)
  ['FOO', 'BAR', 'BAZ'].each do |column_value|
    cursor[1] = column_value  # Change the bind value
    cursor.exec               # and insert it.
  end
ensure
  cursor.close()
end
# This makes same result with the following but is more efficient.
#
#  ['FOO', 'BAR', 'BAZ'].each do |column_value|
#    conn.exec("INSERT INTO test(col1) VALUES(:1)", column_value)
#  end
#

Parameters:



246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
 
# File 'lib/oci8/cursor.rb', line 246

def []=(key, val)
  handle = @bind_handles[key]
  return nil if handle.nil?

  if val.is_a? Array
    if @actual_array_size > 0 && val.length != @actual_array_size
      raise RuntimeError, "all binding arrays hould be the same size"
    end
    if @actual_array_size == 0 && val.length <= @max_array_size
      @actual_array_size = val.length
    end
  end
  handle.send(:set_data, val)
  val
end

#bind_param(key, param, type = nil, length = nil) ⇒ Object

Binds variables explicitly.

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("SELECT * FROM emp WHERE ename = :ename")
cursor.bind_param(1, 'SMITH') # bind by position
# ...or...
cursor.bind_param(':ename', 'SMITH') # bind by name

To bind as number, set the number intself to val. If its initial value is NULL, please set nil to type and Integer, Float or OraNumber to val.

example:

cursor.bind_param(1, 1234) # bind as Integer, Initial value is 1234.
cursor.bind_param(1, 1234.0) # bind as Float, Initial value is 1234.0.
cursor.bind_param(1, nil, Integer) # bind as Integer, Initial value is NULL.
cursor.bind_param(1, nil, Float) # bind as Float, Initial value is NULL.
cursor.bind_param(1, OraNumber(1234)) # bind as OraNumber, Initial value is 1234.
cursor.bind_param(1, nil, OraNumber) # bind as OraNumber, Initial value is NULL.

In case of binding a string, set the string itself to val. When the bind variable is used as output, set the string whose length is enough to store or set the length.

example:

cursor = conn.parse("BEGIN :out := :in || '_OUT'; END;")
cursor.bind_param(':in', 'DATA') # bind as String with width 4.
cursor.bind_param(':out', nil, String, 7) # bind as String with width 7.
cursor.exec()
p cursor[':out'] # => 'DATA_OU'
# Though the length of :out is 8 bytes in PL/SQL block, it is
# bound as 7 bytes. So result is cut off at 7 byte.

In case of binding a string as RAW, set OCI::RAW to type.

example:

cursor = conn.parse("INSERT INTO raw_table(raw_column) VALUE (:1)")
cursor.bind_param(1, 'RAW_STRING', OCI8::RAW)
cursor.exec()
cursor.close()



96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
 
# File 'lib/oci8/cursor.rb', line 96

def bind_param(key, param, type = nil, length = nil)
  case param
  when Hash
  when Class
    param = {:value => nil,   :type => param, :length => length}
  else
    param = {:value => param, :type => type,  :length => length}
  end
  bindobj = make_bind_object(param)
  __bind(key, bindobj)
  if old = @bind_handles[key]
    old.send(:free)
  end
  @bind_handles[key] = bindobj
  self
end

#bind_param_array(key, var_array, type = nil, max_item_length = nil) ⇒ Object

Binds array explicitly

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

The max_array_size should be set before calling bind_param_array

Examples:

cursor = conn.parse("INSERT INTO test_table VALUES (:str)")
cursor.max_array_size = 3
cursor.bind_param_array(1, ['happy', 'new', 'year'], String, 30)
cursor.exec_array


287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
 
# File 'lib/oci8/cursor.rb', line 287

def bind_param_array(key, var_array, type = nil, max_item_length = nil)
  raise "please call max_array_size= first." if @max_array_size.nil?
  raise "expect array as input param for bind_param_array." if !var_array.nil? && !(var_array.is_a? Array) 
  raise "the size of var_array should not be greater than max_array_size." if !var_array.nil? && var_array.size > @max_array_size

  if var_array.nil? 
    raise "all binding arrays should be the same size." unless @actual_array_size.nil? || @actual_array_size == 0
    @actual_array_size = 0
  else
    raise "all binding arrays should be the same size." unless @actual_array_size.nil? || var_array.size == @actual_array_size
    @actual_array_size = var_array.size if @actual_array_size.nil?
  end
  
  param = {:value => var_array, :type => type, :length => max_item_length, :max_array_size => @max_array_size}
  first_non_nil_elem = var_array.nil? ? nil : var_array.find{|x| x!= nil}
  
  if type.nil?
    if first_non_nil_elem.nil?
      raise "bind type is not given."
    else
      type = first_non_nil_elem.class
    end
  end
  
  bindclass = OCI8::BindType::Mapping[type]
  if bindclass.nil? and type.is_a? Class
    bindclass = OCI8::BindType::Mapping[type.to_s]
    OCI8::BindType::Mapping[type] = bindclass if bindclass
  end
  raise "unsupported dataType: #{type}" if bindclass.nil?
  bindobj = bindclass.create(@con, var_array, param, @max_array_size)
  __bind(key, bindobj)
  #
  if old = @bind_handles[key]
    old.send(:free)
  end
  @bind_handles[key] = bindobj
  self
end

#close ⇒ Object

close the cursor.



371
372
373
374
375
 
# File 'lib/oci8/cursor.rb', line 371

def close
  free()
  @names = nil
  @column_metadata = nil
end

#column_metadata ⇒ Array of OCI8::Metadata::Column

Gets an array of OCI8::Metadata::Column of a select statement.

Examples:

cursor = conn.exec('select * from tab')
puts ' Name                                      Type'
puts ' ----------------------------------------- ----------------------------'
cursor.column_metadata.each do |colinfo|
  puts format(' %-41s %s',
              colinfo.name,
              colinfo.type_string)
end

Returns:

Since:

  • 1.0.0



366
367
368
 
# File 'lib/oci8/cursor.rb', line 366

def column_metadata
  @column_metadata
end

#define(pos, type, length = nil) ⇒ Object

explicitly indicate the date type of fetched value. run this method within parse and exec. pos starts from 1. lentgh is used when type is String.

example:

cursor = conn.parse("SELECT ename, hiredate FROM emp")
cursor.define(1, String, 20) # fetch the first column as String.
cursor.define(2, Time)       # fetch the second column as Time.
cursor.exec()



44
45
46
47
48
49
50
51
52
 
# File 'lib/oci8/cursor.rb', line 44

def define(pos, type, length = nil)
  bindobj = make_bind_object({:type => type, :length => length}, @fetch_array_size || 1)
  __define(pos, bindobj)
  if old = @define_handles[pos - 1]
    old.send(:free)
  end
  @define_handles[pos - 1] = bindobj
  self
end

#exec(*bindvars) ⇒ Object

Executes the SQL statement assigned the cursor. The type of return value depends on the type of sql statement: select; insert, update and delete; create, alter, drop and PL/SQL.

In case of select statement, it returns the number of the select-list.

In case of insert, update or delete statement, it returns the number of processed rows.

In case of create, alter, drop and PL/SQL statement, it returns true. In contrast with OCI8#exec, it returns true even though PL/SQL. Use OCI8::Cursor#[] explicitly to get bind variables.



127
128
129
130
131
132
133
134
135
136
137
138
139
140
 
# File 'lib/oci8/cursor.rb', line 127

def exec(*bindvars)
  bind_params(*bindvars)
  case type
  when :select_stmt
    __execute(0)
    define_columns() if @column_metadata.size == 0
    @rowbuf_size = 0
    @rowbuf_index = 0
    @column_metadata.size
  else
    __execute(1)
    row_count
  end
end

#exec_array ⇒ Object

Executes the SQL statement assigned the cursor with array binding



328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
 
# File 'lib/oci8/cursor.rb', line 328

def exec_array
  raise "please call max_array_size= first." if @max_array_size.nil?

  if !@actual_array_size.nil? && @actual_array_size > 0
    __execute(@actual_array_size)
  else
    raise "please set non-nil values to array binding parameters"
  end

  case type
  when :update_stmt, :delete_stmt, :insert_stmt
    row_count
  else
    true
  end
end

#fetch ⇒ Array

Gets fetched data as array. This is available for select statement only.

Examples:

conn = OCI8.new('scott', 'tiger')
cursor = conn.exec('SELECT * FROM emp')
while r = cursor.fetch()
  puts r.join(',')
end
cursor.close
conn.logoff

Returns:

  • (Array) 


155
156
157
158
159
160
161
162
163
164
 
# File 'lib/oci8/cursor.rb', line 155

def fetch
  if block_given?
    while row = fetch_one_row_as_array
      yield row
    end
    self
  else
    fetch_one_row_as_array
  end
end

#fetch_hash ⇒ Hash

Gets fetched data as a Hash. The hash keys are column names. If a block is given, acts as an iterator.

Returns:

  • (Hash) —

    the hash keys are column names and hash values are column values



170
171
172
173
174
175
176
177
178
 
# File 'lib/oci8/cursor.rb', line 170

def fetch_hash
  if block_given?
    while row = fetch_one_row_as_hash()
      yield row
    end
  else
    fetch_one_row_as_hash
  end
end

#get_col_names ⇒ Object

Gets the names of select-list as array. Please use this method after exec.



347
348
349
 
# File 'lib/oci8/cursor.rb', line 347

def get_col_names
  @names ||= @column_metadata.collect { |md| md.name }
end

#keys ⇒ Array

Returns the keys of bind variables.

Returns:

  • (Array) —

    bind variable keys



380
381
382
 
# File 'lib/oci8/cursor.rb', line 380

def keys
  @bind_handles.keys
end

#max_array_size=(size) ⇒ Object

Set the maximum array size for bind_param_array

All the binds will be clean from cursor if instance variable max_array_size is set before

Instance variable actual_array_size holds the size of the arrays users actually binds through bind_param_array

all the binding arrays are required to be the same size



268
269
270
271
272
273
 
# File 'lib/oci8/cursor.rb', line 268

def max_array_size=(size)
  raise "expect positive number for max_array_size." if size.nil? && size <=0
  free_bind_handles()  if !@max_array_size.nil?
  @max_array_size = size
  @actual_array_size = nil
end

#prefetch_rows=(rows) ⇒ Object

Set the number of rows to be prefetched. This can reduce the number of network round trips when fetching many rows. The default value is one.

FYI: Rails oracle adaptor uses 100 by default.

Parameters:

  • rows (Integer) —

    The number of rows to be prefetched



391
392
393
394
 
# File 'lib/oci8/cursor.rb', line 391

def prefetch_rows=(rows)
  attr_set_ub4(11, rows) # OCI_ATTR_PREFETCH_ROWS(11)
  @prefetch_rows = rows
end

#row_count ⇒ Integer

Returns the number of processed rows.

Returns:

  • (Integer) 


400
401
402
403
 
# File 'lib/oci8/cursor.rb', line 400

def row_count
  # https://docs.oracle.com/database/121/LNOCI/ociaahan.htm#sthref5774
  attr_get_ub8(457) # OCI_ATTR_UB8_ROW_COUNT(457)
end

#rowid ⇒ String

Gets the rowid of the last inserted, updated or deleted row. This cannot be used for select statements.

Examples:

cursor = conn.parse('INSERT INTO foo_table values(:1, :2)', 1, 2)
cursor.exec
cursor.rowid # => "AAAFlLAAEAAAAG9AAA", the inserted row's rowid

Returns:



356
357
358
359
360
 
# File 'ext/oci8/stmt.c', line 356

static VALUE oci8_stmt_get_rowid(VALUE self)
{
    oci8_base_t *base = oci8_check_typeddata(self, &oci8_stmt_data_type, 1);
    return oci8_get_rowid_attr(base, OCI_ATTR_ROWID, base->hp.stmt);
}

#statement ⇒ String

Note:

When NCHAR String Literal Replacement is turned on, it returns the modified SQL text, instead of the original SQL text.

Returns the text of the SQL statement prepared in the cursor.

Examples:

cursor = conn.parse("select * from country where country_code = 'ja'")
cursor.statement # => "select * from country where country_code = 'ja'"

Returns:

  • (String) —

    prepared SQL statement

Since:

  • 2.1.3



426
427
428
429
430
 
# File 'lib/oci8/cursor.rb', line 426

def statement
  # The magic number 144 is OCI_ATTR_STATEMENT.
  # See http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/ociaahan.htm#sthref5503
  attr_get_string(144)
end

#type ⇒ Object

gets the type of SQL statement as follows.

  • OCI8::STMT_SELECT

  • OCI8::STMT_UPDATE

  • OCI8::STMT_DELETE

  • OCI8::STMT_INSERT

  • OCI8::STMT_CREATE

  • OCI8::STMT_DROP

  • OCI8::STMT_ALTER

  • OCI8::STMT_BEGIN (PL/SQL block which starts with a BEGIN keyword)

  • OCI8::STMT_DECLARE (PL/SQL block which starts with a DECLARE keyword)

  • Other Integer value undocumented in Oracle manuals.

Changes between ruby-oci8 1.0 and 2.0.

ruby-oci8 2.0

OCI8::STMT_* are Symbols. (:select_stmt, :update_stmt, etc.)

ruby-oci8 1.0

OCI8::STMT_* are Integers. (1, 2, 3, etc.)



449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
 
# File 'lib/oci8/cursor.rb', line 449

def type
  # http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/ociaahan.htm#sthref5506
  stmt_type = attr_get_ub2(24) # OCI_ATTR_STMT_TYPE(24)
  case stmt_type
  when 1 # OCI_STMT_SELECT
    :select_stmt
  when 2 # OCI_STMT_UPDATE
    :update_stmt
  when 3 # OCI_STMT_DELETE
    :delete_stmt
  when 4 # OCI_STMT_INSERT
    :insert_stmt
  when 5 # OCI_STMT_CREATE
    :create_stmt
  when 6 # OCI_STMT_DROP
    :drop_stmt
  when 7 # OCI_STMT_ALTER
    :alter_stmt
  when 8 # OCI_STMT_BEGIN
    :begin_stmt
  when 9 # OCI_STMT_DECLARE
    :declare_stmt
  else
    stmt_type
  end
end