class OCI8::Cursor
def self.select_number_as
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
def self.select_number_as=(val)
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
def [](key)
-
(Object)
- the value of the bind variable
Parameters:
-
key
(Object
) -- bind key
Other tags:
- Example: implicitly bind -
Example: explicitly bind by position -
Example: explicitly bind by name -
def [](key) handle = @bind_handles[key] handle && handle.send(:get_data) end
def []=(key, val)
-
val
(Object
) -- bind value -
key
(Object
) -- bind key
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
def bind_param(key, param, type = nil, length = nil)
cursor.exec()
cursor.bind_param(1, 'RAW_STRING', OCI8::RAW)
cursor = conn.parse("INSERT INTO raw_table(raw_column) VALUE (:1)")
example:
In case of binding a string as RAW, set OCI::RAW to +type+.
# bound as 7 bytes. So result is cut off at 7 byte.
# Though the length of :out is 8 bytes in PL/SQL block, it is
p cursor[':out'] # => 'DATA_OU'
cursor.exec()
cursor.bind_param(':out', nil, String, 7) # bind as String with width 7.
cursor.bind_param(':in', 'DATA') # bind as String with width 4.
cursor = conn.parse("BEGIN :out := :in || '_OUT'; END;")
example:
string whose length is enough to store or set the length.
+val+. When the bind variable is used as output, set the
In case of binding a string, set the string itself to
cursor.bind_param(1, nil, OraNumber) # bind as OraNumber, Initial value is NULL.
cursor.bind_param(1, OraNumber(1234)) # bind as OraNumber, Initial value is 1234.
cursor.bind_param(1, nil, Float) # bind as Float, Initial value is NULL.
cursor.bind_param(1, nil, Integer) # bind as Integer, Initial value is NULL.
cursor.bind_param(1, 1234.0) # bind as Float, Initial value is 1234.0.
cursor.bind_param(1, 1234) # bind as Integer, Initial value is 1234.
example:
is NULL, please set nil to +type+ and Integer, Float or OraNumber to +val+.
To bind as number, set the number intself to +val+. If its initial value
cursor.bind_param(':ename', 'SMITH') # bind by name
# ...or...
cursor.bind_param(1, 'SMITH') # bind by position
cursor = conn.parse("SELECT * FROM emp WHERE ename = :ename")
example:
When key is string, it binds by the name of placeholder.
When key is number, it binds by position, which starts from 1.
Binds variables explicitly.
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
def bind_param_array(key, var_array, type = nil, max_item_length = nil)
cursor.bind_param_array(1, ['happy', 'new', 'year'], String, 30)
cursor.max_array_size = 3
cursor = conn.parse("INSERT INTO test_table VALUES (:str)")
@example
The max_array_size should be set before calling bind_param_array
When key is string, it binds by the name of placeholder.
When key is number, it binds by position, which starts from 1.
Binds array explicitly
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
def bind_params(*bindvars)
def bind_params(*bindvars) bindvars.each_with_index do |val, i| if val.is_a? Array bind_param(i + 1, val[0], val[1], val[2]) else bind_param(i + 1, val) end end end
def close
def close free() @names = nil @column_metadata = nil end
def column_metadata
- Since: - 1.0.0
Returns:
-
(Array of OCI8::Metadata::Column)
-
def column_metadata @column_metadata end
def define(pos, type, length = nil)
cursor.define(2, Time) # fetch the second column as Time.
cursor.define(1, String, 20) # fetch the first column as String.
cursor = conn.parse("SELECT ename, hiredate FROM emp")
example:
when type is String.
method within parse and exec. pos starts from 1. lentgh is used
explicitly indicate the date type of fetched value. run this
def define(pos, type, length = nil) bindobj = make_bind_object({:type => type, :length => length}, @fetch_array_size || 1) __define(pos, bindobj) if old = @define_handles[pos - 1] old.send(:free) end @define_handles[pos - 1] = bindobj self end
def define_columns
def define_columns # http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/ociaahan.htm#sthref5494 num_cols = attr_get_ub4(18) # OCI_ATTR_PARAM_COUNT(18) @column_metadata = 1.upto(num_cols).collect do |i| __paramGet(i) end if @define_handles.size == 0 use_array_fetch = @@use_array_fetch @column_metadata.each do |md| case md.data_type when :clob, :blob, :bfile # Rows prefetching doesn't work for CLOB, BLOB and BFILE. # Use array fetching to get more than one row in a network round trip. use_array_fetch = true end end @fetch_array_size = @prefetch_rows if use_array_fetch end @column_metadata.each_with_index do |md, i| define_one_column(i + 1, md) unless @define_handles[i] end num_cols end
def define_one_column(pos, param)
def define_one_column(pos, param) bindobj = make_bind_object(param, @fetch_array_size || 1) __define(pos, bindobj) @define_handles[pos - 1] = bindobj end
def exec(*bindvars)
though PL/SQL. Use OCI8::Cursor#[] explicitly to get bind
true. In contrast with OCI8#exec, it returns true even
In case of create, alter, drop and PL/SQL statement, it returns
number of processed rows.
In case of insert, update or delete statement, it returns the
select-list.
In case of select statement, it returns the number of the
insert, update and delete; create, alter, drop and PL/SQL.
return value depends on the type of sql statement: select;
Executes the SQL statement assigned the cursor. The type of
def exec(*bindvars) bind_params(*bindvars) case type when :select_stmt __execute(0) define_columns() if @column_metadata.size == 0 @rowbuf_size = 0 @rowbuf_index = 0 @column_metadata.size else __execute(1) row_count end end
def exec_array
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
def fetch
-
(Array)
-
def fetch if block_given? while row = fetch_one_row_as_array yield row end self else fetch_one_row_as_array end end
def fetch_hash
-
(Hash)
- the hash keys are column names and hash values are column values
def fetch_hash if block_given? while row = fetch_one_row_as_hash() yield row end else fetch_one_row_as_hash end end
def fetch_one_row_as_array
def fetch_one_row_as_array if fetch_row_internal ret = @define_handles.collect do |handle| handle.send(:get_data, @rowbuf_index) end @rowbuf_index += 1 ret else nil end end
def fetch_one_row_as_hash
def fetch_one_row_as_hash if fetch_row_internal ret = {} get_col_names.each_with_index do |name, idx| ret[name] = @define_handles[idx].send(:get_data, @rowbuf_index) end @rowbuf_index += 1 ret else nil end end
def fetch_row_internal
def fetch_row_internal if @rowbuf_size && @rowbuf_size == @rowbuf_index @rowbuf_size = __fetch(@con, @fetch_array_size || 1) @rowbuf_index = 0 end @rowbuf_size end
def free_bind_handles
def free_bind_handles @bind_handles.each_value do |val| val.send(:free) end @bind_handles.clear end
def get_col_names
Gets the names of select-list as array. Please use this
def get_col_names @names ||= @column_metadata.collect { |md| md.name } end
def initialize(conn, sql = nil)
-
sql
(String
) -- SQL statement -
conn
(OCI8
) -- connection
Other tags:
- Note: - Don't use this constructor. Use {OCI8#parse} instead.
def initialize(conn, sql = nil) @bind_handles = {} @define_handles = [] @column_metadata = [] @names = nil @con = conn @max_array_size = nil @fetch_array_size = nil @rowbuf_size = 0 @rowbuf_index = 0 __initialize(conn, sql) # Initialize the internal C structure. self.prefetch_rows = conn.instance_variable_get(:@prefetch_rows) end
def keys
-
(Array)
- bind variable keys
def keys @bind_handles.keys end
def make_bind_object(param, fetch_array_size = nil)
def make_bind_object(param, fetch_array_size = nil) case param when Hash key = param[:type] val = param[:value] max_array_size = param[:max_array_size] if key.nil? if val.nil? raise "bind type is not given." elsif val.is_a? OCI8::Object::Base key = :named_type param = @con.get_tdo_by_class(val.class) else key = val.class end elsif key.class == Class && key < OCI8::Object::Base param = @con.get_tdo_by_class(key) key = :named_type elsif key == :named_type param = @con.get_tdo_by_typename(param[:length]) end when OCI8::Metadata::Base key = param.data_type case key when :named_type if param.type_name == 'XMLTYPE' key = :xmltype else param = @con.get_tdo_by_metadata(param.type_metadata) end end else raise "unknown param #{param.intern}" end bindclass = OCI8::BindType::Mapping[key] if bindclass.nil? and key.is_a? Class bindclass = OCI8::BindType::Mapping[key.to_s] OCI8::BindType::Mapping[key] = bindclass if bindclass end raise "unsupported datatype: #{key}" if bindclass.nil? bindclass.create(@con, val, param, fetch_array_size || max_array_size) end
def max_array_size=(size)
Instance variable actual_array_size holds the size of the arrays users actually binds through bind_param_array
All the binds will be clean from cursor if instance variable max_array_size is set before
Set the maximum array size for bind_param_array
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
def prefetch_rows=(rows)
-
rows
(Integer
) -- The number of rows to be prefetched
def prefetch_rows=(rows) attr_set_ub4(11, rows) # OCI_ATTR_PREFETCH_ROWS(11) @prefetch_rows = rows end
def row_count
-
(Integer)
-
def row_count # https://docs.oracle.com/database/121/LNOCI/ociaahan.htm#sthref5774 attr_get_ub8(457) # OCI_ATTR_UB8_ROW_COUNT(457) end
def row_count
def row_count # http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/ociaahan.htm#sthref5498 attr_get_ub4(9) # OCI_ATTR_ROW_COUNT(9) end
def statement
- Since: - 2.1.3
Returns:
-
(String)
- prepared SQL statement
Other tags:
- Note: -
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
def type
[ruby-oci8 1.0] OCI8::STMT_* are Integers. (1, 2, 3, etc.)
[ruby-oci8 2.0] OCI8::STMT_* are Symbols. (:select_stmt, :update_stmt, etc.)
Changes between ruby-oci8 1.0 and 2.0.
* Other Integer value undocumented in Oracle manuals.
* OCI8::STMT_DECLARE (PL/SQL block which starts with a DECLARE keyword)
* OCI8::STMT_BEGIN (PL/SQL block which starts with a BEGIN keyword)
* OCI8::STMT_ALTER
* OCI8::STMT_DROP
* OCI8::STMT_CREATE
* OCI8::STMT_INSERT
* OCI8::STMT_DELETE
* OCI8::STMT_UPDATE
* OCI8::STMT_SELECT
gets the type of SQL statement as follows.
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