OCI8::Cursor

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.

define(pos, type, length = nil)

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.

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

bind_param(key, val, type = nil, length = nil)

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

 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, Integer, 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.

 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.

 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.

 cursor = conn.parse("INSERT INTO raw_table(raw_column) VALUE (:1)")
 cursor.bind_param(1, 'RAW_STRING', OCI8::RAW)
 cursor.exec()
 cursor.close()

[key]

Gets the value of the bind variable.

In case of binding explicitly, use same key with that of #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.

 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

 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.

 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'

[key] = val

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.

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

 ['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.

keys

return the keys of bind variables as array.

exec(*bindvars)

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

type

gets the type of SQL statement. Its value is one of the 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
• OCI8::STMT_DECLARE

For PL/SQL statement, it returns OCI8::STMT_BEGIN or OCI8::STMT_DECLARE.

On ruby-oci8 1.0, these constants values are Fixnums. On ruby-oci8 2.0, they are Symbols

row_count

return the number of processed rows.

get_col_names

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

column_metadata

(new in 1.0.0 without rc)

get column information of the select statement. You can get it after OCI8#exec or OCI8::Cursor#exec is executed. This returns an array of OCI8::Metadata::Column.

fetch

get fetched data as an Array. This is available for select statement only.

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

fetch_hash

get fetched data as a Hash. The hash keys are column names.

close

close the cursor.

rowid

get the rowid of the last processed row. This value is available as bind data. On the other hand it isn't available for other purpose.

prefetch_rows = rows

set prefetch rows to this cursor. This value doesn't affect other cursors.