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

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, Metadata, Object Classes: ConnectionPool, Cursor, OracleVersion

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

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



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

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

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



1996
1997
1998
 
# File 'lib/oci8/metadata.rb', line 1996

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:



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

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:



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

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:



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

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:



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

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:



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

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:



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

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:



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

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:



2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
 
# File 'lib/oci8/metadata.rb', line 2008

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:



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

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:



2033
2034
2035
 
# File 'lib/oci8/metadata.rb', line 2033

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

#oracle_server_version ⇒ OCI8::OracleVersion

Returns the Oracle server version.

Returns:

See Also:



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

#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

#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