Class: OCI8

Inherits:
OCIHandle show all
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))
@@properties = 
{
  :length_semantics => :byte,
  :bind_string_as_nchar => false,
  :float_conversion_type => :ruby,
  :statement_cache_size => 0,
}

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(*args) ⇒ OCI8

call-seq:

new(username, password, dbname = nil, privilege = nil)

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



95
96
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
163
164
165
166
167
168
169
170
 
# File 'lib/oci8/oci8.rb', line 95

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

  if mode.nil? and cred.nil?
    # logon by the OCI function OCILogon2().
    logon2_mode = 0
    if dbname.is_a? OCI8::ConnectionPool
      @pool = dbname # to prevent GC from freeing the connection pool.
      dbname = dbname.send(:pool_name)
      logon2_mode |= 0x0200 # OCI_LOGON2_CPOOL
    end
    if stmt_cache_size
      # enable statement caching
      logon2_mode |= 0x0004 # OCI_LOGON2_STMTCACHE
    end

    logon2(username, password, dbname, logon2_mode)

    if stmt_cache_size
      # set statement cache size
      attr_set_ub4(176, stmt_cache_size) # 176: OCI_ATTR_STMTCACHESIZE
    end
  else
    # logon by the OCI function OCISessionBegin().
    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
    end
    if stmt_cache_size
      # enable statement caching
      attach_mode |= 0x0004 # OCI_STMT_CACHE
    end

    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
    server_attach(dbname, attach_mode)
    session_begin(cred ? cred : OCI_CRED_RDBMS, mode ? mode : OCI_DEFAULT)

    if stmt_cache_size
      # set statement cache size
      attr_set_ub4(176, stmt_cache_size) # 176: OCI_ATTR_STMTCACHESIZE
    end
  end

  @prefetch_rows = nil
  @username = nil
end

Instance Attribute Details

#last_error ⇒ Object

Class Method Details

.[](name) ⇒ Object

Raises:

  • (IndexError) 


19
20
21
22
 
# File 'lib/oci8/properties.rb', line 19

def @@properties.[](name)
  raise IndexError, "No such property name: #{name}" unless @@properties.has_key?(name)
  super(name)
end

.[]=(name, val) ⇒ Object

Raises:

  • (IndexError) 


24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/oci8/properties.rb', line 24

def @@properties.[]=(name, val)
  raise IndexError, "No such property name: #{name}" unless @@properties.has_key?(name)
  case name
  when :length_semantic
    if val != :byte and val != :char
      raise ArgumentError, "Invalid property value #{val} for :length_semantics."
    end
  when :bind_string_as_nchar
    val = val ? true : false
  when :float_conversion_type
    # handled by native code in oci8lib_xx.so.
    OCI8.__set_property(name, val)
  when :statement_cache_size
    if OCI8.oracle_client_version < OCI8::ORAVER_9_2
      raise RuntimeError, ":statement_cache_size is disabled on Oracle 9iR1 client."
    end
    val = val.to_i
    raise ArgumentError, "The property value for :statement_cache_size must not be negative." if val < 0
  end
  super(name, val)
end

.client_charset_name ⇒ String

Returns the client-side Oracle character set name.

Returns:

  • (String) —

    client-side character set name

Since:

  • 2.1.0



380
381
382
 
# File 'lib/oci8/oci8.rb', line 380

def self.client_charset_name
  @@client_charset_name
end

.encoding ⇒ Encoding

Returns the Oracle client encoding.

When string data, such as SQL statements and bind variables, are passed to Oracle, they are converted to OCI8.encoding in advance.

# When OCI8.encoding is ISO-8859-1,
conn.exec('insert into country_code values(:1, :2, :3)',
          'AT', 'Austria', "\u00d6sterreichs")
# "\u00d6sterreichs" is 'Österreichs' encoded by UTF-8.
# It is converted to ISO-8859-1 before it is passed to
# the Oracle C API.

When string data, such as fetched values and bind variable for output, are retrieved from Oracle, they are encoded by OCI8.encoding if Encoding.default_internal is nil. If it isn’t nil, they are converted from OCI8.encoding to Encoding.default_internal.

If OCI8.encoding is ASCII-8BIT, no encoding conversions are done.

Returns:

  • (Encoding)

Since:

  • 2.0.0 and ruby 1.9



247
248
249
250
 
# File 'ext/oci8/encoding.c', line 247

static VALUE oci8_get_encoding(VALUE klass)
{
    return rb_enc_from_encoding(oci8_encoding);
}

.encoding=(enc) ⇒ Object

Sets Oracle client encoding. You must not use this method. You should set the environment variable NLS_LANG properly to change OCI8.encoding.

Parameters:

  • enc (Encoding)

Since:

  • 2.0.0 and ruby 1.9



263
264
265
266
267
268
269
270
271
 
# File 'ext/oci8/encoding.c', line 263

static VALUE oci8_set_encoding(VALUE klass, VALUE encoding)
{
    if (NIL_P(encoding)) {
        oci8_encoding = NULL;
    } else {
        oci8_encoding = rb_to_encoding(encoding);
    }
    return encoding;
}

.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.error_message(1) # => "ORA-00001: unique constraint (%s.%s) violated"

# When NLS_LANG is FRENCH_FRANCE.AL32UTF8
OCI8.error_message(1) # => "ORA-00001: violation de contrainte unique (%s.%s)"

Returns:

Parameters:

  • message_no (Fixnum) —

    Oracle error message number

Returns:

  • (String) —

    Oracle error message



214
215
216
217
 
# File 'ext/oci8/oci8.c', line 214

static VALUE oci8_s_error_message(VALUE klass, VALUE msgid)
{
    return oci8_get_error_message(NUM2UINT(msgid), NULL);
}

.nls_ratio ⇒ Integer

Gets NLS ratio, maximum number of bytes per one character of the current NLS chracter set. It is a factor to calculate the internal buffer size of a string bind variable whose nls length semantics is char.

Returns:

  • (Integer)

Returns:

  • (Fixnum) —

    NLS ratio

Since:

  • 2.1.0



189
190
191
192
 
# File 'ext/oci8/encoding.c', line 189

static VALUE oci8_get_nls_ratio(VALUE klass)
{
    return INT2NUM(oci8_nls_ratio);
}

.nls_ratio=(integer) ⇒ Object

Sets NLS ratio, maximum number of bytes per one character of the current NLS chracter set. It is initialized in ‘oci8/encoding-init.rb’ when oci8 is required. You have no need to set it explicitly.

Parameters:

  • integer (Fixnum) —

    NLS ratio

Since:

  • 2.1.0



205
206
207
208
209
210
211
212
213
 
# File 'ext/oci8/encoding.c', line 205

static VALUE oci8_set_nls_ratio(VALUE klass, VALUE val)
{
    int v = NUM2INT(val);
    if (v <= 0) {
        rb_raise(rb_eRangeError, "expected a positive integer but %d", v);
    }
    oci8_nls_ratio = v;
    return val;
}

.properties ⇒ Object

call-seq:

OCI8.properties -> a customized Hash

(new in 2.0.5)

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

(new in 2.1.0)

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

:bind_string_as_nchar

true when string bind variables are bound as NCHAR, otherwise false. The default value is false.

:float_conversion_type

(new in 2.1.0)

: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

:statement_cache_size

(new in 2.1.1)

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



97
98
99
 
# File 'lib/oci8/properties.rb', line 97

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

Since:

  • 2.0.3



986
987
988
989
990
991
992
993
994
995
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
 
# File 'ext/oci8/oci8.c', line 986

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.



682
683
684
685
686
687
 
# File 'ext/oci8/oci8.c', line 682

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.

Returns:

  • (Boolean) 


670
671
672
673
674
 
# File 'ext/oci8/oci8.c', line 670

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



733
734
735
736
737
738
739
740
741
742
743
744
745
 
# File 'ext/oci8/oci8.c', line 733

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;

Parameters:

  • charset_id (Fixnum) —

    Oracle character set id

Returns:

  • (String) —

    Oracle character set name or nil

Since:

  • 2.0.0



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;

Parameters:

  • charset_name (String) —

    Oracle character set name

Returns:

  • (Fixnum) —

    Oracle character set id or nil

Since:

  • 2.0.0



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

Since:

  • 2.0.3



844
845
846
847
848
849
850
851
852
853
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
 
# File 'ext/oci8/oci8.c', line 844

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

Since:

  • 2.0.3



1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
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
 
# File 'ext/oci8/oci8.c', line 1049

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.



564
565
566
567
568
569
 
# File 'ext/oci8/oci8.c', line 564

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.

Returns:

  • (String) —

    Oracle database character set name

Since:

  • 2.1.0



372
373
374
 
# File 'lib/oci8/oci8.rb', line 372

def database_charset_name
  charset_id2name(@server_handle.send(:attr_get_ub2, OCI_ATTR_CHARSET_ID))
end

#describe_any(object_name) ⇒ Object

return a subclass of OCI8::Metadata::Base which has information about object_name. OCI8::Metadata::Table, OCI8::Metadata::View, OCI8::Metadata::Procedure, OCI8::Metadata::Function, OCI8::Metadata::Package, OCI8::Metadata::Type, OCI8::Metadata::Synonym or OCI8::Metadata::Sequence



1994
1995
1996
 
# File 'lib/oci8/metadata.rb', line 1994

def describe_any(object_name)
  __describe(object_name, OCI8::Metadata::Unknown, true)
end

#describe_database(database_name) ⇒ Object

returns a OCI8::Metadata::Database.



2054
2055
2056
 
# File 'lib/oci8/metadata.rb', line 2054

def describe_database(database_name)
  __describe(database_name, OCI8::Metadata::Database, false)
end

#describe_function(function_name) ⇒ Object

returns a OCI8::Metadata::Function in the current schema.



2030
2031
2032
 
# File 'lib/oci8/metadata.rb', line 2030

def describe_function(function_name)
  __describe(function_name, OCI8::Metadata::Function, false)
end

#describe_package(package_name) ⇒ Object

returns a OCI8::Metadata::Package in the current schema.



2034
2035
2036
 
# File 'lib/oci8/metadata.rb', line 2034

def describe_package(package_name)
  __describe(package_name, OCI8::Metadata::Package, false)
end

#describe_procedure(procedure_name) ⇒ Object

returns a OCI8::Metadata::Procedure in the current schema.



2026
2027
2028
 
# File 'lib/oci8/metadata.rb', line 2026

def describe_procedure(procedure_name)
  __describe(procedure_name, OCI8::Metadata::Procedure, false)
end

#describe_schema(schema_name) ⇒ Object

returns a OCI8::Metadata::Schema in the database.



2050
2051
2052
 
# File 'lib/oci8/metadata.rb', line 2050

def describe_schema(schema_name)
  __describe(schema_name, OCI8::Metadata::Schema, false)
end

#describe_sequence(sequence_name) ⇒ Object

returns a OCI8::Metadata::Sequence in the current schema.



2046
2047
2048
 
# File 'lib/oci8/metadata.rb', line 2046

def describe_sequence(sequence_name)
  __describe(sequence_name, OCI8::Metadata::Sequence, false)
end

#describe_synonym(synonym_name, check_public_also = true) ⇒ Object

returns a OCI8::Metadata::Synonym in the current schema.



2042
2043
2044
 
# File 'lib/oci8/metadata.rb', line 2042

def describe_synonym(synonym_name, check_public_also = true)
  __describe(synonym_name, OCI8::Metadata::Synonym, check_public_also)
end

#describe_table(table_name, table_only = false) ⇒ Object

returns a OCI8::Metadata::Table or a OCI8::Metadata::View. If the name is a current schema’s synonym name or a public synonym name, it returns a OCI8::Metadata::Table or a OCI8::Metadata::View which the synonym refers.

If the second argument is true, this returns a OCI8::Metadata::Table in the current schema.



2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
 
# File 'lib/oci8/metadata.rb', line 2004

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.
    metadata = __describe(table_name, OCI8::Metadata::Unknown, true)
    case metadata
    when OCI8::Metadata::Table, OCI8::Metadata::View
      metadata
    when OCI8::Metadata::Synonym
      describe_table(metadata.translated_name)
    else
      raise OCIError.new("ORA-04043: object #{table_name} does not exist", 4043)
    end
  end
end

#describe_type(type_name) ⇒ Object

returns a OCI8::Metadata::Type in the current schema.



2038
2039
2040
 
# File 'lib/oci8/metadata.rb', line 2038

def describe_type(type_name)
  __describe(type_name, OCI8::Metadata::Type, false)
end

#describe_view(view_name) ⇒ Object

returns a OCI8::Metadata::View in the current schema.



2022
2023
2024
 
# File 'lib/oci8/metadata.rb', line 2022

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



275
276
277
278
 
# File 'lib/oci8/oci8.rb', line 275

def exec(sql, *bindvars, &block)
  @last_error = nil
  exec_internal(sql, *bindvars, &block)
end

#inspect ⇒ Object 



340
341
342
 
# File 'lib/oci8/oci8.rb', line 340

def inspect
  "#<OCI8:#{username}>"
end

#logoff ⇒ Object

Disconnects from the Oracle server. The uncommitted transaction is rollbacked.



541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
 
# File 'ext/oci8/oci8.c', line 541

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.

Returns:

  • (Fixnum) 


701
702
703
704
705
 
# File 'ext/oci8/oci8.c', line 701

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



717
718
719
720
721
722
723
 
# File 'ext/oci8/oci8.c', line 717

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

Since:

  • 2.0.3



917
918
919
920
921
922
923
924
925
926
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
 
# File 'ext/oci8/oci8.c', line 917

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.



643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
 
# File 'ext/oci8/oci8.c', line 643

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

Returns:

  • (Boolean) 


593
594
595
596
597
598
599
600
601
602
603
604
 
# File 'ext/oci8/oci8.c', line 593

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 an OCI8::OracleVersion of the Oracle server version.

See also: OCI8.oracle_client_version

Returns:



349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
 
# File 'lib/oci8/oci8.rb', line 349

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.

Parameters:

  • sql (String) —

    SQL statement

Returns:



176
177
178
179
 
# File 'lib/oci8/oci8.rb', line 176

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.

Returns:

  • (Boolean)

Since:

  • 2.0.2



803
804
805
806
807
808
809
810
811
812
813
814
815
816
 
# File 'ext/oci8/oci8.c', line 803

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=(number) ⇒ 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.



758
759
760
761
762
 
# File 'ext/oci8/oci8.c', line 758

static VALUE oci8_set_prefetch_rows(VALUE self, VALUE val)
{
    rb_ivar_set(self, id_at_prefetch_rows, val);
    return val;
}

#rollback ⇒ Object

Rollbacks the transaction.



577
578
579
580
581
582
 
# File 'ext/oci8/oci8.c', line 577

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.

Parameters:

  • sql (String) —

    SQL statement

  • bindvars (Object) —

    bind variables

Returns:

  • (Array) —

    an array of first row.



320
321
322
323
324
325
326
327
328
329
 
# File 'lib/oci8/oci8.rb', line 320

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 



331
332
333
334
335
336
337
338
 
# File 'lib/oci8/oci8.rb', line 331

def username
  @username || begin
    exec('select user from dual') do |row|
      @username = row[0]
    end
    @username
  end
end