OCI8

This is a class representing a database connection, which corresponds to java.sql.Connection of JDBC and database handle $dbh of Perl/DBI.

OCI8.new(userid, password, dbname = nil, privilege = nil)

connect to Oracle by userid and password. dbname is the connect string of Net8. If you need DBA privilege, please set privilege as :SYSDBA or :SYSOPER. If the Oracle client is 10g or later, you can use "//hostname_or_ip:port_no/oracle_sid" as dbname.

 # sqlplus scott/tiger
 conn = OCI8.new("scott", "tiger")

 # sqlplus scott/tiger@orcl.world
 conn = OCI8.new("scott", "tiger", "orcl.world")

 # sqlplus 'sys/change_on_install as sysdba'
 conn = OCI8.new("sys", "change_on_install", nil, :SYSDBA)

 # sqlplus scott/tiger@//oracle_db.example.com/XE
 conn = OCI8.new("scott", "tiger", "//oracle_db.example.com/XE")

To connect to Oracle by OS authentication, use nil for userid and password (new in 1.0.0-rc2.)

logoff()

disconnect from Oracle. Uncommitted transaction is rollbacked.

 conn = OCI8.new("scott", "tiger")
 ... do something ...
 conn.logoff

exec(sql, *bindvars)

execute the sql statement. The type of return value depends on the type of sql statement: select; insert, update and delete; create, alter and drop; and PL/SQL.

When bindvars are specified, they are bound as bind variables before execution.

In case of select statement with no block, it returns the instance of OCI8::Cursor.

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

In case of select statement with a block, it acts as iterator and returns the processed row counts. Fetched data is passed to the block as array. NULL value becomes nil in ruby.

 conn = OCI8.new('scott', 'tiger')
 num_rows = conn.exec('SELECT * FROM emp') do |r|
   puts r.join(',')
 end
 puts num_rows.to_s + ' rows were processed.'
 conn.logoff

In case of insert, update or delete statement, it returns the number of processed rows.

 conn = OCI8.new('scott', 'tiger')
 num_rows = conn.exec('UPDATE emp SET sal = sal * 1.1')
 puts num_rows.to_s + ' rows were updated.'
 conn.logoff

In case of create, alter or drop statement, it returns true.

 conn = OCI8.new('scott', 'tiger')
 conn.exec('CREATE TABLE test (col1 CHAR(6))')
 conn.logoff

In case of PL/SQL statement, it returns the array of bind variables.

 conn = OCI8.new('scott', 'tiger')
 conn.exec("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;", 'ABCD', 123)
 # => ["0123", 123]
 conn.logoff

Above example uses two bind variables which names are :str and :num. These initial values are "the string whose width is 4 and whose value is 'ABCD'" and "the number whose value is 123". This method returns the array of these bind variables, which may modified by PL/SQL statement. The order of array is same with that of bind variables.

parse(sql)

create cursor, prepare to execute SQL statement and return the instance of OCI8::Cursor.

describe_table(table_name)

(new in 1.0.0 without 'rc')

returns OCI8::Metadata::Table or OCI8::Metadata::View. If the table_name is not found, an OCIError(ORA-04043) exception is raised.

commit()

commit the transaction.

 conn = OCI8.new("scott", "tiger")
 conn.exec("UPDATE emp SET sal = sal * 1.1") # yahoo
 conn.commit
 conn.logoff

rollback()

rollback the transaction.

 conn = OCI8.new("scott", "tiger")
 conn.exec("UPDATE emp SET sal = sal * 0.9") # boos
 conn.rollback
 conn.logoff

autocommit?

return the state of the autocommit mode. The default value is false. If true, the transaction is committed automatically whenever executing insert/update/delete statements.

autocommit = boolean_value

change the status of the autocommit mode.

 conn = OCI8.new("scott", "tiger")
 conn.autocommit = true
 ... do something ...
 conn.logoff

long_read_len

get the internal size of a fetch buffer for LONG or LONG RAW datatype. The default value is 65535. On ruby-oci8 1.0 it is the maximum fetch size. If the fetched value is longer than this value, it causes an error. On ruby-oci8 2.0, it is the size of one piece to perform a piecewise fetch. The piecewise fetch is performed internally. You have no need to care it.

See also: How can I use LONG or LONG RAW data?

long_read_len = size

set the size of long_read_len.

See also: How can I use LONG or LONG RAW data?

prefetch_rows = rows

change the prefetch rows size. This reduces network round trips when fetching multiple rows. The default value is 1.

non_blocking?

return the status of blocking/non-blocking mode. The default value is false. It is blocking mode.

non_blocking= boolean_value

Changes the status of blocking/non-blocking mode.

break

cancels the OCI call performing in other thread. To use this, the connection must be non-blocking mode.