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
 
# 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
  __initialize(conn, sql) # Initialize the internal C structure.
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



203
204
205
206
 
# File 'lib/oci8/cursor.rb', line 203

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:



238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
 
# File 'lib/oci8/cursor.rb', line 238

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, Fixnum and Float are available, but Bignum is not supported. If its initial value is NULL, please set nil to type and Fixnum or Float to val.

example:

cursor.bind_param(1, 1234) # bind as Fixnum, Initial value is 1234.
cursor.bind_param(1, 1234.0) # bind as Float, Initial value is 1234.0.
cursor.bind_param(1, nil, Fixnum) # bind as Fixnum, Initial value is NULL.
cursor.bind_param(1, nil, Float) # bind as Float, 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()



91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
 
# File 'lib/oci8/cursor.rb', line 91

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


279
280
281
282
283
284
285
286
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
 
# File 'lib/oci8/cursor.rb', line 279

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.



363
364
365
366
367
 
# File 'lib/oci8/cursor.rb', line 363

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



358
359
360
 
# File 'lib/oci8/cursor.rb', line 358

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()



40
41
42
43
44
45
46
47
48
 
# File 'lib/oci8/cursor.rb', line 40

def define(pos, type, length = nil)
  bindobj = make_bind_object(:type => type, :length => length)
  __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.



122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/oci8/cursor.rb', line 122

def exec(*bindvars)
  bind_params(*bindvars)
  case type
  when :select_stmt
    __execute(0)
    define_columns()
  else
    __execute(1)
    row_count
  end
end

#exec_array ⇒ Object

Executes the SQL statement assigned the cursor with array binding



320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
 
# File 'lib/oci8/cursor.rb', line 320

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) 


147
148
149
150
151
152
153
154
155
156
 
# File 'lib/oci8/cursor.rb', line 147

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



162
163
164
165
166
167
168
169
170
 
# File 'lib/oci8/cursor.rb', line 162

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.



339
340
341
 
# File 'lib/oci8/cursor.rb', line 339

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



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

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



260
261
262
263
264
265
 
# File 'lib/oci8/cursor.rb', line 260

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 (Fixnum) —

    The number of rows to be prefetched



383
384
385
 
# File 'lib/oci8/cursor.rb', line 383

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

#row_count ⇒ Integer

Returns the number of processed rows.

Returns:

  • (Integer) 


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

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:



321
322
323
324
325
 
# File 'ext/oci8/stmt.c', line 321

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



417
418
419
420
421
 
# File 'lib/oci8/cursor.rb', line 417

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 Fixnum 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 Fixnums. (1, 2, 3, etc.)



440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
 
# File 'lib/oci8/cursor.rb', line 440

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