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.12"
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
# 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
  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
  end

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

  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_errorOCIError

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:



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

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

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



170
171
172
# File 'lib/oci8.rb', line 170

def self.oracle_client_version
  @@oracle_client_version
end

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



927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
# File 'ext/oci8/oci8.c', line 927

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


727
728
729
730
731
732
# File 'ext/oci8/oci8.c', line 727

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)


714
715
716
717
718
# File 'ext/oci8/oci8.c', line 714

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

#breakObject

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:



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

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



858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
# File 'ext/oci8/oci8.c', line 858

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



958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
# File 'ext/oci8/oci8.c', line 958

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_LENINT(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;
}

#commitObject

Commits the transaction.



651
652
653
654
655
656
# File 'ext/oci8/oci8.c', line 651

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_nameString

Returns the Oracle database character set name such as AL32UTF8.

Returns:

  • (String)

    Oracle database character set name

Since:

  • 2.1.0



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

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:



2032
2033
2034
2035
2036
2037
2038
2039
2040
# File 'lib/oci8/metadata.rb', line 2032

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:



2145
2146
2147
# File 'lib/oci8/metadata.rb', line 2145

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:



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

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:



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

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:



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

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:



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

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:



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

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:



2118
2119
2120
2121
2122
2123
2124
2125
2126
# File 'lib/oci8/metadata.rb', line 2118

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:



2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
# File 'lib/oci8/metadata.rb', line 2050

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

Parameters:

Returns:



2111
2112
2113
# File 'lib/oci8/metadata.rb', line 2111

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:



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

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


260
261
262
263
# File 'lib/oci8/oci8.rb', line 260

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

#logoffObject

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



628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
# File 'ext/oci8/oci8.c', line 628

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_lenInteger

Deprecated.

This has no effect since ruby-oci8 2.2.7. LONG, LONG RAW and XMLTYPE columns are fetched up to 4 gigabytes without this parameter.

Returns:

  • (Integer)

See Also:



744
745
746
747
748
749
# File 'ext/oci8/oci8.c', line 744

static VALUE oci8_long_read_len(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    rb_warning("OCI8.long_read_len has no effect since ruby-oci8 2.2.7");
    return svcctx->long_read_len;
}

#long_read_len=(length) ⇒ Object

Deprecated.

This has no effect since ruby-oci8 2.2.7. LONG, LONG RAW and XMLTYPE columns are fetched up to 4 gigabytes without this parameter.

Parameters:

  • length (Integer)

See Also:



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

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);
    rb_warning("OCI8.long_read_len= has no effect since ruby-oci8 2.2.7");
    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



893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
# File 'ext/oci8/oci8.c', line 893

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


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

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)


678
679
680
681
682
# File 'ext/oci8/oci8.c', line 678

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

#oracle_server_versionOCI8::OracleVersion

Returns the Oracle server version.

When the Oracle client version is 12c or earlier and the Oracle server version is 18c or later, this method doesn’t return full version number such as ‘18.3.0.0.0’. It returns version number whose number components after the first dot are zeros such as ‘18.0.0.0.0’.



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

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:



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

def parse(sql)
  @last_error = nil
  parse_internal(sql)
end

#pingBoolean

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



832
833
834
835
836
837
838
839
840
841
842
843
844
845
# File 'ext/oci8/oci8.c', line 832

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.



332
333
334
# File 'lib/oci8/oci8.rb', line 332

def prefetch_rows=(num)
  @prefetch_rows = num
end

#recv_timeoutFloat

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



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

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



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

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

#rollbackObject

Rollbacks the transaction.



663
664
665
666
667
668
# File 'ext/oci8/oci8.c', line 663

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.



305
306
307
308
309
310
311
312
313
314
315
# File 'lib/oci8/oci8.rb', line 305

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

#send_timeoutFloat

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



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

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



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

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

#usernameObject



317
318
319
320
321
322
323
324
# File 'lib/oci8/oci8.rb', line 317

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