Class: OCI8

Inherits:
OCIHandle show all
Defined in:
lib/oci8/oci8.rb,
lib/oci8.rb,
lib/oci8/cursor.rb,
lib/oci8/object.rb,
lib/oci8/object.rb,
lib/oci8/version.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,
lib/oci8/check_load_error.rb,
ext/oci8/lob.c,
ext/oci8/oci8.c,
ext/oci8/stmt.c,
ext/oci8/util.c,
ext/oci8/encoding.c,
ext/oci8/ocinumber.c,
ext/oci8/connection_pool.c

Overview

This file is loaded only on LoadError.

Defined Under Namespace

Modules: BindType, Math, Metadata, Object, Util Classes: BFILE, BLOB, CLOB, ConnectionPool, Cursor, InCondBindHelper, LOB, NCLOB, OracleVersion, TDO

Constant Summary collapse

VERSION = 
"2.2.4.1"
LIB_VERSION = 
rb_obj_freeze(rb_usascii_str_new_cstr(OCI8LIB_VERSION))
@@client_charset_name = 
charset_id2name(@@environment_handle.send(:attr_get_ub2, 31))

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

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, :SYSOPER, :SYSASM, :SYSBACKUP, :SYSDG or :SYSKM to privilege, otherwise “username/password as sysdba”, “username/password as sysoper”, etc. 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
 
# File 'lib/oci8/oci8.rb', line 97

def initialize(*args)
  if args.length == 1
    username, password, dbname, privilege = parse_connect_string(args[0])
  else
    username, password, dbname, privilege = args
  end

  if username.nil? and password.nil?
    cred = OCI_CRED_EXT
  end
  auth_mode = to_auth_mode(privilege)

  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
  else
    tcp_connect_timeout = OCI8::properties[:tcp_connect_timeout]
    connect_timeout = OCI8::properties[:connect_timeout]
    tcp_keepalive = OCI8::properties[:tcp_keepalive]
    if tcp_connect_timeout || connect_timeout || tcp_keepalive
      dbname = to_connect_descriptor(dbname, tcp_connect_timeout, connect_timeout, tcp_keepalive)
    end
  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
      # Sets the driver name displayed in V$SESSION_CONNECT_INFO.CLIENT_DRIVER
      # if both the client and the server are Oracle 11g or upper.
      # Only the first 8 chracters "ruby-oci" are displayed when the Oracle
      # server version is lower than 12.0.1.2.
      # 424: OCI_ATTR_DRIVER_NAME
      @session_handle.send(:attr_set_string, 424, "ruby-oci8 : #{OCI8::VERSION}")
    end
    server_attach(dbname, attach_mode)
    if OCI8.oracle_client_version >= OCI8::ORAVER_11_1
      self.send_timeout = OCI8::properties[:send_timeout] if OCI8::properties[:send_timeout]
      self.recv_timeout = OCI8::properties[:recv_timeout] if OCI8::properties[:recv_timeout]
    end
    session_begin(cred ? cred : OCI_CRED_RDBMS, auth_mode)
  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 = 100
  @username = nil
end

Instance Attribute Details

#last_error ⇒ OCIError

Returns:



30
31
32
 
# File 'lib/oci8/oci8.rb', line 30

def last_error
  @last_error
end

Class Method Details

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

Parameters:

  • charset_id (Integer) —

    Oracle character set id

Returns:

  • (String) —

    Oracle character set name or nil

Since:

  • 2.2.0



30
31
32
33
34
35
36
37
38
39
40
41
 
# File 'ext/oci8/encoding.c', line 30

VALUE oci8_s_charset_id2name(VALUE klass, VALUE csid)
{
    char buf[OCI_NLS_MAXBUFSZ];
    sword rv;

    Check_Type(csid, T_FIXNUM);
    rv = OCINlsCharSetIdToName(oci8_envhp, TO_ORATEXT(buf), sizeof(buf), (ub2)FIX2INT(csid));
    if (rv != OCI_SUCCESS) {
        return Qnil;
    }
    return rb_usascii_str_new_cstr(buf);
}

.charset_name2id(charset_name) ⇒ Integer

Returns the Oracle character set ID for the specified Oracle character set name if it is valid. Othewise, nil is returned.

Parameters:

  • charset_name (String) —

    Oracle character set name

Returns:

  • (Integer) —

    Oracle character set id or nil

Since:

  • 2.2.0



54
55
56
57
58
59
60
61
62
63
 
# File 'ext/oci8/encoding.c', line 54

static VALUE oci8_s_charset_name2id(VALUE klass, VALUE name)
{
    ub2 rv;

    rv = OCINlsCharSetNameToId(oci8_envhp, TO_ORATEXT(StringValueCStr(name)));
    if (rv == 0) {
        return Qnil;
    }
    return INT2FIX(rv);
}

.error_message(message_no) ⇒ String

Get the Oracle error message specified by message_no. Its language depends on NLS_LANGUAGE.

Examples:

# 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)"

Parameters:

  • message_no (Integer) —

    Oracle error message number

Returns:

  • (String) —

    Oracle error message



370
371
372
373
 
# File 'ext/oci8/oci8.c', line 370

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

.in_cond(bind_name_prefix, array, type = nil, length = nil) ⇒ OCI8::InCondBindHelper

Creates a helper object to bind an array to paramters in IN-condition.

See Bind an Array to IN-condition

Parameters:

  • bind_name_prefix (Symbol) —

    prefix of the place holder name

  • array (Object) —

    an array of values to be bound.

  • type (Class) (defaults to: nil) —

    data type. This is used as the third argument of OCI8::Cursor#bind_param.

  • length (Integer) (defaults to: nil) —

    maximum bind length for string values. This is used as the fourth argument of OCI8::Cursor#bind_param.

Returns:



501
502
503
 
# File 'lib/oci8/oci8.rb', line 501

def self.in_cond(bind_name_prefix, array, type = nil, length = nil)
  InCondBindHelper.new(bind_name_prefix, array, type, length)
end

.oracle_client_version ⇒ OCI8::OracleVersion

Returns an OCI8::OracleVersion of the Oracle client version.

If this library is configured without ‘–with-runtime-check’, and compiled for Oracle 10.1 or lower, the major and minor numbers are determined at compile-time. The rests are zeros.

If this library is configured with ‘–with-runtime-check’ and the runtime Oracle library is Oracle 10.1 or lower, the major and minor numbers are determined at run-time. The rests are zeros.

Otherwise, it is the version retrieved from an OCI function OCIClientVersion().

Returns:

See Also:



163
164
165
 
# File 'lib/oci8.rb', line 163

def self.oracle_client_version
  @@oracle_client_version
end

.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, otherwise false. The default value is false.

: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: web.archive.org/web/20140521195004/https://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. 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 is false. This corresponds to oci8.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

:cancel_read_at_exit

true when read system calls are canceled at exit. Otherwise, false. The default value is false because it uses unusual technique which hooks read system calls issued by Oracle client library and it works only on Linux, OSX and Windows. This feature is added not to block ruby process termination when network quality is poor and packets are lost irregularly.

Since: 2.1.8

:tcp_connect_timeout

See timeout-parameters

Since: 2.2.2

:connect_timeout

See timeout-parameters

Since: 2.2.2

:send_timeout

See timeout-parameters

Since: 2.2.2

:recv_timeout

See timeout-parameters

Since: 2.2.2

:tcp_keepalive

See hanging-after-inactivity

Since: 2.2.4

:tcp_keepalive_time

See hanging-after-inactivity

Since: 2.2.4

Returns:

  • (a customized Hash)

Since:

  • 2.0.5



193
194
195
 
# File 'lib/oci8/properties.rb', line 193

def self.properties
  @@properties
end

Instance Method Details

#action=(action) ⇒ Object

Sets the specified value to V$SESSION.ACTION. This is also stored in V$SQL.ACTION and V$SQLAREA.ACTION when an SQL statement is first parsed in the Oracle server.

The specified value is sent to the server by piggybacking on the next network round trip issued by #exec, #ping and so on.

Parameters:

Since:

  • 2.0.3



1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
 
# File 'ext/oci8/oci8.c', line 1018

static VALUE oci8_set_action(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    const char *ptr;
    ub4 size;

    if (!NIL_P(val)) {
        OCI8SafeStringValue(val);
        ptr = RSTRING_PTR(val);
        size = RSTRING_LEN(val);
    } else {
        ptr = "";
        size = 0;
    }
    chker2(OCIAttrSet(svcctx->usrhp, OCI_HTYPE_SESSION, (dvoid*)ptr,
                      size, OCI_ATTR_ACTION, oci8_errhp),
           &svcctx->base);
    return val;
}

#autocommit=(autocommit_mode) ⇒ Object

Sets the autocommit mode. The default value is false.

Parameters:

  • autocommit_mode (Boolean) 


813
814
815
816
817
818
 
# File 'ext/oci8/oci8.c', line 813

static VALUE oci8_set_autocommit(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(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) 


800
801
802
803
804
 
# File 'ext/oci8/oci8.c', line 800

static VALUE oci8_autocommit_p(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    return svcctx->is_autocommit ? Qtrue : Qfalse;
}

#break ⇒ Object

Cancels the executing SQL.

Note that this doesn’t work when the following cases.

  • The Oracle server runs on Windows.

  • Out-of-band data are blocked by a firewall or by a VPN.

In the latter case, create an sqlnet.ora file in the path specified by the TNS_ADMIN environment variable that sets DISABLE_OOB=on.

See Also:



873
874
875
876
877
878
879
880
881
882
 
# File 'ext/oci8/oci8.c', line 873

static VALUE oci8_break(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);

    if (NIL_P(svcctx->executing_thread)) {
        return Qfalse;
    }
    rb_thread_wakeup(svcctx->executing_thread);
    return Qtrue;
}

#charset_id2name(charset_id) ⇒ String

Deprecated.

Use charset_id2name instead.

Returns the Oracle character set name from the specified character set ID if it is valid. Otherwise, nil is returned.

Parameters:

  • charset_id (Integer) —

    Oracle character set id

Returns:

  • (String) —

    Oracle character set name or nil

Since:

  • 2.0.0



175
176
177
178
179
 
# File 'ext/oci8/encoding.c', line 175

static VALUE oci8_charset_id2name(VALUE svc, VALUE name)
{
    rb_warning("Use OCI8.charset_id2name instead of OCI8#charset_id2name.");
    return oci8_s_charset_id2name(Qnil, name);
}

#charset_name2id(charset_name) ⇒ Integer

Deprecated.

Use charset_name2id instead.

Returns the Oracle character set ID for the specified Oracle character set name if it is valid. Othewise, nil is returned.

Parameters:

  • charset_name (String) —

    Oracle character set name

Returns:

  • (Integer) —

    Oracle character set id or nil

Since:

  • 2.0.0



157
158
159
160
161
 
# File 'ext/oci8/encoding.c', line 157

static VALUE oci8_charset_name2id(VALUE svc, VALUE name)
{
    rb_warning("Use OCI8.charset_name2id instead of OCI8#charset_name2id.");
    return oci8_s_charset_name2id(Qnil, name);
}

#client_identifier=(client_identifier) ⇒ Object

Sets the specified value to V$SESSION.CLIENT_IDENTIFIER.

The specified value is sent to the server by piggybacking on the next network round trip issued by #exec, #ping and so on.

Parameters:

Since:

  • 2.0.3



949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
 
# File 'ext/oci8/oci8.c', line 949

static VALUE oci8_set_client_identifier(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    const char *ptr;
    ub4 size;

    if (!NIL_P(val)) {
        OCI8SafeStringValue(val);
        ptr = RSTRING_PTR(val);
        size = RSTRING_LEN(val);
    } else {
        ptr = "";
        size = 0;
    }

    if (size > 0 && ptr[0] == ':') {
        rb_raise(rb_eArgError, "client identifier should not start with ':'.");
    }
    chker2(OCIAttrSet(svcctx->usrhp, OCI_HTYPE_SESSION, (dvoid*)ptr,
                      size, OCI_ATTR_CLIENT_IDENTIFIER, oci8_errhp),
           &svcctx->base);
    return val;
}

#client_info=(client_info) ⇒ Object

Sets the specified value to V$SESSION.CLIENT_INFO.

The specified value is sent to the server by piggybacking on the next network round trip issued by #exec, #ping and so on.

Parameters:

Since:

  • 2.0.3



1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
 
# File 'ext/oci8/oci8.c', line 1049

static VALUE oci8_set_client_info(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    const char *ptr;
    ub4 size;

    if (!NIL_P(val)) {
        OCI8SafeStringValue(val);
        ptr = RSTRING_PTR(val);
        size = RSTRING_LEN(val);
    } else {
        ptr = "";
        size = 0;
    }
    chker2(OCIAttrSet(svcctx->usrhp, OCI_HTYPE_SESSION, (dvoid*)ptr,
                      size, OCI_ATTR_CLIENT_INFO, oci8_errhp),
           &svcctx->base);
    return val;
}

#commit ⇒ Object

Commits the transaction.



737
738
739
740
741
742
 
# File 'ext/oci8/oci8.c', line 737

static VALUE oci8_commit(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(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.

Returns:

  • (String) —

    Oracle database character set name

Since:

  • 2.1.0



358
359
360
 
# File 'lib/oci8/oci8.rb', line 358

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

OCI8::Metadata::Table

View

OCI8::Metadata::View

Procedure

OCI8::Metadata::Procedure

Function

OCI8::Metadata::Function

Package

OCI8::Metadata::Package

Type

OCI8::Metadata::Type

Synonym

OCI8::Metadata::Synonym

Sequence

OCI8::Metadata::Sequence

Parameters:

Returns:



2024
2025
2026
2027
2028
2029
2030
2031
2032
 
# File 'lib/oci8/metadata.rb', line 2024

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

Parameters:

Returns:



2137
2138
2139
 
# File 'lib/oci8/metadata.rb', line 2137

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

#describe_function(function_name) ⇒ OCI8::Metadata::Function

Returns function information

Parameters:

Returns:



2089
2090
2091
 
# File 'lib/oci8/metadata.rb', line 2089

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

#describe_package(package_name) ⇒ OCI8::Metadata::Package

Returns package information

Parameters:

Returns:



2096
2097
2098
 
# File 'lib/oci8/metadata.rb', line 2096

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

#describe_procedure(procedure_name) ⇒ OCI8::Metadata::Procedure

Returns procedure information

Parameters:

Returns:



2082
2083
2084
 
# File 'lib/oci8/metadata.rb', line 2082

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

#describe_schema(schema_name) ⇒ OCI8::Metadata::Schema

Returns schema information

Parameters:

Returns:



2130
2131
2132
 
# File 'lib/oci8/metadata.rb', line 2130

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

#describe_sequence(sequence_name) ⇒ OCI8::Metadata::Sequence

Returns sequence information

Parameters:

Returns:



2123
2124
2125
 
# File 'lib/oci8/metadata.rb', line 2123

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

Parameters:

Returns:



2110
2111
2112
2113
2114
2115
2116
2117
2118
 
# File 'lib/oci8/metadata.rb', line 2110

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.

Parameters:

  • table_name (String)
  • table_only (Boolean) (defaults to: false) —

    (default: false)

Returns:



2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
 
# File 'lib/oci8/metadata.rb', line 2042

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
      metadata = __describe(table_name, OCI8::Metadata::Unknown, true)
      case metadata
      when OCI8::Metadata::Table, OCI8::Metadata::View
        return metadata
      when OCI8::Metadata::Synonym
        table_name = metadata.translated_name
        if metadata.obj_link and metadata.link.nil?
          # table_name is a synonym in a remote database for an object in the
          # remote database itself.
          table_name = "#{table_name}@#{metadata.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

Parameters:

Returns:



2103
2104
2105
 
# File 'lib/oci8/metadata.rb', line 2103

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

#describe_view(view_name) ⇒ OCI8::Metadata::View

Returns view information

Parameters:

Returns:



2075
2076
2077
 
# File 'lib/oci8/metadata.rb', line 2075

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



266
267
268
269
 
# File 'lib/oci8/oci8.rb', line 266

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.



714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
 
# File 'ext/oci8/oci8.c', line 714

static VALUE oci8_svcctx_logoff(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(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->base.closed = 1;
        svcctx->logoff_strategy = NULL;
        chker2(oci8_call_without_gvl(svcctx, strategy->execute, data), &svcctx->base);
    }
    return Qtrue;
}

#long_read_len ⇒ Integer

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, the fetched valud is truncated and the value of #last_error become OCISuccessWithInfo whose message is “ORA-01406: fetched column value was truncated”.

Note: long_read_len is also used for maximum length of XMLTYPE data type.

Returns:

  • (Integer)

See Also:



835
836
837
838
839
 
# File 'ext/oci8/oci8.c', line 835

static VALUE oci8_long_read_len(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    return svcctx->long_read_len;
}

#long_read_len=(length) ⇒ Object

Sets the maximum length in bytes to fetch a LONG or LONG RAW column.

Parameters:

  • length (Integer)

See Also:



850
851
852
853
854
855
856
 
# File 'ext/oci8/oci8.c', line 850

static VALUE oci8_set_long_read_len(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    Check_Type(val, T_FIXNUM);
    RB_OBJ_WRITE(self, &svcctx->long_read_len, val);
    return val;
}

#module= ⇒ Object

Sets the specified value to V$SESSION.MODULE. This is also stored in V$SQL.MODULE and V$SQLAREA.MODULE when an SQL statement is first parsed in the Oracle server.

Parameters:

Since:

  • 2.0.3



984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
 
# File 'ext/oci8/oci8.c', line 984

static VALUE oci8_set_module(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    const char *ptr;
    ub4 size;

    if (!NIL_P(val)) {
        OCI8SafeStringValue(val);
        ptr = RSTRING_PTR(val);
        size = RSTRING_LEN(val);
    } else {
        ptr = "";
        size = 0;
    }
    chker2(OCIAttrSet(svcctx->usrhp, OCI_HTYPE_SESSION, (dvoid*)ptr,
                      size, OCI_ATTR_MODULE, oci8_errhp),
           &svcctx->base);
    return self;
}

#non_blocking=(non_blocking_mode) ⇒ Object

Sets true to enable non-blocking mode, false otherwise. The default value is true except ruby 1.8.

When the connection is in non-blocking mode (non_blocking = true), an SQL execution blocks the thread running the SQL. It does’t prevent other threads. The blocking thread can be canceled by #break.

When in blocking mode (non_blocking = false), an SQL execution blocks not only the thread, but also the ruby process itself. It makes the whole application stop until the SQL finishes.

Parameters:

  • non_blocking_mode (Boolean) 


787
788
789
790
791
792
 
# File 'ext/oci8/oci8.c', line 787

static VALUE oci8_set_non_blocking(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    svcctx->non_blocking = RTEST(val);
    return val;
}

#non_blocking? ⇒ Boolean

Returns true if the connection is in non-blocking mode, false otherwise.

See Also:

Returns:

  • (Boolean) 


764
765
766
767
768
 
# File 'ext/oci8/oci8.c', line 764

static VALUE oci8_non_blocking_p(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    return svcctx->non_blocking ? Qtrue : Qfalse;
}

#oracle_server_version ⇒ OCI8::OracleVersion

Returns the Oracle server version.

Returns:

See Also:



350
351
352
 
# File 'lib/oci8/oci8.rb', line 350

def oracle_server_version
  @oracle_server_version ||= OCI8::OracleVersion.new(oracle_server_vernum)
end

#parse(sql) ⇒ OCI8::Cursor

Returns a prepared SQL handle.

Parameters:

  • sql (String) —

    SQL statement

Returns:



167
168
169
170
 
# File 'lib/oci8/oci8.rb', line 167

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.

This also flushes all the pending OCI client-side calls such as #action=, #client_identifier=, #client_info= and #module=.

Oracle 10.2 client or upper

A dummy round trip call is made by the OCI function OCIPing added 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



923
924
925
926
927
928
929
930
931
932
933
934
935
936
 
# File 'ext/oci8/oci8.c', line 923

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 100. 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: The default value had been 1 before ruby-oci8 2.2.0.



337
338
339
 
# File 'lib/oci8/oci8.rb', line 337

def prefetch_rows=(num)
  @prefetch_rows = num
end

#recv_timeout ⇒ Float

Returns receive timeout in seconds. Zero means no timeout. This is equivalent to SQLNET.RECV_TIMEOUT in client-side sqlnet.ora.

Returns:

  • (Float) —

    seconds

Raises:

  • (NotImplementedError)

See Also:

Since:

  • 2.1.8 and Oracle 11.1



412
413
414
415
 
# File 'lib/oci8/oci8.rb', line 412

def recv_timeout
  # OCI_ATTR_RECEIVE_TIMEOUT = 436
  @server_handle.send(:attr_get_ub4, 436).to_f / 1000
end

#recv_timeout=(timeout)

This method returns an undefined value.

Sets receive timeout in seconds. Zero means no timeout. This is equivalent to SQLNET.RECV_TIMEOUT in client-side sqlnet.ora.

If you need to set receive timeout while establishing a connection, use timeout parameters in OCI8::properties instead.

Note that the connection becomes unusable on timeout.

If you have trouble by setting this, don’t use it because it uses an undocumented OCI handle attribute.

Parameters:

  • timeout (Float)

Raises:

  • (NotImplementedError)

See Also:

Since:

  • 2.1.8 and Oracle 11.1



432
433
434
435
 
# File 'lib/oci8/oci8.rb', line 432

def recv_timeout=(timeout)
  # OCI_ATTR_RECEIVE_TIMEOUT = 436
  @server_handle.send(:attr_set_ub4, 436, timeout * 1000)
end

#rollback ⇒ Object

Rollbacks the transaction.



749
750
751
752
753
754
 
# File 'ext/oci8/oci8.c', line 749

static VALUE oci8_rollback(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(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.



311
312
313
314
315
316
317
318
319
320
 
# File 'lib/oci8/oci8.rb', line 311

def select_one(sql, *bindvars)
  cursor = self.parse(sql)
  begin
    cursor.exec(*bindvars)
    row = cursor.fetch
  ensure
    cursor.close
  end
  return row
end

#send_timeout ⇒ Float

Returns send timeout in seconds. Zero means no timeout. This is equivalent to SQLNET.SEND_TIMEOUT in client-side sqlnet.ora.

Returns:

  • (Float) —

    seconds

Raises:

  • (NotImplementedError)

See Also:

Since:

  • 2.1.8 and Oracle 11.1



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

def send_timeout
  # OCI_ATTR_SEND_TIMEOUT = 435
  @server_handle.send(:attr_get_ub4, 435).to_f / 1000
end

#send_timeout=(timeout)

This method returns an undefined value.

Sets send timeout in seconds. Zero means no timeout. This is equivalent to SQLNET.SEND_TIMEOUT in client-side sqlnet.ora.

If you need to set send timeout while establishing a connection, use timeout parameters in OCI8::properties instead.

Note that the connection becomes unusable on timeout.

If you have trouble by setting this, don’t use it because it uses an undocumented OCI handle attribute.

Parameters:

  • timeout (Float)

Raises:

  • (NotImplementedError)

See Also:

Since:

  • 2.1.8 and Oracle 11.1



400
401
402
403
 
# File 'lib/oci8/oci8.rb', line 400

def send_timeout=(timeout)
  # OCI_ATTR_SEND_TIMEOUT = 435
  @server_handle.send(:attr_set_ub4, 435, timeout * 1000)
end

#username ⇒ Object 



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

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