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.

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



240
241
242
243
 
# File 'ext/oci8/oci8.c', line 240

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



996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
 
# File 'ext/oci8/oci8.c', line 996

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.



709
710
711
712
713
714
 
# File 'ext/oci8/oci8.c', line 709

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) 


697
698
699
700
701
 
# File 'ext/oci8/oci8.c', line 697

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



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

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



854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
 
# File 'ext/oci8/oci8.c', line 854

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



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
1093
1094
 
# File 'ext/oci8/oci8.c', line 1059

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.



591
592
593
594
595
596
 
# File 'ext/oci8/oci8.c', line 591

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:



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.



568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
 
# File 'ext/oci8/oci8.c', line 568

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) 


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

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



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

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



927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
 
# File 'ext/oci8/oci8.c', line 927

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.



670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
 
# File 'ext/oci8/oci8.c', line 670

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) 


620
621
622
623
624
625
626
627
628
629
630
631
 
# File 'ext/oci8/oci8.c', line 620

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:



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.

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



813
814
815
816
817
818
819
820
821
822
823
824
825
826
 
# File 'ext/oci8/oci8.c', line 813

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

#rollback ⇒ Object

Rollbacks the transaction.



604
605
606
607
608
609
 
# File 'ext/oci8/oci8.c', line 604

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.



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

#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