Class: OCI8

Inherits:
Object
  • Object
show all
Defined in:
lib/oci8/oci8.rb,
lib/oci8.rb,
lib/oci8/cursor.rb,
lib/oci8/object.rb,
lib/oci8/object.rb,
lib/oci8/version.rb,
lib/oci8/bindtype.rb,
lib/oci8/datetime.rb,
lib/oci8/metadata.rb,
lib/oci8/properties.rb,
lib/oci8/encoding-init.rb,
lib/oci8/oracle_version.rb,
lib/oci8/connection_pool.rb,
lib/oci8/check_load_error.rb

Overview

This file is loaded only on LoadError.

Defined Under Namespace

Modules: BindType, Metadata, Object, Util Classes: ConnectionPool, Cursor, OracleVersion

Constant Summary collapse

VERSION = 
"2.2.0"
@@client_charset_name = 
charset_id2name(@@environment_handle.send(:attr_get_ub2, 31))

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(username, password, dbname = nil, privilege = nil) ⇒ OCI8

Connects to an Oracle database server by username and password at dbname as privilege.

connecting to the local server

Set username and password or pass “username/password” as a single argument.

OCI8.new('scott', 'tiger')

or

OCI8.new('scott/tiger')

connecting to a remote server

Set username, password and dbname or pass “username/password@dbname” as a single argument.

OCI8.new('scott', 'tiger', 'orcl.world')

or

OCI8.new('scott/[email protected]')

The dbname is a net service name or an easy connectection identifier. The former is a name listed in the file tnsnames.ora. Ask to your DBA if you don’t know what it is. The latter has the syntax as “//host:port/service_name”.

OCI8.new('scott', 'tiger', '//remote-host:1521/XE')

or

OCI8.new('scott/tiger@//remote-host:1521/XE')

connecting as a privileged user

Set :SYSDBA 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
 
# 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
      # Sets the driver name displayed in V$SESSION_CONNECT_INFO.CLIENT_DRIVER
      # if both the client and the server are Oracle 11g or upper.
      # Only the first 8 chracters "ruby-oci" are displayed when the Oracle
      # server version is lower than 12.0.1.2.
      # 424: OCI_ATTR_DRIVER_NAME
      @session_handle.send(:attr_set_string, 424, "ruby-oci8 : #{OCI8::VERSION}")
    end
    server_attach(dbname, attach_mode)
    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 = 100
  @username = nil
end

Instance Attribute Details

#last_error ⇒ OCIError

Returns:



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

def last_error
  @last_error
end

Class Method Details

.oracle_client_version ⇒ OCI8::OracleVersion

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

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

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

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

Returns:

See Also:



126
127
128
 
# File 'lib/oci8.rb', line 126

def self.oracle_client_version
  @@oracle_client_version
end

.properties ⇒ a customized Hash

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

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

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

Supported properties are listed below:

:length_semantics

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

Since: 2.1.0

:bind_string_as_nchar

true when string bind variables are bound as NCHAR, otherwise false. The default value is false.

:float_conversion_type

:ruby when Oracle decimal numbers are converted to ruby Float values same as Float#to_s does. (default) :oracle: when they are done by Oracle OCI functions.

From ruby 1.9.2, a float value converted from Oracle number 15.7 by the Oracle function OCINumberToReal() makes a string representation 15.700000000000001 by Float#to_s. See: web.archive.org/web/20140521195004/https://rubyforge.org/forum/forum.php?thread_id=50030&forum_id=1078

Since: 2.1.0

:statement_cache_size

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

Since: 2.1.1

:events_mode

true when Fast Application Notification (FAN) Support is enabled. false when it is disabled. The default value is false. This corresponds to oci8.events in PHP.

This parameter can be changed only when no OCI methods are called.

require 'oci8'
OCI8.properties[:events_mode] = true # works fine.
# ... call some OCI methods ...
OCI8.properties[:events_mode] = true # raises a runtime error.

Since: 2.1.4

:cancel_read_at_exit

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

Since: 2.1.8

Returns:

  • (a customized Hash)

Since:

  • 2.0.5



138
139
140
 
# File 'lib/oci8/properties.rb', line 138

def self.properties
  @@properties
end

Instance Method Details

#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



376
377
378
 
# File 'lib/oci8/oci8.rb', line 376

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

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

Returns object information.

The return type is depends on the object type.

Oracle type

Ruby type

Table

OCI8::Metadata::Table

View

OCI8::Metadata::View

Procedure

OCI8::Metadata::Procedure

Function

OCI8::Metadata::Function

Package

OCI8::Metadata::Package

Type

OCI8::Metadata::Type

Synonym

OCI8::Metadata::Synonym

Sequence

OCI8::Metadata::Sequence

Parameters:

Returns:



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

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

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

Returns database information

Parameters:

Returns:



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

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

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

Returns function information

Parameters:

Returns:



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

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

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

Returns package information

Parameters:

Returns:



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

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

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

Returns procedure information

Parameters:

Returns:



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

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

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

Returns schema information

Parameters:

Returns:



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

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

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

Returns sequence information

Parameters:

Returns:



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

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

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

Returns synonym information

Parameters:

Returns:



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

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

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

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

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

Parameters:

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

    (default: false)

Returns:



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

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

    # follow synonyms up to 20 times to prevent infinite loop
    # caused by recursive synonyms.
    recursive_level = 20
    recursive_level.times do
      metadata = __describe(table_name, OCI8::Metadata::Unknown, true)
      case metadata
      when OCI8::Metadata::Table, OCI8::Metadata::View
        return metadata
      when OCI8::Metadata::Synonym
        table_name = metadata.translated_name
        if metadata.obj_link and metadata.link.nil?
          # table_name is a synonym in a remote database for an object in the
          # remote database itself.
          table_name = "#{table_name}@#{metadata.obj_link}"
        end
      else
        raise OCIError.new(4043, table_name) # ORA-04043: object %s does not exist
      end
    end
    raise OCIError.new(36, recursive_level) # ORA-00036: maximum number of recursive SQL levels (%s) exceeded
  end
end

#describe_type(type_name) ⇒ OCI8::Metadata::Type

Returns type information

Parameters:

Returns:



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

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

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

Returns view information

Parameters:

Returns:



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

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

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

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

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

select statements without block

It returns the instance of OCI8::Cursor.

example:

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

select statements with a block

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

example:

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

PL/SQL block (ruby-oci8 1.0)

It returns the array of bind variables’ values.

example:

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

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

If a block is given, it is ignored.

PL/SQL block (ruby-oci8 2.0)

It returns the number of processed rows.

example:

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

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

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

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

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

Other SQL statements

It returns the number of processed rows.

example:

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

example:

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



269
270
271
272
 
# File 'lib/oci8/oci8.rb', line 269

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

#oracle_server_version ⇒ OCI8::OracleVersion

Returns the Oracle server version.

Returns:

See Also:



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

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:



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

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

#prefetch_rows=(num) ⇒ Object

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

Note: The default value had been 1 before ruby-oci8 2.2.0.



340
341
342
 
# File 'lib/oci8/oci8.rb', line 340

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



426
427
428
429
 
# File 'lib/oci8/oci8.rb', line 426

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



442
443
444
445
 
# File 'lib/oci8/oci8.rb', line 442

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

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



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

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



398
399
400
401
 
# File 'lib/oci8/oci8.rb', line 398

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



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

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

#username ⇒ Object 



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

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