Class: OCI8::Cursor
- 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
-
#[](key) ⇒ Object
Gets the value of the bind variable.
-
#[]=(key, val) ⇒ Object
Changes the bind variable value.
-
#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
Binds array explicitly.
-
#close ⇒ Object
close the cursor.
-
#column_metadata ⇒ Array of OCI8::Metadata::Column
Gets an array of OCI8::Metadata::Column of a select statement.
-
#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 ⇒ Array
Gets fetched data as array.
-
#fetch_hash ⇒ Hash
Gets fetched data as a Hash.
-
#get_col_names ⇒ Object
Gets the names of select-list as array.
-
#initialize(conn, sql = nil) ⇒ Cursor
constructor
A new instance of Cursor.
-
#keys ⇒ Array
Returns the keys of bind variables.
-
#max_array_size=(size) ⇒ Object
Set the maximum array size for bind_param_array.
-
#prefetch_rows=(rows) ⇒ Object
Set the number of rows to be prefetched.
-
#row_count ⇒ Integer
Returns the number of processed rows.
-
#rowid ⇒ String
Gets the rowid of the last inserted, updated or deleted row.
-
#statement ⇒ String
Returns the text of the SQL statement prepared in the cursor.
-
#type ⇒ Object
gets the type of SQL statement as follows.
Constructor Details
#initialize(conn, sql = nil) ⇒ Cursor
Don’t use this constructor. Use OCI8#parse instead.
Returns a new instance of Cursor.
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.
205 206 207 208 |
# File 'lib/oci8/cursor.rb', line 205 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.
240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 |
# File 'lib/oci8/cursor.rb', line 240 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()
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
# File 'lib/oci8/cursor.rb', line 92 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
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 318 319 |
# File 'lib/oci8/cursor.rb', line 281 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.
365 366 367 368 369 |
# File 'lib/oci8/cursor.rb', line 365 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.
360 361 362 |
# File 'lib/oci8/cursor.rb', line 360 def @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.
123 124 125 126 127 128 129 130 131 132 133 134 |
# File 'lib/oci8/cursor.rb', line 123 def exec(*bindvars) bind_params(*bindvars) case type when :select_stmt __execute(0) define_columns() if @column_metadata.size == 0 @column_metadata.size else __execute(1) row_count end end |
#exec_array ⇒ Object
Executes the SQL statement assigned the cursor with array binding
322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 |
# File 'lib/oci8/cursor.rb', line 322 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.
149 150 151 152 153 154 155 156 157 158 |
# File 'lib/oci8/cursor.rb', line 149 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.
164 165 166 167 168 169 170 171 172 |
# File 'lib/oci8/cursor.rb', line 164 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.
341 342 343 |
# File 'lib/oci8/cursor.rb', line 341 def get_col_names @names ||= @column_metadata.collect { |md| md.name } end |
#keys ⇒ Array
Returns the keys of bind variables.
374 375 376 |
# File 'lib/oci8/cursor.rb', line 374 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
262 263 264 265 266 267 |
# File 'lib/oci8/cursor.rb', line 262 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.
385 386 387 |
# File 'lib/oci8/cursor.rb', line 385 def prefetch_rows=(rows) attr_set_ub4(11, rows) # OCI_ATTR_PREFETCH_ROWS(11) end |
#row_count ⇒ Integer
Returns the number of processed rows.
393 394 395 396 |
# File 'lib/oci8/cursor.rb', line 393 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.
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
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.
419 420 421 422 423 |
# File 'lib/oci8/cursor.rb', line 419 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.)
442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 |
# File 'lib/oci8/cursor.rb', line 442 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 |