Class: OCI8::Cursor

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

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'



478
479
480
481
482
483
484
485
486
 
# File 'ext/oci8/stmt.c', line 478

static VALUE oci8_stmt_aref(VALUE self, VALUE key)
{
    oci8_stmt_t *stmt = TO_STMT(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.



512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
 
# File 'ext/oci8/stmt.c', line 512

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



445
446
447
448
449
450
451
452
453
454
455
 
# File 'lib/oci8/oci8.rb', line 445

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



507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
 
# File 'lib/oci8/oci8.rb', line 507

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)
  self
end

#close ⇒ Object

close the cursor.



602
603
604
605
606
 
# File 'lib/oci8/oci8.rb', line 602

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



582
583
584
 
# File 'lib/oci8/oci8.rb', line 582

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



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

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.



471
472
473
474
475
476
477
478
479
480
 
# File 'lib/oci8/oci8.rb', line 471

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



543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
 
# File 'lib/oci8/oci8.rb', line 543

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



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

static VALUE oci8_stmt_fetch(VALUE self)
{
    oci8_stmt_t *stmt = TO_STMT(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.



591
592
593
594
595
596
597
598
599
 
# File 'lib/oci8/oci8.rb', line 591

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

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



562
563
564
 
# File 'lib/oci8/oci8.rb', line 562

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) 


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

static VALUE oci8_stmt_keys(VALUE self)
{
    oci8_stmt_t *stmt = TO_STMT(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



488
489
490
491
492
493
 
# File 'lib/oci8/oci8.rb', line 488

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.



576
577
578
579
580
581
582
583
584
 
# File 'ext/oci8/stmt.c', line 576

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

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

#row_count ⇒ Object

Returns the number of processed rows.



408
409
410
411
412
 
# File 'ext/oci8/stmt.c', line 408

static VALUE oci8_stmt_get_row_count(VALUE self)
{
    oci8_base_t *base = oci8_get_handle(self, cOCIStmt);
    return oci8_get_ub4_attr(base, OCI_ATTR_ROW_COUNT, base->hp.stmt);
}

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



428
429
430
431
432
 
# File 'ext/oci8/stmt.c', line 428

static VALUE oci8_stmt_get_rowid(VALUE self)
{
    oci8_base_t *base = oci8_get_handle(self, cOCIStmt);
    return oci8_get_rowid_attr(base, OCI_ATTR_ROWID, base->hp.stmt);
}

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



377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
 
# File 'ext/oci8/stmt.c', line 377

static VALUE oci8_stmt_get_stmt_type(VALUE self)
{
    oci8_base_t *base = oci8_get_handle(self, cOCIStmt);
    VALUE stmt_type = oci8_get_ub2_attr(base, OCI_ATTR_STMT_TYPE, base->hp.stmt);
    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;
    }
}