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
163
164
165
166
167
168
169
170
171
172
 
# 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

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

    logon2(username, password, dbname, logon2_mode)

    if stmt_cache_size
      # set statement cache size
      attr_set_ub4(176, stmt_cache_size) # 176: OCI_ATTR_STMTCACHESIZE
    end
  else
    # logon by the OCI function OCISessionBegin().
    attach_mode = 0
    if dbname.is_a? OCI8::ConnectionPool
      @pool = dbname # to prevent GC from freeing the connection pool.
      dbname = dbname.send(:pool_name)
      attach_mode |= 0x0200 # OCI_CPOOL
    end
    if stmt_cache_size
      # enable statement caching
      attach_mode |= 0x0004 # OCI_STMT_CACHE
    end

    allocate_handles()
    @session_handle.send(:attr_set_string, OCI_ATTR_USERNAME, username) if username
    @session_handle.send(:attr_set_string, OCI_ATTR_PASSWORD, password) if password
    server_attach(dbname, attach_mode)
    session_begin(cred ? cred : OCI_CRED_RDBMS, mode ? mode : OCI_DEFAULT)

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

  @prefetch_rows = nil
  @username = nil
end

Instance Attribute Details

#last_error ⇒ 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.

Note: This method is unavailable if the Oracle client version is 8.0.

example:

# When NLS_LANG is AMERICAN_AMERICA.AL32UTF8
OCI8.error_message(1) # => "ORA-00001: unique constraint (%s.%s) violated"

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

Returns:

Parameters:

  • message_no (Fixnum) —

    Oracle error message number

Returns:

  • (String) —

    Oracle error message



245
246
247
248
 
# File 'ext/oci8/oci8.c', line 245

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

Returns:

  • (a customized Hash)

Since:

  • 2.0.5



131
132
133
 
# File 'lib/oci8/properties.rb', line 131

def self.properties
  @@properties
end

Instance Method Details

#action=(string) ⇒ Object

Sets the name of the current action within the current module. This information is stored in the V$SESSION view and is also stored in the V$SQL view and the V$SQLAREA view when a SQL statement is executed and the SQL statement is first parsed in the Oracle server.

Oracle 10g client or upper

This doesn’t perform network round trips. The change is reflected to the server by the next round trip such as OCI8#exec, OCI8#ping, etc.

Oracle 9i client or lower

This executes the following PL/SQL block internally. The change is reflected immediately by a network round trip.

BEGIN
  DBMS_APPLICATION_INFO.SET_ACTION(:action);
END;

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

Since:

  • 2.0.3



1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
 
# File 'ext/oci8/oci8.c', line 1018

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

    if (!NIL_P(val)) {
        OCI8SafeStringValue(val);
        ptr = RSTRING_PTR(val);
        size = RSTRING_LEN(val);
    } else {
        ptr = "";
        size = 0;
    }
    if (oracle_client_version >= ORAVER_10_1) {
        /* Oracle 10g or upper */
        chker2(OCIAttrSet(oci8_get_oci_session(self), OCI_HTYPE_SESSION, (dvoid*)ptr,
                          size, OCI_ATTR_ACTION, oci8_errhp),
               DATA_PTR(self));
    } else {
        /* Oracle 9i or lower */
        oci8_exec_sql_var_t bind_vars[1];

        /* :action */
        bind_vars[0].valuep = (dvoid*)ptr;
        bind_vars[0].value_sz = size;
        bind_vars[0].dty = SQLT_CHR;
        bind_vars[0].indp = NULL;
        bind_vars[0].alenp = NULL;

        oci8_exec_sql(oci8_get_svcctx(self),
                      "BEGIN\n"
                      "  DBMS_APPLICATION_INFO.SET_ACTION(:action);\n"
                      "END;\n", 0, NULL, 1, bind_vars, 1);
    }
    return val;
}

#autocommit=(true) ⇒ Object

Sets the autocommit mode. The default value is false.



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

static VALUE oci8_set_autocommit(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = DATA_PTR(self);
    svcctx->is_autocommit = RTEST(val);
    return val;
}

#autocommit? ⇒ Boolean

Returns true if the connection is in autocommit mode, false otherwise. The default value is false.

Returns:

  • (Boolean) 


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

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

#break ⇒ Object

Cancels the executing SQL.

See also #non_blocking=.



765
766
767
768
769
770
771
772
773
774
775
776
777
 
# File 'ext/oci8/oci8.c', line 765

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

    if (NIL_P(svcctx->executing_thread)) {
        return Qfalse;
    }
#ifndef NATIVE_THREAD_WITH_GVL
    chker2(OCIBreak(svcctx->base.hp.ptr, oci8_errhp), &svcctx->base);
#endif
    rb_thread_wakeup(svcctx->executing_thread);
    return Qtrue;
}

#charset_id2name(charset_id) ⇒ String

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

Oracle 9iR2 client or upper

It is done by using the mapping table stored in the client side.

Oracle 9iR1 client or lower

It executes the following PL/SQL block internally to use the mapping table stored in the server side.

BEGIN
  :name := nls_charset_name(:csid);
END;

Parameters:

  • charset_id (Fixnum) —

    Oracle character set id

Returns:

  • (String) —

    Oracle character set name or nil

Since:

  • 2.0.0



53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'ext/oci8/encoding.c', line 53

VALUE oci8_charset_id2name(VALUE svc, VALUE csid)
{
    VALUE name = rb_hash_aref(csid2name, csid);

    if (!NIL_P(name)) {
        return name;
    }
    Check_Type(csid, T_FIXNUM);
    if (have_OCINlsCharSetIdToName) {
        /* Oracle 9iR2 or upper */
        char buf[OCI_NLS_MAXBUFSZ];
        sword rv;

        rv = OCINlsCharSetIdToName(oci8_envhp, TO_ORATEXT(buf), sizeof(buf), (ub2)FIX2INT(csid));
        if (rv != OCI_SUCCESS) {
            return Qnil;
        }
        name = rb_usascii_str_new_cstr(buf);
    } else {
        /* Oracle 9iR1 or lower */
        oci8_exec_sql_var_t bind_vars[2];
        char buf[OCI_NLS_MAXBUFSZ];
        ub2 buflen = 0;
        int ival = FIX2INT(csid);

        /* :name */
        bind_vars[0].valuep = buf;
        bind_vars[0].value_sz = OCI_NLS_MAXBUFSZ;
        bind_vars[0].dty = SQLT_CHR;
        bind_vars[0].indp = NULL;
        bind_vars[0].alenp = &buflen;
        /* :csid */
        bind_vars[1].valuep = &ival;
        bind_vars[1].value_sz = sizeof(int);
        bind_vars[1].dty = SQLT_INT;
        bind_vars[1].indp = NULL;
        bind_vars[1].alenp = NULL;

        /* convert chaset id to charset name by querying Oracle server. */
        oci8_exec_sql(oci8_get_svcctx(svc), "BEGIN :name := nls_charset_name(:csid); END;", 0, NULL, 2, bind_vars, 1);
        if (buflen == 0) {
            return Qnil;
        }
        name = rb_usascii_str_new(buf, buflen);
    }
    OBJ_FREEZE(name);
    rb_hash_aset(csid2name, csid, name);
    rb_hash_aset(csname2id, name, csid);
    return name;
}

#charset_name2id(charset_name) ⇒ Fixnum

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

Oracle 9iR2 client or upper

It is done by using the mapping table stored in the client side.

Oracle 9iR1 client or lower

It executes the following PL/SQL block internally to use the mapping table stored in the server side.

BEGIN
  :csid := nls_charset_id(:name);
END;

Parameters:

  • charset_name (String) —

    Oracle character set name

Returns:

  • (Fixnum) —

    Oracle character set id or nil

Since:

  • 2.0.0



128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
 
# File 'ext/oci8/encoding.c', line 128

static VALUE oci8_charset_name2id(VALUE svc, VALUE name)
{
    VALUE csid;

    name = rb_funcall(name, id_upcase, 0);
    csid = rb_hash_aref(csname2id, StringValue(name));
    if (!NIL_P(csid)) {
        return csid;
    }
    if (have_OCINlsCharSetNameToId) {
        /* Oracle 9iR2 or upper */
        ub2 rv;

        rv = OCINlsCharSetNameToId(oci8_envhp, RSTRING_ORATEXT(name));
        if (rv == 0) {
            return Qnil;
        }
        csid = INT2FIX(rv);
    } else {
        /* Oracle 9iR1 or lower */
        oci8_exec_sql_var_t bind_vars[2];
        int ival;
        sb2 ind = 0; /* null indicator */

        /* :csid */
        bind_vars[0].valuep = &ival;
        bind_vars[0].value_sz = sizeof(int);
        bind_vars[0].dty = SQLT_INT;
        bind_vars[0].indp = &ind;
        bind_vars[0].alenp = NULL;
        /* :name */
        bind_vars[1].valuep = RSTRING_PTR(name);
        bind_vars[1].value_sz = RSTRING_LEN(name);
        bind_vars[1].dty = SQLT_CHR;
        bind_vars[1].indp = NULL;
        bind_vars[1].alenp = NULL;

        /* convert chaset name to charset id by querying Oracle server. */
        oci8_exec_sql(oci8_get_svcctx(svc), "BEGIN :csid := nls_charset_id(:name); END;", 0, NULL, 2, bind_vars, 1);
        if (ind) {
            return Qnil;
        }
        csid = INT2FIX(ival);
    }
    rb_hash_aset(csid2name, csid, name);
    rb_hash_aset(csname2id, name, csid);
    return csid;
}

#client_identifier=(string) ⇒ Object

Sets the client ID. This information is stored in the V$SESSION view.

Oracle 9i client or upper

This doesn’t perform network round trips. The change is reflected to the server by the next round trip such as OCI8#exec, OCI8#ping, etc.

Oracle 8i client or lower

This executes the following PL/SQL block internally. The change is reflected immediately by a network round trip.

BEGIN
  DBMS_SESSION.SET_IDENTIFIER(:client_id);
END;

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

Since:

  • 2.0.3



876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
 
# File 'ext/oci8/oci8.c', line 876

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

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

    if (oracle_client_version >= ORAVERNUM(9, 2, 0, 3, 0) || size > 0) {
        if (size > 0 && ptr[0] == ':') {
            rb_raise(rb_eArgError, "client identifier should not start with ':'.");
        }
        chker2(OCIAttrSet(oci8_get_oci_session(self), OCI_HTYPE_SESSION, (dvoid*)ptr,
                          size, OCI_ATTR_CLIENT_IDENTIFIER, oci8_errhp),
               DATA_PTR(self));
    } else {
        /* Workaround for Bug 2449486 */
        oci8_exec_sql_var_t bind_vars[1];

        /* :client_id */
        bind_vars[0].valuep = (dvoid*)ptr;
        bind_vars[0].value_sz = size;
        bind_vars[0].dty = SQLT_CHR;
        bind_vars[0].indp = NULL;
        bind_vars[0].alenp = NULL;

        oci8_exec_sql(oci8_get_svcctx(self),
                      "BEGIN\n"
                      "  DBMS_SESSION.SET_IDENTIFIER(:client_id);\n"
                      "END;\n", 0, NULL, 1, bind_vars, 1);
    }
    return val;
}

#client_info=(string) ⇒ Object

Sets additional information about the client application. This information is stored in the V$SESSION view.

Oracle 10g client or upper

This doesn’t perform network round trips. The change is reflected to the server by the next round trip such as OCI8#exec, OCI8#ping, etc.

Oracle 9i client or lower

This executes the following PL/SQL block internally. The change is reflected immediately by a network round trip.

BEGIN
  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:client_info);
END;

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

Since:

  • 2.0.3



1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
 
# File 'ext/oci8/oci8.c', line 1081

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

    if (!NIL_P(val)) {
        OCI8SafeStringValue(val);
        ptr = RSTRING_PTR(val);
        size = RSTRING_LEN(val);
    } else {
        ptr = "";
        size = 0;
    }
    if (oracle_client_version >= ORAVER_10_1) {
        /* Oracle 10g or upper */
        chker2(OCIAttrSet(oci8_get_oci_session(self), OCI_HTYPE_SESSION, (dvoid*)ptr,
                          size, OCI_ATTR_CLIENT_INFO, oci8_errhp),
               DATA_PTR(self));
    } else {
        /* Oracle 9i or lower */
        oci8_exec_sql_var_t bind_vars[1];

        /* :client_info */
        bind_vars[0].valuep = (dvoid*)ptr;
        bind_vars[0].value_sz = size;
        bind_vars[0].dty = SQLT_CHR;
        bind_vars[0].indp = NULL;
        bind_vars[0].alenp = NULL;

        oci8_exec_sql(oci8_get_svcctx(self), 
                      "BEGIN\n"
                      "  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(:client_info);\n"
                      "END;\n", 0, NULL, 1, bind_vars, 1);
    }
    return val;
}

#commit ⇒ Object

Commits the transaction.



596
597
598
599
600
601
 
# File 'ext/oci8/oci8.c', line 596

static VALUE oci8_commit(VALUE self)
{
    oci8_svcctx_t *svcctx = DATA_PTR(self);
    chker2(OCITransCommit_nb(svcctx, svcctx->base.hp.svc, oci8_errhp, OCI_DEFAULT), &svcctx->base);
    return self;
}

#database_charset_name ⇒ String

Returns the Oracle database character set name such as AL32UTF8.

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:



2004
2005
2006
 
# File 'lib/oci8/metadata.rb', line 2004

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

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

Returns database information

Parameters:

Returns:



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

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:



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

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:



2062
2063
2064
 
# File 'lib/oci8/metadata.rb', line 2062

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:



2048
2049
2050
 
# File 'lib/oci8/metadata.rb', line 2048

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:



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

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:



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

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:



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

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

#describe_table(table_name, table_only = false) ⇒ 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:



2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
 
# File 'lib/oci8/metadata.rb', line 2016

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



2069
2070
2071
 
# File 'lib/oci8/metadata.rb', line 2069

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:



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

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



277
278
279
280
 
# File 'lib/oci8/oci8.rb', line 277

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.



573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
 
# File 'ext/oci8/oci8.c', line 573

static VALUE oci8_svcctx_logoff(VALUE self)
{
    oci8_svcctx_t *svcctx = (oci8_svcctx_t *)DATA_PTR(self);

    while (svcctx->base.children != NULL) {
        oci8_base_free(svcctx->base.children);
    }
    if (svcctx->logoff_strategy != NULL) {
        const oci8_logoff_strategy_t *strategy = svcctx->logoff_strategy;
        void *data = strategy->prepare(svcctx);
        svcctx->base.type = 0;
        svcctx->logoff_strategy = NULL;
        chker2(oci8_call_without_gvl(svcctx, strategy->execute, data), &svcctx->base);
    }
    return Qtrue;
}

#long_read_len ⇒ Fixnum

Gets the maximum length in bytes to fetch a LONG or LONG RAW column. The default value is 65535.

If the actual data length is longer than long_read_len, “ORA-01406: fetched column value was truncated” is raised.

Note: long_read_len is also used for XMLTYPE data type in 2.0.

Returns:

  • (Fixnum) 


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

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

#long_read_len=(fixnum) ⇒ Object

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

See also #long_read_len



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

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

#module=(string) ⇒ Object

Sets the name of the current module. This information is stored in the V$SESSION view and is also stored in the V$SQL view and the V$SQLAREA view when a SQL statement is executed and the SQL statement is first parsed in the Oracle server.

Oracle 10g client or upper

This doesn’t perform network round trips. The change is reflected to the server by the next round trip such as OCI8#exec, OCI8#ping, etc.

Oracle 9i client or lower

This executes the following PL/SQL block internally. The change is reflected immediately by a network round trip.

DECLARE
  action VARCHAR2(32);
BEGIN
  -- retrieve action name.
  SELECT SYS_CONTEXT('USERENV','ACTION') INTO action FROM DUAL;
  -- change module name without modifying the action name.
  DBMS_APPLICATION_INFO.SET_MODULE(:module, action);
END;

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

Since:

  • 2.0.3



949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
 
# File 'ext/oci8/oci8.c', line 949

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

    if (!NIL_P(val)) {
        OCI8SafeStringValue(val);
        ptr = RSTRING_PTR(val);
        size = RSTRING_LEN(val);
    } else {
        ptr = "";
        size = 0;
    }
    if (oracle_client_version >= ORAVER_10_1) {
        /* Oracle 10g or upper */
        chker2(OCIAttrSet(oci8_get_oci_session(self), OCI_HTYPE_SESSION, (dvoid*)ptr,
                          size, OCI_ATTR_MODULE, oci8_errhp),
               DATA_PTR(self));
    } else {
        /* Oracle 9i or lower */
        oci8_exec_sql_var_t bind_vars[1];

        /* :module */
        bind_vars[0].valuep = (dvoid*)ptr;
        bind_vars[0].value_sz = size;
        bind_vars[0].dty = SQLT_CHR;
        bind_vars[0].indp = NULL;
        bind_vars[0].alenp = NULL;

        oci8_exec_sql(oci8_get_svcctx(self),
                      "DECLARE\n"
                      "  action VARCHAR2(32);\n"
                      "BEGIN\n"
                      "  SELECT SYS_CONTEXT('USERENV','ACTION') INTO action FROM DUAL;\n"
                      "  DBMS_APPLICATION_INFO.SET_MODULE(:module, action);\n"
                      "END;\n", 0, NULL, 1, bind_vars, 1);
    }
    return self;
}

#non_blocking=(true) ⇒ Object

Sets true to enable non-blocking mode, false otherwise. The default setting depends on the ruby version and ruby-oci8 version.

When the connection is in blocking mode (non_blocking = false), SQL executions block not only the thread, but also the ruby process. It makes the whole application stop while a SQL execution needs long time.

When in non-blocking mode (non_blocking = true), SQL executions block only the thread. It does’t prevent other threads. A SQL execution which blocks a thread can be canceled by OCI8#break.

ruby 1.9

The default setting is true if the ruby-oci8 version is 2.0.3 or upper, false otherwise.

Ruby-oci8 makes the connection non-blocking by releasing ruby interpreter’s GVL (Global VM Lock or Giant VM Lock) while OCI functions which may need more than one network round trips are in execution.

ruby 1.8

The default setting is false.

Ruby-oci8 makes the connection non-blocking by polling the return values of OCI functions. When an OCI function returns OCI_STILL_EXECUTING, the thread sleeps for 10 milli seconds to make a time for other threads to run. The sleep time is doubled up to 640 milli seconds as the function returns the same value.



675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
 
# File 'ext/oci8/oci8.c', line 675

static VALUE oci8_set_non_blocking(VALUE self, VALUE val)
{
    oci8_svcctx_t *svcctx = DATA_PTR(self);
#ifdef NATIVE_THREAD_WITH_GVL
    svcctx->non_blocking = RTEST(val);
#else
    sb1 non_blocking;

    if (svcctx->state & OCI8_STATE_CPOOL) {
        rb_raise(rb_eRuntimeError, "Could not set non-blocking mode to a connection allocated from OCI8::ConnectionPool.");
    }
    chker2(OCIAttrGet(svcctx->srvhp, OCI_HTYPE_SERVER, &non_blocking, 0, OCI_ATTR_NONBLOCKING_MODE, oci8_errhp), &svcctx->base);
    if ((RTEST(val) && !non_blocking) || (!RTEST(val) && non_blocking)) {
        /* toggle blocking / non-blocking. */
        chker2(OCIAttrSet(svcctx->srvhp, OCI_HTYPE_SERVER, 0, 0, OCI_ATTR_NONBLOCKING_MODE, oci8_errhp), &svcctx->base);
    }
#endif
    return val;
}

#non_blocking? ⇒ Boolean

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

See also #non_blocking=.

Returns:

  • (Boolean) 


625
626
627
628
629
630
631
632
633
634
635
636
 
# File 'ext/oci8/oci8.c', line 625

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

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

#oracle_server_version ⇒ OCI8::OracleVersion

Returns the Oracle server version.

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:



178
179
180
181
 
# File 'lib/oci8/oci8.rb', line 178

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

#ping ⇒ Boolean

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

OCI8#ping also can be used to flush all the pending OCI client-side calls to the server if any exist.

Oracle 10.2 client or upper

A dummy round trip call is made by a newly added OCI function OCIPing in Oracle 10.2.

Oracle 10.1 client or lower

A simple PL/SQL block “BEGIN NULL; END;” is executed to make a round trip call.

Returns:

  • (Boolean)

Since:

  • 2.0.2



835
836
837
838
839
840
841
842
843
844
845
846
847
848
 
# File 'ext/oci8/oci8.c', line 835

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

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

#prefetch_rows=(number) ⇒ Object

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

Note: Active record adaptors set 100 by default.



790
791
792
793
794
 
# File 'ext/oci8/oci8.c', line 790

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

#rollback ⇒ Object

Rollbacks the transaction.



609
610
611
612
613
614
 
# File 'ext/oci8/oci8.c', line 609

static VALUE oci8_rollback(VALUE self)
{
    oci8_svcctx_t *svcctx = DATA_PTR(self);
    chker2(OCITransRollback_nb(svcctx, svcctx->base.hp.svc, oci8_errhp, OCI_DEFAULT), &svcctx->base);
    return self;
}

#select_one(sql, *bindvars) ⇒ Array

Executes a SQL statement and fetches the first one row.

Parameters:

  • sql (String) —

    SQL statement

  • bindvars (Object) —

    bind variables

Returns:

  • (Array) —

    an array of first row.



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

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

#username ⇒ Object 



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

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