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

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 or :SYSOPER to privilege, otherwise “username/password as sysdba” or “username/password as sysoper” as a single argument.

OCI8.new('sys', 'change_on_install', nil, :SYSDBA)

or

OCI8.new('sys/change_on_install as sysdba')

external OS authentication

Set nil to username and password, or “/” as a single argument.

OCI8.new(nil, nil)

or

OCI8.new('/')

To connect to a remote host:

OCI8.new(nil, nil, 'dbname')

or

OCI8.new('/@dbname')

proxy authentication

Enclose end user’s username with square brackets and add it at the end of proxy user’s username.

OCI8.new('proxy_user_name[end_user_name]', 'proxy_password')

or

OCI8.new('proxy_user_name[end_user_name]/proxy_password')



97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
 
# File 'lib/oci8/oci8.rb', line 97

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

  if username.nil? and password.nil?
    cred = OCI_CRED_EXT
  end
  case mode
  when :SYSDBA
    mode = OCI_SYSDBA
  when :SYSOPER
    mode = OCI_SYSOPER
  when :SYSASM
    if OCI8.oracle_client_version < OCI8::ORAVER_11_1
      raise "SYSASM is not supported on Oracle version #{OCI8.oracle_client_version}"
    end
    mode = OCI_SYSASM
  when nil
    # do nothing
  else
    raise "unknown privilege type #{mode}"
  end

  stmt_cache_size = OCI8.properties[:statement_cache_size]
  stmt_cache_size = nil if stmt_cache_size == 0

  attach_mode = 0
  if dbname.is_a? OCI8::ConnectionPool
    @pool = dbname # to prevent GC from freeing the connection pool.
    dbname = dbname.send(:pool_name)
    attach_mode |= 0x0200 # OCI_CPOOL and OCI_LOGON2_CPOOL
  end
  if stmt_cache_size
    # enable statement caching
    attach_mode |= 0x0004 # OCI_STMT_CACHE and OCI_LOGON2_STMTCACHE
  end

  if true
    # logon by the OCI function OCISessionBegin().
    allocate_handles()
    @session_handle.send(:attr_set_string, OCI_ATTR_USERNAME, username) if username
    @session_handle.send(:attr_set_string, OCI_ATTR_PASSWORD, password) if password
    if @@oracle_client_version >= ORAVER_11_1
      # 'rubyoci8' is displayed in V$SESSION_CONNECT_INFO.CLIENT_DRIVER
      # if both the client and the server are Oracle 11g or upper.
      # 424: OCI_ATTR_DRIVER_NAME
      @session_handle.send(:attr_set_string, 424, 'rubyoci8')
    end
    server_attach(dbname, attach_mode)
    session_begin(cred ? cred : OCI_CRED_RDBMS, mode ? mode : OCI_DEFAULT)
  else
    # logon by the OCI function OCILogon2().
    logon2(username, password, dbname, attach_mode)
  end

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

  @prefetch_rows = nil
  @username = nil
end

Instance Attribute Details

#last_error ⇒ OCIError

Returns:



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

def last_error
  @last_error
end

Class Method Details

.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 (Fixnum) —

    Oracle error message number

Returns:

  • (String) —

    Oracle error message



367
368
369
370
 
# File 'ext/oci8/oci8.c', line 367

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

.properties ⇒ a customized Hash

Returns a Hash which ruby-oci8 global settings. The hash’s setter and getter methods are customized to check property names and values.

# get properties
OCI8.properties[:bind_string_as_nchar]  # => false
OCI8.properties[:invalid_property_name] # raises an IndexError

# set properties
OCI8.properties[:bind_string_as_nchar] = true
OCI8.properties[:invalid_property_name] = true # raises an IndexError

Supported properties are listed below:

:length_semantics

:char when Oracle character length is counted by the number of characters. :byte when it is counted by the number of bytes. The default setting is :byte because :char causes unexpected behaviour on Oracle 9i.

Since: 2.1.0

:bind_string_as_nchar

true when string bind variables are bound as NCHAR, 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: rubyforge.org/forum/forum.php?thread_id=50030&forum_id=1078

Since: 2.1.0

:statement_cache_size

The statement cache size per each session. The default size is 0, which means no statement cache, since 2.1.2. It was 20 in 2.1.1. This feature is available on Oracle 9iR2 or later. See: docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci09adv.htm#i471377

Since: 2.1.1

:events_mode

true when Fast Application Notification (FAN) Support is enabled. false when it is disabled. The default value 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

Returns:

  • (a customized Hash)

Since:

  • 2.0.5



146
147
148
 
# File 'lib/oci8/properties.rb', line 146

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.

Oracle 10g client or upper

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

Oracle 9i client or lower

This executes the following PL/SQL block internally.

BEGIN
  DBMS_APPLICATION_INFO.SET_ACTION(:action);
END;

See Oracle Manual: Oracle Database PL/SQL Packages and Types Reference

Parameters:

Since:

  • 2.0.3



1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
 
# File 'ext/oci8/oci8.c', line 1120

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;
    }
    if (oracle_client_version >= ORAVER_10_1) {
        /* Oracle 10g or upper */
        chker2(OCIAttrSet(svcctx->usrhp, OCI_HTYPE_SESSION, (dvoid*)ptr,
                          size, OCI_ATTR_ACTION, oci8_errhp),
               &svcctx->base);
    } 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(svcctx,
                      "BEGIN\n"
                      "  DBMS_APPLICATION_INFO.SET_ACTION(:action);\n"
                      "END;\n", 0, NULL, 1, bind_vars, 1);
    }
    return val;
}

#autocommit=(autocommit_mode) ⇒ Object

Sets the autocommit mode. The default value is false.

Parameters:

  • autocommit_mode (Boolean) 


830
831
832
833
834
835
 
# File 'ext/oci8/oci8.c', line 830

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) 


817
818
819
820
821
 
# File 'ext/oci8/oci8.c', line 817

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:



890
891
892
893
894
895
896
897
898
899
900
901
902
 
# File 'ext/oci8/oci8.c', line 890

static VALUE oci8_break(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(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=(client_identifier) ⇒ Object

Sets the specified value to V$SESSION.CLIENT_IDENTIFIER.

Oracle 9i client or upper

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

Oracle 8i client or lower

This executes the following PL/SQL block internally.

BEGIN
  DBMS_SESSION.SET_IDENTIFIER(:client_id);
END;

See Oracle Manual: Oracle Database PL/SQL Packages and Types Reference

Parameters:

Since:

  • 2.0.3



981
982
983
984
985
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
 
# File 'ext/oci8/oci8.c', line 981

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 (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(svcctx->usrhp, OCI_HTYPE_SESSION, (dvoid*)ptr,
                          size, OCI_ATTR_CLIENT_IDENTIFIER, oci8_errhp),
               &svcctx->base);
    } 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(svcctx,
                      "BEGIN\n"
                      "  DBMS_SESSION.SET_IDENTIFIER(:client_id);\n"
                      "END;\n", 0, NULL, 1, bind_vars, 1);
    }
    return val;
}

#client_info=(client_info) ⇒ Object

Sets the specified value to V$SESSION.CLIENT_INFO.

Oracle 10g client or upper

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

Oracle 9i client or lower

This executes the following PL/SQL block internally.

BEGIN
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:client_info);
END;

See Oracle Manual: Oracle Database PL/SQL Packages and Types Reference

Parameters:

Since:

  • 2.0.3



1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
 
# File 'ext/oci8/oci8.c', line 1181

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;
    }
    if (oracle_client_version >= ORAVER_10_1) {
        /* Oracle 10g or upper */
        chker2(OCIAttrSet(svcctx->usrhp, OCI_HTYPE_SESSION, (dvoid*)ptr,
                          size, OCI_ATTR_CLIENT_INFO, oci8_errhp),
               &svcctx->base);
    } 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(svcctx, 
                      "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.



734
735
736
737
738
739
 
# File 'ext/oci8/oci8.c', line 734

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



374
375
376
 
# File 'lib/oci8/oci8.rb', line 374

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

#describe_any(object_name) ⇒ a subclass of OCI8::Metadata::Base

Returns object information.

The return type is depends on the object type.

Oracle type

Ruby type

Table

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:



2054
2055
2056
2057
2058
2059
2060
2061
2062
 
# File 'lib/oci8/metadata.rb', line 2054

def describe_any(object_name)
  if /^PUBLIC\.(.*)/i =~ object_name
    md = __describe($1, OCI8::Metadata::Unknown, true)
    raise OCIError.new(4043, object_name) if md.obj_schema != 'PUBLIC'
    md
  else
    __describe(object_name, OCI8::Metadata::Unknown, true)
  end
end

#describe_database(database_name) ⇒ OCI8::Metadata::Database

Returns database information

Parameters:

Returns:



2167
2168
2169
 
# File 'lib/oci8/metadata.rb', line 2167

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

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

Returns function information

Parameters:

Returns:



2119
2120
2121
 
# File 'lib/oci8/metadata.rb', line 2119

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

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

Returns package information

Parameters:

Returns:



2126
2127
2128
 
# File 'lib/oci8/metadata.rb', line 2126

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

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

Returns procedure information

Parameters:

Returns:



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

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

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

Returns schema information

Parameters:

Returns:



2160
2161
2162
 
# File 'lib/oci8/metadata.rb', line 2160

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

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

Returns sequence information

Parameters:

Returns:



2153
2154
2155
 
# File 'lib/oci8/metadata.rb', line 2153

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

#describe_synonym(synonym_name, check_public_also = true) ⇒ OCI8::Metadata::Synonym

Returns synonym information

Parameters:

Returns:



2140
2141
2142
2143
2144
2145
2146
2147
2148
 
# File 'lib/oci8/metadata.rb', line 2140

def describe_synonym(synonym_name, check_public_also = true)
  if /^PUBLIC\.(.*)/i =~ synonym_name
    md = __describe($1, OCI8::Metadata::Synonym, true)
    raise OCIError.new(4043, synonym_name) if md.obj_schema != 'PUBLIC'
    md
  else
    __describe(synonym_name, OCI8::Metadata::Synonym, check_public_also)
  end
end

#describe_table(table_name, table_only = false) ⇒ OCI8::Metadata::Table or OCI8::Metadata::View

Returns table or view information. If the name is a current schema’s synonym name or a public synonym name, it returns table or view information which the synonym refers.

If table_only is true, it checks tables in the current schema.

Parameters:

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

    (default: false)

Returns:



2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
 
# File 'lib/oci8/metadata.rb', line 2072

def describe_table(table_name, table_only = false)
  if table_only
    # check my own tables only.
    __describe(table_name, OCI8::Metadata::Table, false)
  else
    # check tables, views, synonyms and public synonyms.

    # follow synonyms up to 20 times to prevent infinite loop
    # caused by recursive synonyms.
    recursive_level = 20
    recursive_level.times do
      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:



2133
2134
2135
 
# File 'lib/oci8/metadata.rb', line 2133

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

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

Returns view information

Parameters:

Returns:



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

def describe_view(view_name)
  __describe(view_name, OCI8::Metadata::View, false)
end

#exec(sql, *bindvars, &block) ⇒ Object

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

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

select statements without block

It returns the instance of OCI8::Cursor.

example:

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

select statements with a block

It acts as iterator and returns the processed row counts. Fetched data is passed to the block as array. NULL value becomes nil in ruby.

example:

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

PL/SQL block (ruby-oci8 1.0)

It returns the array of bind variables’ values.

example:

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

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

If a block is given, it is ignored.

PL/SQL block (ruby-oci8 2.0)

It returns the number of processed rows.

example:

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

If a block is given, the bind variables’ values are passed to the block after executed.

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

FYI, the following code do same on ruby-oci8 1.0 and ruby-oci8 2.0.

conn.exec(sql, *bindvars) { |*outvars| outvars }

Other SQL statements

It returns the number of processed rows.

example:

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

example:

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



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

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

#logoff ⇒ Object

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



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

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:



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

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:



867
868
869
870
871
872
873
 
# File 'ext/oci8/oci8.c', line 867

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.

Oracle 10g client or upper

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

Oracle 9i client or lower

This executes the following PL/SQL block internally.

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

Parameters:

Since:

  • 2.0.3



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
1085
1086
1087
1088
1089
1090
1091
1092
 
# File 'ext/oci8/oci8.c', line 1053

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;
    }
    if (oracle_client_version >= ORAVER_10_1) {
        /* Oracle 10g or upper */
        chker2(OCIAttrSet(svcctx->usrhp, OCI_HTYPE_SESSION, (dvoid*)ptr,
                          size, OCI_ATTR_MODULE, oci8_errhp),
               &svcctx->base);
    } 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(svcctx,
                      "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=(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) 


791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
 
# File 'ext/oci8/oci8.c', line 791

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

Returns:

  • (Boolean) 


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

static VALUE oci8_non_blocking_p(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
#ifdef NATIVE_THREAD_WITH_GVL
    return svcctx->non_blocking ? Qtrue : Qfalse;
#else
    sb1 non_blocking;

    chker2(OCIAttrGet(svcctx->srvhp, OCI_HTYPE_SERVER, &non_blocking, 0, OCI_ATTR_NONBLOCKING_MODE, oci8_errhp), &svcctx->base);
    return non_blocking ? Qtrue : Qfalse;
#endif
}

#oracle_server_version ⇒ OCI8::OracleVersion

Returns the Oracle server version.

Returns:

See Also:

  • oracle_client_version


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

def oracle_server_version
  unless defined? @oracle_server_version
    if vernum = oracle_server_vernum
      # If the Oracle client is Oracle 9i or upper,
      # get the server version from the OCI function OCIServerRelease.
      @oracle_server_version = OCI8::OracleVersion.new(vernum)
    else
      # Otherwise, get it from v$version.
      self.exec('select banner from v$version') do |row|
        if /^Oracle.*?(\d+\.\d+\.\d+\.\d+\.\d+)/ =~ row[0]
          @oracle_server_version = OCI8::OracleVersion.new($1)
          break
        end
      end
    end
  end
  @oracle_server_version
end

#parse(sql) ⇒ OCI8::Cursor

Returns a prepared SQL handle.

Parameters:

  • sql (String) —

    SQL statement

Returns:



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

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

#ping ⇒ Boolean

Makes a round trip call to the server to confirm that the connection and the server are active.

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



943
944
945
946
947
948
949
950
951
952
953
954
955
956
 
# File 'ext/oci8/oci8.c', line 943

static VALUE oci8_ping(VALUE self)
{
    oci8_svcctx_t *svcctx = oci8_get_svcctx(self);
    sword rv;

    if (have_OCIPing_nb) {
        /* Oracle 10.2 or upper */
        rv = OCIPing_nb(svcctx, svcctx->base.hp.svc, oci8_errhp, OCI_DEFAULT);
    } else {
        /* Oracle 10.1 or lower */
        rv = oci8_exec_sql(svcctx, "BEGIN NULL; END;", 0U, NULL, 0U, NULL, 0);
    }
    return rv == OCI_SUCCESS ? Qtrue : FALSE;
}

#prefetch_rows=(num) ⇒ Object

Sets the prefetch rows size. The default value is one. When a select statement is executed, the OCI library allocate prefetch buffer to reduce the number of network round trips by retrieving specified number of rows in one round trip.

Note: Active record adaptors set 100 by default.



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

def prefetch_rows=(num)
  @prefetch_rows = num
end

#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



424
425
426
427
 
# File 'lib/oci8/oci8.rb', line 424

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



440
441
442
443
 
# File 'lib/oci8/oci8.rb', line 440

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

#rollback ⇒ Object

Rollbacks the transaction.



746
747
748
749
750
751
 
# File 'ext/oci8/oci8.c', line 746

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.



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

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

#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



396
397
398
399
 
# File 'lib/oci8/oci8.rb', line 396

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



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

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

#username ⇒ Object 



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

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