Class: OCI8::Cursor

Inherits:

Object
OCIHandle
OCI8::Cursor

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.

Constant Summary

Constants inherited from OCIHandle

Class Method Summary collapse

Instance Method Summary collapse

#[](key) ⇒ Object

Gets the value of the bind variable.
#[]=(key, val) ⇒ Object

Sets the value to the bind variable.
#bind_param(key, param, type = nil, length = nil) ⇒ Object

Binds variables explicitly.
#bind_param_array(key, var_array, type = nil, max_item_length = nil) ⇒ Object

Bind array explicitly.
#close ⇒ Object

close the cursor.
#column_metadata ⇒ Object

call-seq: column_metadata -> column information.
#define(pos, type, length = nil) ⇒ Object

explicitly indicate the date type of fetched value.
#exec(*bindvars) ⇒ Object

Executes the SQL statement assigned the cursor.
#exec_array ⇒ Object

Executes the SQL statement assigned the cursor with array binding.
#fetch ⇒ Object

Gets fetched data as array.
#fetch_hash ⇒ Object

call-seq: fetch_hash.
#get_col_names ⇒ Object (also: #getColNames)

Gets the names of select-list as array.
#keys ⇒ an Array

Returns the keys of bind variables as array.
#max_array_size=(size) ⇒ Object

Set the maximum array size for bind_param_array.
#prefetch_rows=(aFixnum) ⇒ Object

Set number of rows to be prefetched.
#row_count ⇒ Object

Returns the number of processed rows.
#rowid ⇒ Object

Get the rowid of the last inserted/updated/deleted row.
#type ⇒ Object

gets the type of SQL statement as follows.

Class Method Details

.select_number_as ⇒ `Object`

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

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

# File 'ext/oci8/stmt.c', line 466

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.

# File 'ext/oci8/stmt.c', line 500

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

# File 'lib/oci8/oci8.rb', line 394

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

# File 'lib/oci8/oci8.rb', line 456

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.

# File 'lib/oci8/oci8.rb', line 551

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



531
532
533

# File 'lib/oci8/oci8.rb', line 531

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

# File 'lib/oci8/oci8.rb', line 348

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.

# File 'lib/oci8/oci8.rb', line 420

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

# File 'lib/oci8/oci8.rb', line 492

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

# File 'ext/oci8/stmt.c', line 316

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.

# File 'lib/oci8/oci8.rb', line 540

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.



511
512
513

# File 'lib/oci8/oci8.rb', line 511

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)

# File 'ext/oci8/stmt.c', line 534

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

# File 'lib/oci8/oci8.rb', line 437

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.

# File 'ext/oci8/stmt.c', line 564

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.

# File 'ext/oci8/stmt.c', line 396

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.

# File 'ext/oci8/stmt.c', line 416

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

# File 'ext/oci8/stmt.c', line 365

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;
    }
}

Class: OCI8::Cursor

Overview

Constant Summary

Constants inherited from OCIHandle

Class Method Summary collapse

Instance Method Summary collapse

Class Method Details

.select_number_as ⇒ Object

.select_number_as=(val) ⇒ Object

Instance Method Details

#[](key) ⇒ Object

#[]=(key, val) ⇒ Object

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

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

#close ⇒ Object

#column_metadata ⇒ Object

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

#exec(*bindvars) ⇒ Object

#exec_array ⇒ Object

#fetch ⇒ Object

#fetch_hash ⇒ Object

#get_col_names ⇒ Object Also known as: getColNames

#keys ⇒ an Array

#max_array_size=(size) ⇒ Object

#prefetch_rows=(aFixnum) ⇒ Object

#row_count ⇒ Object

#rowid ⇒ Object

#type ⇒ Object

.select_number_as ⇒ `Object`

.select_number_as=(val) ⇒ `Object`

#[](key) ⇒ `Object`

#[]=(key, val) ⇒ `Object`

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

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

#close ⇒ `Object`

#column_metadata ⇒ `Object`

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

#exec(*bindvars) ⇒ `Object`

#exec_array ⇒ `Object`

#fetch ⇒ `Object`

#fetch_hash ⇒ `Object`

#get_col_names ⇒ `Object` Also known as: getColNames

#keys ⇒ `an Array`

#max_array_size=(size) ⇒ `Object`

#prefetch_rows=(aFixnum) ⇒ `Object`

#row_count ⇒ `Object`

#rowid ⇒ `Object`

#type ⇒ `Object`