Class ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter

  1. lib/active_record/connection_adapters/oracle_enhanced_adapter.rb
Parent: AbstractAdapter

Oracle enhanced adapter will work with both Ruby 1.8/1.9 ruby-oci8 gem (which provides interface to Oracle OCI client) or with JRuby and Oracle JDBC driver.

It should work with Oracle 9i, 10g and 11g databases. Limited set of functionality should work on Oracle 8i as well but several features rely on newer functionality in Oracle database.

Usage notes:

  • Key generation assumes a “${table_name}_seq” sequence is available for all tables; the sequence name can be changed using ActiveRecord::Base.set_sequence_name. When using Migrations, these sequences are created automatically. Use set_sequence_name :autogenerated with legacy tables that have triggers that populate primary keys automatically.
  • Oracle uses DATE or TIMESTAMP datatypes for both dates and times. Consequently some hacks are employed to map data back to Date or Time in Ruby. Timezones and sub-second precision on timestamps are not supported.
  • Default values that are functions (such as “SYSDATE”) are not supported. This is a restriction of the way ActiveRecord supports default values.

Required parameters:

  • :username
  • :password
  • :database - either TNS alias or connection string for OCI client or database name in JDBC connection string

Optional parameters:

  • :host - host name for JDBC connection, defaults to “localhost“
  • :port - port number for JDBC connection, defaults to 1521
  • :privilege - set “SYSDBA” if you want to connect with this privilege
  • :allow_concurrency - set to “true” if non-blocking mode should be enabled (just for OCI client)
  • :prefetch_rows - how many rows should be fetched at one time to increase performance, defaults to 100
  • :cursor_sharing - cursor sharing mode to minimize amount of unique statements, defaults to “force“
  • :nls_length_semantics - semantics of size of VARCHAR2 and CHAR columns, defaults to “CHAR” (meaning that size specifies number of characters and not bytes)

Constants

IDENTIFIER_MAX_LENGTH = 30   maximum length of Oracle identifiers
AUTOGENERATED_SEQUENCE_NAME = 'autogenerated'.freeze #:nodoc:
DBMS_OUTPUT_BUFFER_SIZE = 10000   DBMS_OUTPUT =============================================

PL/SQL in Oracle uses dbms_output for logging print statements These methods stick that output into the Rails log so Ruby and PL/SQL code can can be debugged together in a single application

Public class methods

boolean_to_string (bool)

How boolean value should be quoted to String. Used if emulate_booleans_from_strings option is set to true.

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 362
      def self.boolean_to_string(bool)
        bool ? "Y" : "N"
      end
default_sequence_start_value ()

Specify default sequence start with value (by default 10000 if not explicitly set), e.g.:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.default_sequence_start_value = 1
[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 855
      cattr_accessor :default_sequence_start_value
emulate_booleans ()

By default, the OracleEnhancedAdapter will consider all columns of type NUMBER(1) as boolean. If you wish to disable this emulation you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans = false
[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 275
      cattr_accessor :emulate_booleans
emulate_booleans_from_strings ()

If you wish that CHAR(1), VARCHAR2(1) columns or VARCHAR2 columns with FLAG or YN at the end of their name are typecasted to booleans then you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_booleans_from_strings = true
[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 349
      cattr_accessor :emulate_booleans_from_strings
emulate_dates ()

By default, the OracleEnhancedAdapter will typecast all columns of type DATE to Time or DateTime (if value is out of Time value range) value. If you wish that DATE values with hour, minutes and seconds equal to 0 are typecasted to Date then you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates = true

As this option can have side effects when unnecessary typecasting is done it is recommended that Date columns are explicily defined with set_date_columns method.

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 289
      cattr_accessor :emulate_dates
emulate_dates_by_column_name ()

By default, the OracleEnhancedAdapter will typecast all columns of type DATE to Time or DateTime (if value is out of Time value range) value. If you wish that DATE columns with “date” in their name (e.g. “creation_date“) are typecasted to Date then you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_dates_by_column_name = true

As this option can have side effects when unnecessary typecasting is done it is recommended that Date columns are explicily defined with set_date_columns method.

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 303
      cattr_accessor :emulate_dates_by_column_name
emulate_integers_by_column_name ()

By default, the OracleEnhancedAdapter will typecast all columns of type NUMBER (without precision or scale) to Float or BigDecimal value. If you wish that NUMBER columns with name “id” or that end with “_id” are typecasted to Integer then you can add the following line to your initializer file:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.emulate_integers_by_column_name = true
[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 333
      cattr_accessor :emulate_integers_by_column_name
is_boolean_column? (name, field_type, table_name = nil)

Check column name to identify if it is boolean (and not String) column. Is used if emulate_booleans_from_strings option is set to true. Override this method definition in initializer file if different boolean column recognition is needed.

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 355
      def self.is_boolean_column?(name, field_type, table_name = nil)
        return true if ["CHAR(1)","VARCHAR2(1)"].include?(field_type)
        field_type =~ /^VARCHAR2/ && (name =~ /_flag$/i || name =~ /_yn$/i)
      end
is_date_column? (name, table_name = nil)

Check column name to identify if it is Date (and not Time) column. Is used if emulate_dates_by_column_name option is set to true. Override this method definition in initializer file if different Date column recognition is needed.

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 309
      def self.is_date_column?(name, table_name = nil)
        name =~ /(^|_)date(_|$)/i
      end
is_integer_column? (name, table_name = nil)

Check column name to identify if it is Integer (and not Float or BigDecimal) column. Is used if emulate_integers_by_column_name option is set to true. Override this method definition in initializer file if different Integer column recognition is needed.

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 339
      def self.is_integer_column?(name, table_name = nil)
        name =~ /(^|_)id$/i
      end

Public instance methods

create_table (name, options = {}, &block)

Additional options for create_table method in migration files.

You can specify individual starting value in table creation migration file, e.g.:

create_table :users, :sequence_start_value => 100 do |t|
  # ...
end

You can also specify other sequence definition additional parameters, e.g.:

create_table :users, :sequence_start_value => “100 NOCACHE INCREMENT BY 10” do |t|
  # ...
end

Create primary key trigger (so that you can skip primary key value in INSERT statement). By default trigger name will be “table_name_pkt“, you can override the name with :trigger_name option (but it is not recommended to override it as then this trigger will not be detected by ActiveRecord model and it will still do prefetching of sequence value). Example:

create_table :users, :primary_key_trigger => true do |t|
  # ...
end

It is possible to add table and column comments in table creation migration files:

create_table :employees, :comment => “Employees and contractors” do |t|
  t.string      :first_name, :comment => “Given name”
  t.string      :last_name, :comment => “Surname”
end
[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 888
      def create_table(name, options = {}, &block)
        create_sequence = options[:id] != false
        column_comments = {}
        super(name, options) do |t|
          # store that primary key was defined in create_table block
          unless create_sequence
            class <<t
              attr_accessor :create_sequence
              def primary_key(*args)
                self.create_sequence = true
                super(*args)
              end
            end
          end

          # store column comments
          class <<t
            attr_accessor :column_comments
            def column(name, type, options = {})
              if options[:comment]
                self.column_comments ||= {}
                self.column_comments[name] = options[:comment]
              end
              super(name, type, options)
            end
          end

          result = block.call(t) if block
          create_sequence = create_sequence || t.create_sequence
          column_comments = t.column_comments if t.column_comments
        end

        create_sequence_and_trigger(name, options) if create_sequence
        
        add_table_comment name, options[:comment]
        column_comments.each do |column_name, comment|
          add_comment name, column_name, comment
        end
        
      end
dbms_output_enabled? ()

Is DBMS_Output logging enabled?

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 1305
      def dbms_output_enabled?
        @enable_dbms_output
      end
default_sequence_name (table_name, primary_key = nil)

Returns default sequence name for table. Will take all or first 26 characters of table name and append _seq suffix

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 628
      def default_sequence_name(table_name, primary_key = nil)
        # TODO: remove schema prefix if present before truncating
        # truncate table name if necessary to fit in max length of identifier
        "#{table_name.to_s[0,IDENTIFIER_MAX_LENGTH-4]}_seq"
      end
disable_dbms_output ()

Turn DBMS_Output logging off

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 1299
      def disable_dbms_output
        set_dbms_output_plsql_connection
        @enable_dbms_output = false
        plsql(:dbms_output).sys.dbms_output.disable
      end
enable_dbms_output ()

Turn DBMS_Output logging on

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 1293
      def enable_dbms_output
        set_dbms_output_plsql_connection
        @enable_dbms_output = true
        plsql(:dbms_output).sys.dbms_output.enable(DBMS_OUTPUT_BUFFER_SIZE)
      end
execute (sql, name = nil)

Executes a SQL statement

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 544
      def execute(sql, name = nil)
        # hack to pass additional "with_returning" option without changing argument list
        log(sql, name) { sql.instance_variable_get(:@with_returning) ? @connection.exec_with_returning(sql) : @connection.exec(sql) }
      end
has_primary_key_trigger? (table_name, owner = nil, desc_table_name = nil, db_link = nil)

check if table has primary key trigger with _pkt suffix

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 781
      def has_primary_key_trigger?(table_name, owner = nil, desc_table_name = nil, db_link = nil)
        (owner, desc_table_name, db_link) = @connection.describe(table_name) unless owner

        trigger_name = default_trigger_name(table_name).upcase
        pkt_sql = "SELECT trigger_name\nFROM all_triggers\#{db_link}\nWHERE owner = '\#{owner}'\nAND trigger_name = '\#{trigger_name}'\nAND table_owner = '\#{owner}'\nAND table_name = '\#{desc_table_name}'\nAND status = 'ENABLED'\n"
        select_value(pkt_sql) ? true : false
      end
next_sequence_value (sequence_name)

Returns the next sequence value from a sequence generator. Not generally called directly; used by ActiveRecord to get the next primary key value when inserting a new database record (see prefetch_primary_key?).

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 579
      def next_sequence_value(sequence_name)
        # if sequence_name is set to :autogenerated then it means that primary key will be populated by trigger
        return nil if sequence_name == AUTOGENERATED_SEQUENCE_NAME
        select_one("SELECT #{quote_table_name(sequence_name)}.NEXTVAL id FROM dual")['id']
      end
prefetch_primary_key? (table_name = nil)

Returns true for Oracle adapter (since Oracle requires primary key values to be pre-fetched before insert). See also next_sequence_value.

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 617
      def prefetch_primary_key?(table_name = nil)
        ! @@do_not_prefetch_primary_key[table_name.to_s]
      end
raw_connection ()

return raw OCI8 or JDBC connection

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 512
      def raw_connection
        @connection.raw_connection
      end
select_rows (sql, name = nil)

Returns an array of arrays containing the field values. Order is the same as that returned by columns.

[show source]
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 551
      def select_rows(sql, name = nil)
        # last parameter indicates to return also column list
        result, columns = select(sql, name, true)
        result.map{ |v| columns.map{|c| v[c]} }
      end