Class: OCI8::Cursor

Inherits:
OCIHandle show all
Defined in:
lib/oci8/oci8.rb,
lib/oci8/compat.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.

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from OCIHandle

#free

Class Method Details

.select_number_as ⇒ Object 



94
95
96
97
98
99
100
101
102
103
 
# File 'lib/oci8/compat.rb', line 94

def self.select_number_as
  case @@bind_unknown_number
  when OCI8::BindType::Fixnum
    return Fixnum
  when OCI8::BindType::Integer
    return Integer
  when OCI8::BindType::Float
    return Float
  end
end

.select_number_as=(val) ⇒ Object 



82
83
84
85
86
87
88
89
90
91
92
 
# File 'lib/oci8/compat.rb', line 82

def self.select_number_as=(val)
  if val == Fixnum
    @@bind_unknown_number = OCI8::BindType::Fixnum
  elsif val == Integer
    @@bind_unknown_number = OCI8::BindType::Integer
  elsif val == Float
    @@bind_unknown_number = OCI8::BindType::Float
  else
    raise ArgumentError, "must be Fixnum, Integer or Float"
  end
end

Instance Method Details

#[](key) ⇒ Object

Gets the value of the bind variable.

In case of binding explicitly, use same key with that of OCI8::Cursor#bind_param. A placeholder can be bound by name or position. If you bind by name, use that name. If you bind by position, use the position.

example:

cursor = conn.parse("BEGIN :out := 'BAR'; END;")
cursor.bind_param(':out', 'FOO') # bind by name
p cursor[':out'] # => 'FOO'
p cursor[1] # => nil
cursor.exec()
p cursor[':out'] # => 'BAR'
p cursor[1] # => nil

example:

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

In case of binding by OCI8#exec or OCI8::Cursor#exec, get the value by position, which starts from 1.

example:

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'



594
595
596
597
598
599
600
601
602
 
# File 'ext/oci8/stmt.c', line 594

static VALUE oci8_stmt_aref(VALUE self, VALUE key)
{
    oci8_stmt_t *stmt = DATA_PTR(self);
    VALUE obj = rb_hash_aref(stmt->binds, key);
    if (NIL_P(obj)) {
        return Qnil;
    }
    return oci8_bind_get_data(obj);
}

#[]=(key, val) ⇒ Object

Sets the value to the bind variable. The way to specify the key is same with OCI8::Cursor#[]. This is available to replace the value and execute many times.

example1:

cursor = conn.parse("INSERT INTO test(col1) VALUES(:1)")
cursor.bind_params(1, nil, String, 3)
['FOO', 'BAR', 'BAZ'].each do |key|
  cursor[1] = key
  cursor.exec
end
cursor.close()

example2:

['FOO', 'BAR', 'BAZ'].each do |key|
  conn.exec("INSERT INTO test(col1) VALUES(:1)", key)
end

Both example’s results are same. But the former will use less resources.



628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
 
# File 'ext/oci8/stmt.c', line 628

static VALUE oci8_stmt_aset(VALUE self, VALUE key, VALUE val)
{
    long max_array_size;
    long actual_array_size;
    long bind_array_size;

    oci8_stmt_t *stmt = DATA_PTR(self);
    VALUE obj = rb_hash_aref(stmt->binds, key);
    if (NIL_P(obj)) {
        return Qnil; /* ?? MUST BE ERROR? */
    }

    if(TYPE(val) == T_ARRAY) {
        max_array_size = NUM2INT(rb_ivar_get(self, id_at_max_array_size));
        actual_array_size = NUM2INT(rb_ivar_get(self, id_at_actual_array_size));
        bind_array_size = RARRAY_LEN(val);

        if(actual_array_size > 0 && bind_array_size != actual_array_size) {
            rb_raise(rb_eRuntimeError, "all binding arrays hould be the same size");        
        }
        if(bind_array_size <= max_array_size && actual_array_size == 0) {
            rb_ivar_set(self, id_at_actual_array_size, INT2NUM(bind_array_size));
        }
    }     
    oci8_bind_set_data(obj, val);
    return val;
}

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



353
354
355
356
357
358
359
360
361
362
363
 
# File 'lib/oci8/oci8.rb', line 353

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
  __bind(key, make_bind_object(param))
  self
end

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

Bind 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

example:

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



415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
 
# File 'lib/oci8/oci8.rb', line 415

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]
  raise "unsupported dataType: #{type}" if bindclass.nil?
  bindobj = bindclass.create(@con, var_array, param, @max_array_size)
  __bind(key, bindobj)
  self
end

#close ⇒ Object

close the cursor.



506
507
508
509
510
 
# File 'lib/oci8/oci8.rb', line 506

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

#column_metadata ⇒ Object

call-seq:

column_metadata -> column information

(new in 1.0.0 and 2.0)

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

example:

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



486
487
488
 
# File 'lib/oci8/oci8.rb', line 486

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



307
308
309
310
 
# File 'lib/oci8/oci8.rb', line 307

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



379
380
381
382
383
384
385
386
387
388
 
# File 'lib/oci8/oci8.rb', line 379

def exec(*bindvars)
  bind_params(*bindvars)
  __execute(nil) # Pass a nil to specify the statement isn't an Array DML
  case type
  when :select_stmt
    define_columns()
  else
    row_count
  end
end

#exec_array ⇒ Object

Executes the SQL statement assigned the cursor with array binding



447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
 
# File 'lib/oci8/oci8.rb', line 447

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 ⇒ Object

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

example:

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



448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
 
# File 'ext/oci8/stmt.c', line 448

static VALUE oci8_stmt_fetch(VALUE self)
{
    oci8_stmt_t *stmt = DATA_PTR(self);
    oci8_svcctx_t *svcctx = oci8_get_svcctx(stmt->svc);

    if (rb_block_given_p()) {
        for (;;) {
            VALUE rs = oci8_stmt_do_fetch(stmt, svcctx);
            if (NIL_P(rs))
                return self; /* NEED TO CHECK 0.1 behavior. */
            rb_yield(rs);
        }
    } else {
        return oci8_stmt_do_fetch(stmt, svcctx);
    }
}

#fetch_hash ⇒ Object

call-seq:

fetch_hash

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



495
496
497
498
499
500
501
502
503
 
# File 'lib/oci8/oci8.rb', line 495

def fetch_hash
  if iterator?
    while ret = fetch_a_hash_row()
      yield(ret)
    end
  else
    fetch_a_hash_row
  end
end

#get_col_names ⇒ Object Also known as: getColNames

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



466
467
468
 
# File 'lib/oci8/oci8.rb', line 466

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

#keys ⇒ an Array

Returns the keys of bind variables as array.

Returns:

  • (an Array) 


662
663
664
665
666
 
# File 'ext/oci8/stmt.c', line 662

static VALUE oci8_stmt_keys(VALUE self)
{
    oci8_stmt_t *stmt = DATA_PTR(self);
    return rb_funcall(stmt->binds, oci8_id_keys, 0);
}

#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



396
397
398
399
400
401
 
# File 'lib/oci8/oci8.rb', line 396

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

#prefetch_rows=(aFixnum) ⇒ Object

Set 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.



692
693
694
695
696
697
698
699
 
# File 'ext/oci8/stmt.c', line 692

static VALUE oci8_stmt_set_prefetch_rows(VALUE self, VALUE rows)
{
    oci8_stmt_t *stmt = DATA_PTR(self);
    ub4 num = NUM2UINT(rows);

    oci_lc(OCIAttrSet(stmt->base.hp.ptr, OCI_HTYPE_STMT, &num, 0, OCI_ATTR_PREFETCH_ROWS, oci8_errhp));
    return Qfalse;
}

#row_count ⇒ Object

Returns the number of processed rows.



527
528
529
530
 
# File 'ext/oci8/stmt.c', line 527

static VALUE oci8_stmt_get_row_count(VALUE self)
{
    return oci8_get_ub4_attr(DATA_PTR(self), OCI_ATTR_ROW_COUNT);
}

#rowid ⇒ Object

Get the rowid of the last inserted/updated/deleted row. This cannot be used for select statements.

example:

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

Changes between ruby-oci8 1.0.3 and 1.0.4.

ruby-oci8 1.0.4 or upper

The return value is a String.

ruby-oci8 1.0.3 or lower

It returns an OCIRowid object.



546
547
548
549
 
# File 'ext/oci8/stmt.c', line 546

static VALUE oci8_stmt_get_rowid(VALUE self)
{
    return oci8_get_rowid_attr(DATA_PTR(self), OCI_ATTR_ROWID);
}

#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.)



497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
 
# File 'ext/oci8/stmt.c', line 497

static VALUE oci8_stmt_get_stmt_type(VALUE self)
{
    VALUE stmt_type = oci8_get_ub2_attr(DATA_PTR(self), OCI_ATTR_STMT_TYPE);
    switch (FIX2INT(stmt_type)) {
    case OCI_STMT_SELECT:
        return oci8_sym_select_stmt;
    case OCI_STMT_UPDATE:
        return oci8_sym_update_stmt;
    case OCI_STMT_DELETE:
        return oci8_sym_delete_stmt;
    case OCI_STMT_INSERT:
        return oci8_sym_insert_stmt;
    case OCI_STMT_CREATE:
        return oci8_sym_create_stmt;
    case OCI_STMT_DROP:
        return oci8_sym_drop_stmt;
    case OCI_STMT_ALTER:
        return oci8_sym_alter_stmt;
    case OCI_STMT_BEGIN:
        return oci8_sym_begin_stmt;
    case OCI_STMT_DECLARE:
        return oci8_sym_declare_stmt;
    default:
        return stmt_type;
    }
}