Class: OCI8
- Defined in:
- lib/oci8/oci8.rb,
lib/oci8/cursor.rb,
lib/oci8/object.rb,
lib/oci8/object.rb,
lib/oci8/bindtype.rb,
lib/oci8/datetime.rb,
lib/oci8/metadata.rb,
lib/oci8/properties.rb,
lib/oci8/encoding-init.rb,
lib/oci8/oracle_version.rb,
lib/oci8/connection_pool.rb,
ext/oci8/lob.c,
ext/oci8/oci8.c,
ext/oci8/stmt.c,
ext/oci8/encoding.c,
ext/oci8/ocinumber.c,
ext/oci8/connection_pool.c
Overview
A connection to a Oracle database server.
example:
# output the emp table's content as CSV format.
conn = OCI8.new(username, password)
conn.exec('select * from emp') do |row|
puts row.join(',')
end
# execute PL/SQL block with bind variables.
conn = OCI8.new(username, password)
conn.exec('BEGIN procedure_name(:1, :2); END;',
value_for_the_first_parameter,
value_for_the_second_parameter)
Defined Under Namespace
Modules: BindType, Math, Metadata, Object Classes: BFILE, BLOB, CLOB, ConnectionPool, Cursor, LOB, NCLOB, OracleVersion, TDO
Constant Summary collapse
- VERSION =
rb_obj_freeze(rb_usascii_str_new_cstr(OCI8LIB_VERSION))
Instance Attribute Summary collapse
Class Method Summary collapse
-
.error_message(message_no) ⇒ String
Get the Oracle error message specified by message_no.
-
.properties ⇒ a customized Hash
Returns a Hash which ruby-oci8 global settings.
Instance Method Summary collapse
-
#action=(string) ⇒ Object
Sets the name of the current action within the current module.
-
#autocommit=(true) ⇒ Object
Sets the autocommit mode.
-
#autocommit? ⇒ Boolean
Returns
true
if the connection is in autocommit mode,false
otherwise. -
#break ⇒ Object
Cancels the executing SQL.
-
#charset_id2name(charset_id) ⇒ String
Returns the Oracle character set name from the specified character set ID if it is valid.
-
#charset_name2id(charset_name) ⇒ Fixnum
Returns the Oracle character set ID for the specified Oracle character set name if it is valid.
-
#client_identifier=(string) ⇒ Object
Sets the client ID.
-
#client_info=(string) ⇒ Object
Sets additional information about the client application.
-
#commit ⇒ Object
Commits the transaction.
-
#database_charset_name ⇒ String
Returns the Oracle database character set name such as AL32UTF8.
-
#describe_any(object_name) ⇒ a subclass of OCI8::Metadata::Base
Returns object information.
-
#describe_database(database_name) ⇒ OCI8::Metadata::Database
Returns database information.
-
#describe_function(function_name) ⇒ OCI8::Metadata::Function
Returns function information.
-
#describe_package(package_name) ⇒ OCI8::Metadata::Package
Returns package information.
-
#describe_procedure(procedure_name) ⇒ OCI8::Metadata::Procedure
Returns procedure information.
-
#describe_schema(schema_name) ⇒ OCI8::Metadata::Schema
Returns schema information.
-
#describe_sequence(sequence_name) ⇒ OCI8::Metadata::Sequence
Returns sequence information.
-
#describe_synonym(synonym_name, check_public_also = true) ⇒ OCI8::Metadata::Synonym
Returns synonym information.
-
#describe_table(table_name, table_only = false) ⇒ OCI8::Metadata::Table or OCI8::Metadata::View
Returns table or view information.
-
#describe_type(type_name) ⇒ OCI8::Metadata::Type
Returns type information.
-
#describe_view(view_name) ⇒ OCI8::Metadata::View
Returns view information.
-
#exec(sql, *bindvars, &block) ⇒ Object
Executes the sql statement.
-
#initialize(username, password, dbname = nil, privilege = nil) ⇒ OCI8
constructor
Connects to an Oracle database server by
username
andpassword
atdbname
asprivilege
. -
#logoff ⇒ Object
Disconnects from the Oracle server.
-
#long_read_len ⇒ Fixnum
Gets the maximum length in bytes to fetch a LONG or LONG RAW column.
-
#long_read_len=(fixnum) ⇒ Object
Sets the maximum length in bytes to fetch a LONG or LONG RAW column.
-
#module=(string) ⇒ Object
Sets the name of the current module.
-
#non_blocking=(true) ⇒ Object
Sets
true
to enable non-blocking mode,false
otherwise. -
#non_blocking? ⇒ Boolean
Returns
true
if the connection is in non-blocking mode,false
otherwise. -
#oracle_server_version ⇒ OCI8::OracleVersion
Returns the Oracle server version.
-
#parse(sql) ⇒ OCI8::Cursor
Returns a prepared SQL handle.
-
#ping ⇒ Boolean
Makes a round trip call to the server to confirm that the connection and the server are active.
-
#prefetch_rows=(num) ⇒ Object
Sets the prefetch rows size.
-
#rollback ⇒ Object
Rollbacks the transaction.
-
#select_one(sql, *bindvars) ⇒ Array
Executes a SQL statement and fetches the first one row.
- #username ⇒ Object
Constructor Details
#initialize(username, password, dbname = nil, privilege = nil) ⇒ OCI8
Connects to an Oracle database server by username
and password
at dbname
as privilege
.
connecting to the local server
Set username
and password
or pass “username/password” as a single argument.
OCI8.new('scott', 'tiger')
or
OCI8.new('scott/tiger')
connecting to a remote server
Set username
, password
and dbname
or pass “username/password@dbname” as a single argument.
OCI8.new('scott', 'tiger', 'orcl.world')
or
OCI8.new('scott/[email protected]')
The dbname
is a net service name or an easy connectection identifier. The former is a name listed in the file tnsnames.ora. Ask to your DBA if you don’t know what it is. The latter has the syntax as “//host:port/service_name”.
OCI8.new('scott', 'tiger', '//remote-host:1521/XE')
or
OCI8.new('scott/tiger@//remote-host:1521/XE')
connecting as a privileged user
Set :SYSDBA or :SYSOPER to privilege
, otherwise “username/password as sysdba” or “username/password as sysoper” as a single argument.
OCI8.new('sys', 'change_on_install', nil, :SYSDBA)
or
OCI8.new('sys/change_on_install as sysdba')
external OS authentication
Set nil to username
and password
, or “/” as a single argument.
OCI8.new(nil, nil)
or
OCI8.new('/')
To connect to a remote host:
OCI8.new(nil, nil, 'dbname')
or
OCI8.new('/@dbname')
proxy authentication
Enclose end user’s username with square brackets and add it at the end of proxy user’s username.
OCI8.new('proxy_user_name[end_user_name]', 'proxy_password')
or
OCI8.new('proxy_user_name[end_user_name]/proxy_password')
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 |
# File 'lib/oci8/oci8.rb', line 97 def initialize(*args) if args.length == 1 username, password, dbname, mode = parse_connect_string(args[0]) else username, password, dbname, mode = args end if username.nil? and password.nil? cred = OCI_CRED_EXT end case mode when :SYSDBA mode = OCI_SYSDBA when :SYSOPER mode = OCI_SYSOPER when :SYSASM if OCI8.oracle_client_version < OCI8::ORAVER_11_1 raise "SYSASM is not supported on Oracle version #{OCI8.oracle_client_version}" end mode = OCI_SYSASM when nil # do nothing else raise "unknown privilege type #{mode}" end stmt_cache_size = OCI8.properties[:statement_cache_size] stmt_cache_size = nil if stmt_cache_size == 0 attach_mode = 0 if dbname.is_a? OCI8::ConnectionPool @pool = dbname # to prevent GC from freeing the connection pool. dbname = dbname.send(:pool_name) attach_mode |= 0x0200 # OCI_CPOOL and OCI_LOGON2_CPOOL end if stmt_cache_size # enable statement caching attach_mode |= 0x0004 # OCI_STMT_CACHE and OCI_LOGON2_STMTCACHE end if true # logon by the OCI function OCISessionBegin(). allocate_handles() @session_handle.send(:attr_set_string, OCI_ATTR_USERNAME, username) if username @session_handle.send(:attr_set_string, OCI_ATTR_PASSWORD, password) if password if @@oracle_client_version >= ORAVER_11_1 # 'rubyoci8' is displayed in V$SESSION_CONNECT_INFO.CLIENT_DRIVER # if both the client and the server are Oracle 11g or upper. # 424: OCI_ATTR_DRIVER_NAME @session_handle.send(:attr_set_string, 424, 'rubyoci8') end server_attach(dbname, attach_mode) session_begin(cred ? cred : OCI_CRED_RDBMS, mode ? mode : OCI_DEFAULT) else # logon by the OCI function OCILogon2(). logon2(username, password, dbname, attach_mode) end if stmt_cache_size # set statement cache size attr_set_ub4(176, stmt_cache_size) # 176: OCI_ATTR_STMTCACHESIZE end @prefetch_rows = nil @username = nil end |
Instance Attribute Details
Class Method Details
.error_message(message_no) ⇒ String
Get the Oracle error message specified by message_no. Its language depends on NLS_LANGUAGE.
Note: This method is unavailable if the Oracle client version is 8.0.
example:
# When NLS_LANG is AMERICAN_AMERICA.AL32UTF8
OCI8.(1) # => "ORA-00001: unique constraint (%s.%s) violated"
# When NLS_LANG is FRENCH_FRANCE.AL32UTF8
OCI8.(1) # => "ORA-00001: violation de contrainte unique (%s.%s)"
240 241 242 243 |
# File 'ext/oci8/oci8.c', line 240
static VALUE oci8_s_error_message(VALUE klass, VALUE msgid)
{
return oci8_get_error_message(NUM2UINT(msgid), NULL);
}
|
.properties ⇒ a customized Hash
Returns a Hash which ruby-oci8 global settings. The hash’s setter and getter methods are customized to check property names and values.
# get properties
OCI8.properties[:bind_string_as_nchar] # => false
OCI8.properties[:invalid_property_name] # raises an IndexError
# set properties
OCI8.properties[:bind_string_as_nchar] = true
OCI8.properties[:invalid_property_name] = true # raises an IndexError
Supported properties are listed below:
- :length_semantics
-
:char
when Oracle character length is counted by the number of characters.:byte
when it is counted by the number of bytes. The default setting is:byte
because:char
causes unexpected behaviour on Oracle 9i.Since: 2.1.0
- :bind_string_as_nchar
-
true
when string bind variables are bound as NCHAR, otherwisefalse
. The default value isfalse
. - :float_conversion_type
-
:ruby
when Oracle decimal numbers are converted to ruby Float values same as Float#to_s does. (default):oracle:
when they are done by Oracle OCI functions.From ruby 1.9.2, a float value converted from Oracle number 15.7 by the Oracle function OCINumberToReal() makes a string representation 15.700000000000001 by Float#to_s. See: rubyforge.org/forum/forum.php?thread_id=50030&forum_id=1078
Since: 2.1.0
- :statement_cache_size
-
The statement cache size per each session. The default size is 0, which means no statement cache, since 2.1.2. It was 20 in 2.1.1. This feature is available on Oracle 9iR2 or later. See: docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci09adv.htm#i471377
Since: 2.1.1
- :events_mode
-
true
when Fast Application Notification (FAN) Support is enabled.false
when it is disabled. The default value isfalse
. This corresponds tooci8.events
in PHP.This parameter can be changed only when no OCI methods are called.
require 'oci8' OCI8.properties[:events_mode] = true # works fine. ... call some OCI methods ... OCI8.properties[:events_mode] = true # raises a runtime error.
Since: 2.1.4
131 132 133 |
# File 'lib/oci8/properties.rb', line 131 def self.properties @@properties end |
Instance Method Details
#action=(string) ⇒ Object
Sets the name of the current action within the current module. This information is stored in the V$SESSION view and is also stored in the V$SQL view and the V$SQLAREA view when a SQL statement is executed and the SQL statement is first parsed in the Oracle server.
Oracle 10g client or upper
This doesn’t perform network round trips. The change is reflected to the server by the next round trip such as OCI8#exec, OCI8#ping, etc.
Oracle 9i client or lower
This executes the following PL/SQL block internally. The change is reflected immediately by a network round trip.
BEGIN
DBMS_APPLICATION_INFO.SET_ACTION(:action);
END;
See Oracle Manual: Oracle Database PL/SQL Packages and Types Reference
996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 |
# File 'ext/oci8/oci8.c', line 996
static VALUE oci8_set_action(VALUE self, VALUE val)
{
const char *ptr;
ub4 size;
if (!NIL_P(val)) {
OCI8SafeStringValue(val);
ptr = RSTRING_PTR(val);
size = RSTRING_LEN(val);
} else {
ptr = "";
size = 0;
}
if (oracle_client_version >= ORAVER_10_1) {
/* Oracle 10g or upper */
chker2(OCIAttrSet(oci8_get_oci_session(self), OCI_HTYPE_SESSION, (dvoid*)ptr,
size, OCI_ATTR_ACTION, oci8_errhp),
DATA_PTR(self));
} else {
/* Oracle 9i or lower */
oci8_exec_sql_var_t bind_vars[1];
/* :action */
bind_vars[0].valuep = (dvoid*)ptr;
bind_vars[0].value_sz = size;
bind_vars[0].dty = SQLT_CHR;
bind_vars[0].indp = NULL;
bind_vars[0].alenp = NULL;
oci8_exec_sql(oci8_get_svcctx(self),
"BEGIN\n"
" DBMS_APPLICATION_INFO.SET_ACTION(:action);\n"
"END;\n", 0, NULL, 1, bind_vars, 1);
}
return val;
}
|
#autocommit=(true) ⇒ Object
Sets the autocommit mode. The default value is false
.
709 710 711 712 713 714 |
# File 'ext/oci8/oci8.c', line 709
static VALUE oci8_set_autocommit(VALUE self, VALUE val)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
svcctx->is_autocommit = RTEST(val);
return val;
}
|
#autocommit? ⇒ Boolean
Returns true
if the connection is in autocommit mode, false
otherwise. The default value is false
.
697 698 699 700 701 |
# File 'ext/oci8/oci8.c', line 697
static VALUE oci8_autocommit_p(VALUE self)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
return svcctx->is_autocommit ? Qtrue : Qfalse;
}
|
#break ⇒ Object
Cancels the executing SQL.
See also #non_blocking=.
760 761 762 763 764 765 766 767 768 769 770 771 772 |
# File 'ext/oci8/oci8.c', line 760
static VALUE oci8_break(VALUE self)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
if (NIL_P(svcctx->executing_thread)) {
return Qfalse;
}
#ifndef NATIVE_THREAD_WITH_GVL
chker2(OCIBreak(svcctx->base.hp.ptr, oci8_errhp), &svcctx->base);
#endif
rb_thread_wakeup(svcctx->executing_thread);
return Qtrue;
}
|
#charset_id2name(charset_id) ⇒ String
Returns the Oracle character set name from the specified character set ID if it is valid. Otherwise, nil
is returned.
Oracle 9iR2 client or upper
It is done by using the mapping table stored in the client side.
Oracle 9iR1 client or lower
It executes the following PL/SQL block internally to use the mapping table stored in the server side.
BEGIN
:name := nls_charset_name(:csid);
END;
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
# File 'ext/oci8/encoding.c', line 53
VALUE oci8_charset_id2name(VALUE svc, VALUE csid)
{
VALUE name = rb_hash_aref(csid2name, csid);
if (!NIL_P(name)) {
return name;
}
Check_Type(csid, T_FIXNUM);
if (have_OCINlsCharSetIdToName) {
/* Oracle 9iR2 or upper */
char buf[OCI_NLS_MAXBUFSZ];
sword rv;
rv = OCINlsCharSetIdToName(oci8_envhp, TO_ORATEXT(buf), sizeof(buf), (ub2)FIX2INT(csid));
if (rv != OCI_SUCCESS) {
return Qnil;
}
name = rb_usascii_str_new_cstr(buf);
} else {
/* Oracle 9iR1 or lower */
oci8_exec_sql_var_t bind_vars[2];
char buf[OCI_NLS_MAXBUFSZ];
ub2 buflen = 0;
int ival = FIX2INT(csid);
/* :name */
bind_vars[0].valuep = buf;
bind_vars[0].value_sz = OCI_NLS_MAXBUFSZ;
bind_vars[0].dty = SQLT_CHR;
bind_vars[0].indp = NULL;
bind_vars[0].alenp = &buflen;
/* :csid */
bind_vars[1].valuep = &ival;
bind_vars[1].value_sz = sizeof(int);
bind_vars[1].dty = SQLT_INT;
bind_vars[1].indp = NULL;
bind_vars[1].alenp = NULL;
/* convert chaset id to charset name by querying Oracle server. */
oci8_exec_sql(oci8_get_svcctx(svc), "BEGIN :name := nls_charset_name(:csid); END;", 0, NULL, 2, bind_vars, 1);
if (buflen == 0) {
return Qnil;
}
name = rb_usascii_str_new(buf, buflen);
}
OBJ_FREEZE(name);
rb_hash_aset(csid2name, csid, name);
rb_hash_aset(csname2id, name, csid);
return name;
}
|
#charset_name2id(charset_name) ⇒ Fixnum
Returns the Oracle character set ID for the specified Oracle character set name if it is valid. Othewise, nil
is returned.
Oracle 9iR2 client or upper
It is done by using the mapping table stored in the client side.
Oracle 9iR1 client or lower
It executes the following PL/SQL block internally to use the mapping table stored in the server side.
BEGIN
:csid := nls_charset_id(:name);
END;
128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 |
# File 'ext/oci8/encoding.c', line 128
static VALUE oci8_charset_name2id(VALUE svc, VALUE name)
{
VALUE csid;
name = rb_funcall(name, id_upcase, 0);
csid = rb_hash_aref(csname2id, StringValue(name));
if (!NIL_P(csid)) {
return csid;
}
if (have_OCINlsCharSetNameToId) {
/* Oracle 9iR2 or upper */
ub2 rv;
rv = OCINlsCharSetNameToId(oci8_envhp, RSTRING_ORATEXT(name));
if (rv == 0) {
return Qnil;
}
csid = INT2FIX(rv);
} else {
/* Oracle 9iR1 or lower */
oci8_exec_sql_var_t bind_vars[2];
int ival;
sb2 ind = 0; /* null indicator */
/* :csid */
bind_vars[0].valuep = &ival;
bind_vars[0].value_sz = sizeof(int);
bind_vars[0].dty = SQLT_INT;
bind_vars[0].indp = &ind;
bind_vars[0].alenp = NULL;
/* :name */
bind_vars[1].valuep = RSTRING_PTR(name);
bind_vars[1].value_sz = RSTRING_LEN(name);
bind_vars[1].dty = SQLT_CHR;
bind_vars[1].indp = NULL;
bind_vars[1].alenp = NULL;
/* convert chaset name to charset id by querying Oracle server. */
oci8_exec_sql(oci8_get_svcctx(svc), "BEGIN :csid := nls_charset_id(:name); END;", 0, NULL, 2, bind_vars, 1);
if (ind) {
return Qnil;
}
csid = INT2FIX(ival);
}
rb_hash_aset(csid2name, csid, name);
rb_hash_aset(csname2id, name, csid);
return csid;
}
|
#client_identifier=(string) ⇒ Object
Sets the client ID. This information is stored in the V$SESSION view.
Oracle 9i client or upper
This doesn’t perform network round trips. The change is reflected to the server by the next round trip such as OCI8#exec, OCI8#ping, etc.
Oracle 8i client or lower
This executes the following PL/SQL block internally. The change is reflected immediately by a network round trip.
BEGIN
DBMS_SESSION.SET_IDENTIFIER(:client_id);
END;
See Oracle Manual: Oracle Database PL/SQL Packages and Types Reference
854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 |
# File 'ext/oci8/oci8.c', line 854
static VALUE oci8_set_client_identifier(VALUE self, VALUE val)
{
const char *ptr;
ub4 size;
if (!NIL_P(val)) {
OCI8SafeStringValue(val);
ptr = RSTRING_PTR(val);
size = RSTRING_LEN(val);
} else {
ptr = "";
size = 0;
}
if (oracle_client_version >= ORAVERNUM(9, 2, 0, 3, 0) || size > 0) {
if (size > 0 && ptr[0] == ':') {
rb_raise(rb_eArgError, "client identifier should not start with ':'.");
}
chker2(OCIAttrSet(oci8_get_oci_session(self), OCI_HTYPE_SESSION, (dvoid*)ptr,
size, OCI_ATTR_CLIENT_IDENTIFIER, oci8_errhp),
DATA_PTR(self));
} else {
/* Workaround for Bug 2449486 */
oci8_exec_sql_var_t bind_vars[1];
/* :client_id */
bind_vars[0].valuep = (dvoid*)ptr;
bind_vars[0].value_sz = size;
bind_vars[0].dty = SQLT_CHR;
bind_vars[0].indp = NULL;
bind_vars[0].alenp = NULL;
oci8_exec_sql(oci8_get_svcctx(self),
"BEGIN\n"
" DBMS_SESSION.SET_IDENTIFIER(:client_id);\n"
"END;\n", 0, NULL, 1, bind_vars, 1);
}
return val;
}
|
#client_info=(string) ⇒ Object
Sets additional information about the client application. This information is stored in the V$SESSION view.
Oracle 10g client or upper
This doesn’t perform network round trips. The change is reflected to the server by the next round trip such as OCI8#exec, OCI8#ping, etc.
Oracle 9i client or lower
This executes the following PL/SQL block internally. The change is reflected immediately by a network round trip.
BEGIN
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:client_info);
END;
See Oracle Manual: Oracle Database PL/SQL Packages and Types Reference
1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 |
# File 'ext/oci8/oci8.c', line 1059
static VALUE oci8_set_client_info(VALUE self, VALUE val)
{
const char *ptr;
ub4 size;
if (!NIL_P(val)) {
OCI8SafeStringValue(val);
ptr = RSTRING_PTR(val);
size = RSTRING_LEN(val);
} else {
ptr = "";
size = 0;
}
if (oracle_client_version >= ORAVER_10_1) {
/* Oracle 10g or upper */
chker2(OCIAttrSet(oci8_get_oci_session(self), OCI_HTYPE_SESSION, (dvoid*)ptr,
size, OCI_ATTR_CLIENT_INFO, oci8_errhp),
DATA_PTR(self));
} else {
/* Oracle 9i or lower */
oci8_exec_sql_var_t bind_vars[1];
/* :client_info */
bind_vars[0].valuep = (dvoid*)ptr;
bind_vars[0].value_sz = size;
bind_vars[0].dty = SQLT_CHR;
bind_vars[0].indp = NULL;
bind_vars[0].alenp = NULL;
oci8_exec_sql(oci8_get_svcctx(self),
"BEGIN\n"
" DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:client_info);\n"
"END;\n", 0, NULL, 1, bind_vars, 1);
}
return val;
}
|
#commit ⇒ Object
Commits the transaction.
591 592 593 594 595 596 |
# File 'ext/oci8/oci8.c', line 591
static VALUE oci8_commit(VALUE self)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
chker2(OCITransCommit_nb(svcctx, svcctx->base.hp.svc, oci8_errhp, OCI_DEFAULT), &svcctx->base);
return self;
}
|
#database_charset_name ⇒ String
Returns the Oracle database character set name such as AL32UTF8.
374 375 376 |
# File 'lib/oci8/oci8.rb', line 374 def database_charset_name charset_id2name(@server_handle.send(:attr_get_ub2, OCI_ATTR_CHARSET_ID)) end |
#describe_any(object_name) ⇒ a subclass of OCI8::Metadata::Base
Returns object information.
The return type is depends on the object type.
- Oracle type
-
Ruby type
- Table
- View
- Procedure
- Function
- Package
- Type
- Synonym
- Sequence
2054 2055 2056 2057 2058 2059 2060 2061 2062 |
# File 'lib/oci8/metadata.rb', line 2054 def describe_any(object_name) if /^PUBLIC\.(.*)/i =~ object_name md = __describe($1, OCI8::Metadata::Unknown, true) raise OCIError.new(4043, object_name) if md.obj_schema != 'PUBLIC' md else __describe(object_name, OCI8::Metadata::Unknown, true) end end |
#describe_database(database_name) ⇒ OCI8::Metadata::Database
Returns database information
2167 2168 2169 |
# File 'lib/oci8/metadata.rb', line 2167 def describe_database(database_name) __describe(database_name, OCI8::Metadata::Database, false) end |
#describe_function(function_name) ⇒ OCI8::Metadata::Function
Returns function information
2119 2120 2121 |
# File 'lib/oci8/metadata.rb', line 2119 def describe_function(function_name) __describe(function_name, OCI8::Metadata::Function, false) end |
#describe_package(package_name) ⇒ OCI8::Metadata::Package
Returns package information
2126 2127 2128 |
# File 'lib/oci8/metadata.rb', line 2126 def describe_package(package_name) __describe(package_name, OCI8::Metadata::Package, false) end |
#describe_procedure(procedure_name) ⇒ OCI8::Metadata::Procedure
Returns procedure information
2112 2113 2114 |
# File 'lib/oci8/metadata.rb', line 2112 def describe_procedure(procedure_name) __describe(procedure_name, OCI8::Metadata::Procedure, false) end |
#describe_schema(schema_name) ⇒ OCI8::Metadata::Schema
Returns schema information
2160 2161 2162 |
# File 'lib/oci8/metadata.rb', line 2160 def describe_schema(schema_name) __describe(schema_name, OCI8::Metadata::Schema, false) end |
#describe_sequence(sequence_name) ⇒ OCI8::Metadata::Sequence
Returns sequence information
2153 2154 2155 |
# File 'lib/oci8/metadata.rb', line 2153 def describe_sequence(sequence_name) __describe(sequence_name, OCI8::Metadata::Sequence, false) end |
#describe_synonym(synonym_name, check_public_also = true) ⇒ OCI8::Metadata::Synonym
Returns synonym information
2140 2141 2142 2143 2144 2145 2146 2147 2148 |
# File 'lib/oci8/metadata.rb', line 2140 def describe_synonym(synonym_name, check_public_also = true) if /^PUBLIC\.(.*)/i =~ synonym_name md = __describe($1, OCI8::Metadata::Synonym, true) raise OCIError.new(4043, synonym_name) if md.obj_schema != 'PUBLIC' md else __describe(synonym_name, OCI8::Metadata::Synonym, check_public_also) end end |
#describe_table(table_name, table_only = false) ⇒ OCI8::Metadata::Table or OCI8::Metadata::View
Returns table or view information. If the name is a current schema’s synonym name or a public synonym name, it returns table or view information which the synonym refers.
If table_only
is true, it checks tables in the current schema.
2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 |
# File 'lib/oci8/metadata.rb', line 2072 def describe_table(table_name, table_only = false) if table_only # check my own tables only. __describe(table_name, OCI8::Metadata::Table, false) else # check tables, views, synonyms and public synonyms. # follow synonyms up to 20 times to prevent infinite loop # caused by recursive synonyms. recursive_level = 20 recursive_level.times do = __describe(table_name, OCI8::Metadata::Unknown, true) case when OCI8::Metadata::Table, OCI8::Metadata::View return when OCI8::Metadata::Synonym table_name = .translated_name if .obj_link and .link.nil? # table_name is a synonym in a remote database for an object in the # remote database itself. table_name = "#{table_name}@#{.obj_link}" end else raise OCIError.new(4043, table_name) # ORA-04043: object %s does not exist end end raise OCIError.new(36, recursive_level) # ORA-00036: maximum number of recursive SQL levels (%s) exceeded end end |
#describe_type(type_name) ⇒ OCI8::Metadata::Type
Returns type information
2133 2134 2135 |
# File 'lib/oci8/metadata.rb', line 2133 def describe_type(type_name) __describe(type_name, OCI8::Metadata::Type, false) end |
#describe_view(view_name) ⇒ OCI8::Metadata::View
Returns view information
2105 2106 2107 |
# File 'lib/oci8/metadata.rb', line 2105 def describe_view(view_name) __describe(view_name, OCI8::Metadata::View, false) end |
#exec(sql, *bindvars, &block) ⇒ Object
Executes 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.
select statements without block
It returns the instance of OCI8::Cursor.
example:
conn = OCI8.new('scott', 'tiger')
cursor = conn.exec('SELECT * FROM emp')
while r = cursor.fetch()
puts r.join(',')
end
cursor.close
conn.logoff
select statements 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.
example:
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
PL/SQL block (ruby-oci8 1.0)
It returns the array of bind variables’ values.
example:
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.
If a block is given, it is ignored.
PL/SQL block (ruby-oci8 2.0)
It returns the number of processed rows.
example:
conn = OCI8.new('scott', 'tiger')
conn.exec("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;", 'ABCD', 123)
# => 1
conn.logoff
If a block is given, the bind variables’ values are passed to the block after executed.
conn = OCI8.new('scott', 'tiger')
conn.exec("BEGIN :str := TO_CHAR(:num, 'FM0999'); END;", 'ABCD', 123) do |str, num|
puts str # => '0123'
puts num # => 123
end
conn.logoff
FYI, the following code do same on ruby-oci8 1.0 and ruby-oci8 2.0.
conn.exec(sql, *bindvars) { |*outvars| outvars }
Other SQL statements
It returns the number of processed rows.
example:
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
example:
conn = OCI8.new('scott', 'tiger')
conn.exec('CREATE TABLE test (col1 CHAR(6))') # => 0
conn.logoff
267 268 269 270 |
# File 'lib/oci8/oci8.rb', line 267 def exec(sql, *bindvars, &block) @last_error = nil exec_internal(sql, *bindvars, &block) end |
#logoff ⇒ Object
Disconnects from the Oracle server. The uncommitted transaction is rollbacked.
568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 |
# File 'ext/oci8/oci8.c', line 568
static VALUE oci8_svcctx_logoff(VALUE self)
{
oci8_svcctx_t *svcctx = (oci8_svcctx_t *)DATA_PTR(self);
while (svcctx->base.children != NULL) {
oci8_base_free(svcctx->base.children);
}
if (svcctx->logoff_strategy != NULL) {
const oci8_logoff_strategy_t *strategy = svcctx->logoff_strategy;
void *data = strategy->prepare(svcctx);
svcctx->base.type = 0;
svcctx->logoff_strategy = NULL;
chker2(oci8_call_without_gvl(svcctx, strategy->execute, data), &svcctx->base);
}
return Qtrue;
}
|
#long_read_len ⇒ Fixnum
Gets the maximum length in bytes to fetch a LONG or LONG RAW column. The default value is 65535.
If the actual data length is longer than long_read_len, “ORA-01406: fetched column value was truncated” is raised.
Note: long_read_len is also used for XMLTYPE data type in 2.0.
728 729 730 731 732 |
# File 'ext/oci8/oci8.c', line 728
static VALUE oci8_long_read_len(VALUE self)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
return svcctx->long_read_len;
}
|
#long_read_len=(fixnum) ⇒ Object
Sets the maximum length in bytes to fetch a LONG or LONG RAW column.
See also #long_read_len
744 745 746 747 748 749 750 |
# File 'ext/oci8/oci8.c', line 744
static VALUE oci8_set_long_read_len(VALUE self, VALUE val)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
Check_Type(val, T_FIXNUM);
svcctx->long_read_len = val;
return val;
}
|
#module=(string) ⇒ Object
Sets the name of the current module. This information is stored in the V$SESSION view and is also stored in the V$SQL view and the V$SQLAREA view when a SQL statement is executed and the SQL statement is first parsed in the Oracle server.
Oracle 10g client or upper
This doesn’t perform network round trips. The change is reflected to the server by the next round trip such as OCI8#exec, OCI8#ping, etc.
Oracle 9i client or lower
This executes the following PL/SQL block internally. The change is reflected immediately by a network round trip.
DECLARE
action VARCHAR2(32);
BEGIN
-- retrieve action name.
SELECT SYS_CONTEXT('USERENV','ACTION') INTO action FROM DUAL;
-- change module name without modifying the action name.
DBMS_APPLICATION_INFO.SET_MODULE(:module, action);
END;
See Oracle Manual: Oracle Database PL/SQL Packages and Types Reference
927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 |
# File 'ext/oci8/oci8.c', line 927
static VALUE oci8_set_module(VALUE self, VALUE val)
{
const char *ptr;
ub4 size;
if (!NIL_P(val)) {
OCI8SafeStringValue(val);
ptr = RSTRING_PTR(val);
size = RSTRING_LEN(val);
} else {
ptr = "";
size = 0;
}
if (oracle_client_version >= ORAVER_10_1) {
/* Oracle 10g or upper */
chker2(OCIAttrSet(oci8_get_oci_session(self), OCI_HTYPE_SESSION, (dvoid*)ptr,
size, OCI_ATTR_MODULE, oci8_errhp),
DATA_PTR(self));
} else {
/* Oracle 9i or lower */
oci8_exec_sql_var_t bind_vars[1];
/* :module */
bind_vars[0].valuep = (dvoid*)ptr;
bind_vars[0].value_sz = size;
bind_vars[0].dty = SQLT_CHR;
bind_vars[0].indp = NULL;
bind_vars[0].alenp = NULL;
oci8_exec_sql(oci8_get_svcctx(self),
"DECLARE\n"
" action VARCHAR2(32);\n"
"BEGIN\n"
" SELECT SYS_CONTEXT('USERENV','ACTION') INTO action FROM DUAL;\n"
" DBMS_APPLICATION_INFO.SET_MODULE(:module, action);\n"
"END;\n", 0, NULL, 1, bind_vars, 1);
}
return self;
}
|
#non_blocking=(true) ⇒ Object
Sets true
to enable non-blocking mode, false
otherwise. The default setting depends on the ruby version and ruby-oci8 version.
When the connection is in blocking mode (non_blocking = false), SQL executions block not only the thread, but also the ruby process. It makes the whole application stop while a SQL execution needs long time.
When in non-blocking mode (non_blocking = true), SQL executions block only the thread. It does’t prevent other threads. A SQL execution which blocks a thread can be canceled by OCI8#break.
ruby 1.9
The default setting is true
if the ruby-oci8 version is 2.0.3 or upper, false
otherwise.
Ruby-oci8 makes the connection non-blocking by releasing ruby interpreter’s GVL (Global VM Lock or Giant VM Lock) while OCI functions which may need more than one network round trips are in execution.
ruby 1.8
The default setting is false
.
Ruby-oci8 makes the connection non-blocking by polling the return values of OCI functions. When an OCI function returns OCI_STILL_EXECUTING, the thread sleeps for 10 milli seconds to make a time for other threads to run. The sleep time is doubled up to 640 milli seconds as the function returns the same value.
670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 |
# File 'ext/oci8/oci8.c', line 670
static VALUE oci8_set_non_blocking(VALUE self, VALUE val)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
#ifdef NATIVE_THREAD_WITH_GVL
svcctx->non_blocking = RTEST(val);
#else
sb1 non_blocking;
if (svcctx->state & OCI8_STATE_CPOOL) {
rb_raise(rb_eRuntimeError, "Could not set non-blocking mode to a connection allocated from OCI8::ConnectionPool.");
}
chker2(OCIAttrGet(svcctx->srvhp, OCI_HTYPE_SERVER, &non_blocking, 0, OCI_ATTR_NONBLOCKING_MODE, oci8_errhp), &svcctx->base);
if ((RTEST(val) && !non_blocking) || (!RTEST(val) && non_blocking)) {
/* toggle blocking / non-blocking. */
chker2(OCIAttrSet(svcctx->srvhp, OCI_HTYPE_SERVER, 0, 0, OCI_ATTR_NONBLOCKING_MODE, oci8_errhp), &svcctx->base);
}
#endif
return val;
}
|
#non_blocking? ⇒ Boolean
Returns true
if the connection is in non-blocking mode, false
otherwise.
See also #non_blocking=.
620 621 622 623 624 625 626 627 628 629 630 631 |
# File 'ext/oci8/oci8.c', line 620
static VALUE oci8_non_blocking_p(VALUE self)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
#ifdef NATIVE_THREAD_WITH_GVL
return svcctx->non_blocking ? Qtrue : Qfalse;
#else
sb1 non_blocking;
chker2(OCIAttrGet(svcctx->srvhp, OCI_HTYPE_SERVER, &non_blocking, 0, OCI_ATTR_NONBLOCKING_MODE, oci8_errhp), &svcctx->base);
return non_blocking ? Qtrue : Qfalse;
#endif
}
|
#oracle_server_version ⇒ OCI8::OracleVersion
Returns the Oracle server version.
351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 |
# File 'lib/oci8/oci8.rb', line 351 def oracle_server_version unless defined? @oracle_server_version if vernum = oracle_server_vernum # If the Oracle client is Oracle 9i or upper, # get the server version from the OCI function OCIServerRelease. @oracle_server_version = OCI8::OracleVersion.new(vernum) else # Otherwise, get it from v$version. self.exec('select banner from v$version') do |row| if /^Oracle.*?(\d+\.\d+\.\d+\.\d+\.\d+)/ =~ row[0] @oracle_server_version = OCI8::OracleVersion.new($1) break end end end end @oracle_server_version end |
#parse(sql) ⇒ OCI8::Cursor
Returns a prepared SQL handle.
168 169 170 171 |
# File 'lib/oci8/oci8.rb', line 168 def parse(sql) @last_error = nil parse_internal(sql) end |
#ping ⇒ Boolean
Makes a round trip call to the server to confirm that the connection and the server are active.
OCI8#ping also can be used to flush all the pending OCI client-side calls to the server if any exist.
Oracle 10.2 client or upper
A dummy round trip call is made by a newly added OCI function OCIPing in Oracle 10.2.
Oracle 10.1 client or lower
A simple PL/SQL block “BEGIN NULL; END;” is executed to make a round trip call.
813 814 815 816 817 818 819 820 821 822 823 824 825 826 |
# File 'ext/oci8/oci8.c', line 813
static VALUE oci8_ping(VALUE self)
{
oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
sword rv;
if (have_OCIPing_nb) {
/* Oracle 10.2 or upper */
rv = OCIPing_nb(svcctx, svcctx->base.hp.svc, oci8_errhp, OCI_DEFAULT);
} else {
/* Oracle 10.1 or lower */
rv = oci8_exec_sql(svcctx, "BEGIN NULL; END;", 0U, NULL, 0U, NULL, 0);
}
return rv == OCI_SUCCESS ? Qtrue : FALSE;
}
|
#prefetch_rows=(num) ⇒ Object
Sets the prefetch rows size. The default value is one. When a select statement is executed, the OCI library allocate prefetch buffer to reduce the number of network round trips by retrieving specified number of rows in one round trip.
Note: Active record adaptors set 100 by default.
338 339 340 |
# File 'lib/oci8/oci8.rb', line 338 def prefetch_rows=(num) @prefetch_rows = num end |
#rollback ⇒ Object
Rollbacks the transaction.
604 605 606 607 608 609 |
# File 'ext/oci8/oci8.c', line 604
static VALUE oci8_rollback(VALUE self)
{
oci8_svcctx_t *svcctx = DATA_PTR(self);
chker2(OCITransRollback_nb(svcctx, svcctx->base.hp.svc, oci8_errhp, OCI_DEFAULT), &svcctx->base);
return self;
}
|
#select_one(sql, *bindvars) ⇒ Array
Executes a SQL statement and fetches the first one row.
312 313 314 315 316 317 318 319 320 321 |
# File 'lib/oci8/oci8.rb', line 312 def select_one(sql, *bindvars) cursor = self.parse(sql) begin cursor.exec(*bindvars) row = cursor.fetch ensure cursor.close end return row end |
#username ⇒ Object
323 324 325 326 327 328 329 330 |
# File 'lib/oci8/oci8.rb', line 323 def username @username || begin exec('select user from dual') do |row| @username = row[0] end @username end end |