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)
Methods
public class
- boolean_to_string
- default_sequence_start_value
- emulate_booleans
- emulate_booleans_from_strings
- emulate_dates
- emulate_dates_by_column_name
- emulate_integers_by_column_name
- is_boolean_column?
- is_date_column?
- is_integer_column?
public instance
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
How boolean value should be quoted to String. Used if emulate_booleans_from_strings option is set to true.
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 362 def self.boolean_to_string(bool) bool ? "Y" : "N" end
Specify default sequence start with value (by default 10000 if not explicitly set), e.g.:
ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.default_sequence_start_value = 1
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 855 cattr_accessor :default_sequence_start_value
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
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 275 cattr_accessor :emulate_booleans
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
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 349 cattr_accessor :emulate_booleans_from_strings
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.
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 289 cattr_accessor :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 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.
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 303 cattr_accessor :emulate_dates_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
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 333 cattr_accessor :emulate_integers_by_column_name
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.
# 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
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.
# 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
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.
# 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
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
# 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
Is DBMS_Output logging enabled?
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 1305 def dbms_output_enabled? @enable_dbms_output end
Returns default sequence name for table. Will take all or first 26 characters of table name and append _seq suffix
# 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
Turn DBMS_Output logging off
# 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
Turn DBMS_Output logging on
# 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
Executes a SQL statement
# 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
check if table has primary key trigger with _pkt suffix
# 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
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?).
# 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
Returns true for Oracle adapter (since Oracle requires primary key values to be pre-fetched before insert). See also next_sequence_value.
# 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
return raw OCI8 or JDBC connection
# File lib/active_record/connection_adapters/oracle_enhanced_adapter.rb, line 512 def raw_connection @connection.raw_connection end
Returns an array of arrays containing the field values. Order is the same as that returned by columns.
# 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