Class: ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter

Inherits:
AbstractAdapter
  • Object
show all
Defined in:
lib/active_record/connection_adapters/oracle_enhanced_adapter.rb

Overview

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)

  • :time_zone - database session time zone (it is recommended to set it using ENV which will be then also used for database session time zone)

Direct Known Subclasses

OracleAdapter

Constant Summary collapse

IDENTIFIER_MAX_LENGTH =

maximum length of Oracle identifiers

30
AUTOGENERATED_SEQUENCE_NAME =

:nodoc:

'autogenerated'.freeze
STATEMENT_TOKEN = 
"\n\n--@@@--\n\n"
DBMS_OUTPUT_BUFFER_SIZE =

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

10000
VERSION = 
'1.2.4'
@@do_not_prefetch_primary_key = 
{}
@@ignore_table_columns =

:nodoc:

nil
@@table_column_type =

:nodoc:

nil

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(connection, logger = nil) ⇒ OracleEnhancedAdapter

:nodoc:



451
452
453
454
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 451

def initialize(connection, logger = nil) #:nodoc:
  super
  @quoted_column_names, @quoted_table_names = {}, {}
end

Instance Attribute Details

#auto_retry ⇒ Object

If SQL statement fails due to lost connection then reconnect and retry SQL statement if autocommit mode is enabled. By default this functionality is disabled.



576
577
578
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 576

def auto_retry
  @auto_retry
end

Class Method Details

.boolean_to_string(bool) ⇒ Object

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



431
432
433
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 431

def self.boolean_to_string(bool)
  bool ? "Y" : "N"
end

.is_boolean_column?(name, field_type, table_name = nil) ⇒ Boolean

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.



424
425
426
427
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 424

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) ⇒ Boolean

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.



378
379
380
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 378

def self.is_date_column?(name, table_name = nil)
  name =~ /(^|_)date(_|$)/i
end

.is_integer_column?(name, table_name = nil) ⇒ Boolean

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.



408
409
410
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 408

def self.is_integer_column?(name, table_name = nil)
  name =~ /(^|_)id$/i
end

.valid_table_name?(name) ⇒ Boolean

unescaped table name should start with letter and contain letters, digits, _, $ or # can be prefixed with schema name CamelCase table names should be quoted



506
507
508
509
510
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 506

def self.valid_table_name?(name) #:nodoc:
  name = name.to_s
  name =~ /\A([A-Za-z_0-9]+\.)?[a-z][a-z_0-9\$#]*(@[A-Za-z_0-9\.]+)?\Z/ ||
  name =~ /\A([A-Za-z_0-9]+\.)?[A-Z][A-Z_0-9\$#]*(@[A-Za-z_0-9\.]+)?\Z/ ? true : false
end

Instance Method Details

#active? ⇒ Boolean

Returns true if the connection is active.



590
591
592
593
594
595
596
597
598
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 590

def active? #:nodoc:
  # Pings the connection to check if it's still good. Note that an
  # #active? method is also available, but that simply returns the
  # last known state, which isn't good enough if the connection has
  # gone stale since the last use.
  @connection.ping
rescue OracleEnhancedConnectionException
  false
end

#adapter_name ⇒ Object

:nodoc:



456
457
458
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 456

def adapter_name #:nodoc:
  'OracleEnhanced'
end

#add_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1132

def add_column(table_name, column_name, type, options = {}) #:nodoc:
  add_column_sql = "ALTER TABLE #{quote_table_name(table_name)} ADD"
  options[:type] = type
  column_metadata = "#{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  add_column_options!(column_metadata, options)
  add_column_sql = "#{add_column_sql} (#{column_metadata})"
  if [:text, :binary].include?(type) && tablespace = (options[:tablespace] || default_tablespace_for(type))
    add_column_sql << lob_tablespace(table_name, column_name, type, tablespace)
  end
  execute(add_column_sql)
end

#add_column_options!(sql, options) ⇒ Object

:nodoc:



1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1441

def add_column_options!(sql, options) #:nodoc:
  type = options[:type] || ((column = options[:column]) && column.type)
  type = type && type.to_sym
  # handle case of defaults for CLOB columns, which would otherwise get "quoted" incorrectly
  if options_include_default?(options)
    if type == :text
      sql << " DEFAULT #{quote(options[:default])}"
    else
      # from abstract adapter
      sql << " DEFAULT #{quote(options[:default], options[:column])}"
    end
  end
  # must explicitly add NULL or NOT NULL to allow change_column to work on migrations
  if options[:null] == false
    sql << " NOT NULL"
  elsif options[:null] == true
    sql << " NULL" unless type == :primary_key
  end
end

#add_comment(table_name, column_name, comment) ⇒ Object

:nodoc:



1181
1182
1183
1184
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1181

def add_comment(table_name, column_name, comment) #:nodoc:
  return if comment.blank?
  execute "COMMENT ON COLUMN #{quote_table_name(table_name)}.#{column_name} IS '#{comment}'"
end

#add_index(table_name, column_name, options = {}) ⇒ Object

clear cached indexes when adding new index



1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1083

def add_index(table_name, column_name, options = {}) #:nodoc:
  self.all_schema_indexes = nil
  column_names = Array(column_name)
  index_name   = index_name(table_name, :column => column_names)

  if Hash === options # legacy support, since this param was a string
    index_type = options[:unique] ? "UNIQUE" : ""
    index_name = options[:name] || index_name
    tablespace = if tablespace_name = (options[:tablespace] || default_tablespace_for('INDEX'))
      " TABLESPACE #{tablespace_name}"
                 else
                   ""
                 end
  else
    index_type = options
  end
  quoted_column_names = column_names.map { |e| quote_column_name(e) }.join(", ")
  execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{quoted_column_names})#{tablespace}#{options[:compress] ? ' COMPRESS 1' : ''}"
end

#add_limit_offset!(sql, options) ⇒ Object

:nodoc:



674
675
676
677
678
679
680
681
682
683
684
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 674

def add_limit_offset!(sql, options) #:nodoc:
  # added to_i for limit and offset to protect from SQL injection
  offset = (options[:offset] || 0).to_i

  if limit = options[:limit]
    limit = limit.to_i
    sql.replace "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_ where rownum <= #{offset+limit}) where raw_rnum_ > #{offset}"
  elsif offset > 0
    sql.replace "select * from (select raw_sql_.*, rownum raw_rnum_ from (#{sql}) raw_sql_) where raw_rnum_ > #{offset}"
  end
end

#add_order_by_for_association_limiting!(sql, options) ⇒ Object

ORDER BY clause for the passed order option.

Uses column aliases as defined by #distinct.



1493
1494
1495
1496
1497
1498
1499
1500
1501
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1493

def add_order_by_for_association_limiting!(sql, options) #:nodoc:
  return sql if options[:order].blank?

  order = options[:order].split(',').collect { |s| s.strip }.reject(&:blank?)
  order.map! {|s| $1 if s =~ / (.*)/}
  order = order.zip((0...order.size).to_a).map { |s,i| "alias_#{i}__ #{s}" }.join(', ')

  sql << " ORDER BY #{order}"
end

#add_table_comment(table_name, comment) ⇒ Object

:nodoc:



1186
1187
1188
1189
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1186

def add_table_comment(table_name, comment) #:nodoc:
  return if comment.blank?
  execute "COMMENT ON TABLE #{quote_table_name(table_name)} IS '#{comment}'"
end

#begin_db_transaction ⇒ Object

:nodoc:



658
659
660
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 658

def begin_db_transaction #:nodoc:
  @connection.autocommit = false
end

#cache_columns ⇒ Object

:singleton-method: Cache column description between requests. Could be used in development environment to avoid selecting table columns from data dictionary tables for each request. This can speed up request processing in development mode if development database is not on local computer.

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.cache_columns = true



898
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 898

cattr_accessor :cache_columns

#change_column(table_name, column_name, type, options = {}) ⇒ Object

:nodoc:



1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1158

def change_column(table_name, column_name, type, options = {}) #:nodoc:
  column = column_for(table_name, column_name)

  # remove :null option if its value is the same as current column definition
  # otherwise Oracle will raise error
  if options.has_key?(:null) && options[:null] == column.null
    options[:null] = nil
  end

  change_column_sql = "ALTER TABLE #{quote_table_name(table_name)} MODIFY #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}"
  options[:type] = type
  add_column_options!(change_column_sql, options)
  execute(change_column_sql)
end

#change_column_default(table_name, column_name, default) ⇒ Object

:nodoc:



1144
1145
1146
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1144

def change_column_default(table_name, column_name, default) #:nodoc:
  execute "ALTER TABLE #{quote_table_name(table_name)} MODIFY #{quote_column_name(column_name)} DEFAULT #{quote(default)}"
end

#change_column_null(table_name, column_name, null, default = nil) ⇒ Object

:nodoc:



1148
1149
1150
1151
1152
1153
1154
1155
1156
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1148

def change_column_null(table_name, column_name, null, default = nil) #:nodoc:
  column = column_for(table_name, column_name)

  unless null || default.nil?
    execute("UPDATE #{quote_table_name(table_name)} SET #{quote_column_name(column_name)}=#{quote(default)} WHERE #{quote_column_name(column_name)} IS NULL")
  end

  change_column table_name, column_name, column.sql_type, :null => null
end

#clear_columns_cache ⇒ Object

used just in tests to clear column cache



964
965
966
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 964

def clear_columns_cache #:nodoc:
  @@columns_cache = nil
end

#clear_ignored_table_columns ⇒ Object

used just in tests to clear ignored table columns



842
843
844
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 842

def clear_ignored_table_columns #:nodoc:
  @@ignore_table_columns = nil
end

#clear_prefetch_primary_key ⇒ Object

used just in tests to clear prefetch primary key flag for all tables



695
696
697
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 695

def clear_prefetch_primary_key #:nodoc:
  @@do_not_prefetch_primary_key = {}
end

#clear_types_for_columns ⇒ Object

used just in tests to clear column data type definitions



862
863
864
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 862

def clear_types_for_columns #:nodoc:
  @@table_column_type = nil
end

#column_comment(table_name, column_name) ⇒ Object

:nodoc:



1200
1201
1202
1203
1204
1205
1206
1207
1208
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1200

def column_comment(table_name, column_name) #:nodoc:
  (owner, table_name, db_link) = @connection.describe(table_name)
  select_value "    SELECT comments FROM all_col_comments\#{db_link}\n    WHERE owner = '\#{owner}'\n      AND table_name = '\#{table_name}'\n      AND column_name = '\#{column_name.upcase}'\n  SQL\nend\n"

#columns(table_name, name = nil) ⇒ Object

:nodoc:



901
902
903
904
905
906
907
908
909
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 901

def columns(table_name, name = nil) #:nodoc:
  # Don't double cache if config.cache_classes is turned on
  if @@cache_columns && !(defined?(Rails) && Rails.configuration.cache_classes)
    @@columns_cache ||= {}
    @@columns_cache[table_name] ||= columns_without_cache(table_name, name)
  else
    columns_without_cache(table_name, name)
  end
end

#columns_without_cache(table_name, name = nil) ⇒ Object

:nodoc:



911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
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
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 911

def columns_without_cache(table_name, name = nil) #:nodoc:
  # get ignored_columns by original table name
  ignored_columns = ignored_table_columns(table_name)

  (owner, desc_table_name, db_link) = @connection.describe(table_name)

  if has_primary_key_trigger?(table_name, owner, desc_table_name, db_link)
    @@do_not_prefetch_primary_key[table_name] = true
  end

  table_cols = "    select column_name as name, data_type as sql_type, data_default, nullable, virtual_column, hidden_column, \n           decode(data_type, 'NUMBER', data_precision,\n                             'FLOAT', data_precision,\n                             'VARCHAR2', decode(char_used, 'C', char_length, data_length),\n                             'CHAR', decode(char_used, 'C', char_length, data_length),\n                              null) as limit,\n           decode(data_type, 'NUMBER', data_scale, null) as scale\n      from all_tab_cols\#{db_link}\n     where owner      = '\#{owner}'\n       and hidden_column = 'NO' \n       and table_name = '\#{desc_table_name}'\n     order by column_id\n  SQL\n\n  # added deletion of ignored columns\n  select_all(table_cols, name).delete_if do |row|\n    ignored_columns && ignored_columns.include?(row['name'].downcase)\n  end.map do |row|\n    limit, scale = row['limit'], row['scale']\n    if limit || scale\n      row['sql_type'] << \"(\#{(limit || 38).to_i}\" + ((scale = scale.to_i) > 0 ? \",\#{scale})\" : \")\")\n    end\n\n    # clean up odd default spacing from Oracle\n    if row['data_default']\n      row['data_default'].sub!(/^(.*?)\\s*$/, '\\1')\n      row['data_default'].sub!(/^'(.*)'$/, '\\1')\n      row['data_default'] = nil if row['data_default'] =~ /^(null|empty_[bc]lob\\(\\))$/i\n    end\n\n    OracleEnhancedColumn.new(oracle_downcase(row['name']),\n                     row['data_default'],\n                     row['sql_type'],\n                     row['nullable'] == 'Y',\n                     # pass table name for table specific column definitions\n                     table_name,\n                     # pass column type if specified in class definition\n                     get_type_for_column(table_name, oracle_downcase(row['name'])), row['virtual_column']=='YES')\n  end\nend\n"

#commit_db_transaction ⇒ Object

:nodoc:



662
663
664
665
666
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 662

def commit_db_transaction #:nodoc:
  @connection.commit
ensure
  @connection.autocommit = true
end

#create_table(name, options = {}, &block) ⇒ Object

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

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


1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1008

def create_table(name, options = {}, &block)
  create_sequence = options[:id] != false
  column_comments = {}
  
  table_definition = TableDefinition.new(self)
  table_definition.primary_key(options[:primary_key] || Base.get_primary_key(name.to_s.singularize)) unless options[:id] == false

  # store that primary key was defined in create_table block
  unless create_sequence
    class << table_definition
      attr_accessor :create_sequence
      def primary_key(*args)
        self.create_sequence = true
        super(*args)
      end
    end
  end

  # store column comments
  class << table_definition
    attr_accessor :column_comments
    def column(name, type, options = {})
      if type==:virtual
        @columns <<=OracleEnhancedColumnDefinition.new(@base, name, type)
      end
      if options[:comment]
        self.column_comments ||= {}
        self.column_comments[name] = options[:comment]
      end
      super(name, type, options)
    end
  end

  result = block.call(table_definition) if block
  create_sequence = create_sequence || table_definition.create_sequence
  column_comments = table_definition.column_comments if table_definition.column_comments


  if options[:force] && table_exists?(name)
    drop_table(name, options)
  end

  create_sql = "CREATE#{' GLOBAL TEMPORARY' if options[:temporary]} TABLE "
  create_sql << "#{quote_table_name(name)} ("
  create_sql << table_definition.to_sql
  create_sql << ")"
  unless options[:skip_tablespaces] ||  options[:temporary]
    create_sql << "#{table_tablespace}"
    table_definition.columns.select{|c| ['CLOB', 'BLOB'].include?(c.sql_type)}.each do |cd|
      create_sql << "#{lob_tablespace(name, cd.name, cd.sql_type)}"
    end
  end
  create_sql << " #{options[:options]}"
  execute create_sql
  
  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

#current_database ⇒ Object

current database name



766
767
768
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 766

def current_database
  select_one("select sys_context('userenv','db_name') db from dual")["db"]
end

#current_user ⇒ Object

current database session user



771
772
773
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 771

def current_user
  select_one("select sys_context('userenv','session_user') u from dual")['u']
end

#dbms_output_enabled? ⇒ Boolean

Is DBMS_Output logging enabled?



1636
1637
1638
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1636

def dbms_output_enabled?
  @enable_dbms_output
end

#default_sequence_name(table_name, primary_key = nil) ⇒ Object

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



701
702
703
704
705
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 701

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

#default_sequence_start_value ⇒ Object

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

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.default_sequence_start_value = 1



973
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 973

cattr_accessor :default_sequence_start_value

#disable_dbms_output ⇒ Object

Turn DBMS_Output logging off



1630
1631
1632
1633
1634
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1630

def disable_dbms_output
  set_dbms_output_plsql_connection
  @enable_dbms_output = false
  plsql(:dbms_output).sys.dbms_output.disable
end

#disconnect! ⇒ Object

Disconnects from the database.



608
609
610
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 608

def disconnect! #:nodoc:
  @connection.logoff rescue nil
end

#distinct(columns, order_by) ⇒ Object

SELECT DISTINCT clause for a given set of columns and a given ORDER BY clause.

Oracle requires the ORDER BY columns to be in the SELECT list for DISTINCT queries. However, with those columns included in the SELECT DISTINCT list, you won’t actually get a distinct list of the column you want (presuming the column has duplicates with multiple values for the ordered-by columns. So we use the FIRST_VALUE function to get a single (first) value for each column, effectively making every row the same.

distinct("posts.id", "posts.created_at desc")



1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1471

def distinct(columns, order_by) #:nodoc:
  return "DISTINCT #{columns}" if order_by.blank?

  # construct a valid DISTINCT clause, ie. one that includes the ORDER BY columns, using
  # FIRST_VALUE such that the inclusion of these columns doesn't invalidate the DISTINCT
  order_columns = order_by.split(',').map { |s| s.strip }.reject(&:blank?)
  order_columns = order_columns.zip((0...order_columns.size).to_a).map do |c, i|
    "FIRST_VALUE(#{c.split.first}) OVER (PARTITION BY #{columns} ORDER BY #{c}) AS alias_#{i}__"
  end
  sql = "DISTINCT #{columns}, "
  sql << order_columns * ", "
end

#drop_table(name, options = {}) ⇒ Object

:nodoc:



1076
1077
1078
1079
1080
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1076

def drop_table(name, options = {}) #:nodoc:
  super(name)
  seq_name = options[:sequence_name] || default_sequence_name(name)
  execute "DROP SEQUENCE #{quote_table_name(seq_name)}" rescue nil
end

#emulate_booleans ⇒ Object

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



344
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 344

cattr_accessor :emulate_booleans

#emulate_booleans_from_strings ⇒ Object

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



418
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 418

cattr_accessor :emulate_booleans_from_strings

#emulate_dates ⇒ Object

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



358
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 358

cattr_accessor :emulate_dates

#emulate_dates_by_column_name ⇒ Object

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



372
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 372

cattr_accessor :emulate_dates_by_column_name

#emulate_integers_by_column_name ⇒ Object

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



402
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 402

cattr_accessor :emulate_integers_by_column_name

#enable_dbms_output ⇒ Object

Turn DBMS_Output logging on



1624
1625
1626
1627
1628
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1624

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) ⇒ Object

Executes a SQL statement



617
618
619
620
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 617

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

#full_drop(preserve_tables = false) ⇒ Object 



1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1429

def full_drop(preserve_tables=false)
  s = preserve_tables ? [] : [structure_drop]
  s << temp_table_drop if preserve_tables
  s << drop_sql_for_feature("view")
  s << drop_sql_for_feature("synonym")
  s << drop_sql_for_feature("type")
  s << drop_sql_for_object("package")
  s << drop_sql_for_object("function")
  s << drop_sql_for_object("procedure")
  s.join("\n\n")
end

#get_type_for_column(table_name, column_name) ⇒ Object

:nodoc:



857
858
859
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 857

def get_type_for_column(table_name, column_name) #:nodoc:
  @@table_column_type && @@table_column_type[table_name] && @@table_column_type[table_name][column_name.to_s.downcase]
end

#has_primary_key_trigger?(table_name, owner = nil, desc_table_name = nil, db_link = nil) ⇒ Boolean

check if table has primary key trigger with _pkt suffix



867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 867

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\n    FROM all_triggers\#{db_link}\n    WHERE owner = '\#{owner}'\n      AND trigger_name = '\#{trigger_name}'\n      AND table_owner = '\#{owner}'\n      AND table_name = '\#{desc_table_name}'\n      AND status = 'ENABLED'\n  SQL\n  select_value(pkt_sql) ? true : false\nend\n"

#ignore_table_columns(table_name, *args) ⇒ Object

set ignored columns for table



829
830
831
832
833
834
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 829

def ignore_table_columns(table_name, *args) #:nodoc:
  @@ignore_table_columns ||= {}
  @@ignore_table_columns[table_name] ||= []
  @@ignore_table_columns[table_name] += args.map{|a| a.to_s.downcase}
  @@ignore_table_columns[table_name].uniq!
end

#ignored_table_columns(table_name) ⇒ Object

:nodoc:



836
837
838
839
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 836

def ignored_table_columns(table_name) #:nodoc:
  @@ignore_table_columns ||= {}
  @@ignore_table_columns[table_name]
end

#index_name(table_name, options) ⇒ Object

returned shortened index name if default is too large



1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1110

def index_name(table_name, options) #:nodoc:
  default_name = super(table_name, options)
  return default_name if default_name.length <= IDENTIFIER_MAX_LENGTH
  
  # remove 'index', 'on' and 'and' keywords
  shortened_name = "i_#{table_name}_#{Array(options[:column]) * '_'}"
  
  # leave just first three letters from each word
  if shortened_name.length > IDENTIFIER_MAX_LENGTH
    shortened_name = shortened_name.split('_').map{|w| w[0,3]}.join('_')
  end
  # generate unique name using hash function
  if shortened_name.length > OracleEnhancedAdapter::IDENTIFIER_MAX_LENGTH
    shortened_name = 'i'+Digest::SHA1.hexdigest(default_name)[0,OracleEnhancedAdapter::IDENTIFIER_MAX_LENGTH-1]
  end
  @logger.warn "#{adapter_name} shortened default index name #{default_name} to #{shortened_name}" if @logger
  shortened_name
end

#indexes(table_name, name = nil) ⇒ Object

This method selects all indexes at once, and caches them in a class variable. Subsequent index calls get them from the variable, without going to the DB.



793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 793

def indexes(table_name, name = nil) #:nodoc:
  (owner, table_name, db_link) = @connection.describe(table_name)
  unless all_schema_indexes
    result = select_all("      SELECT lower(i.table_name) as table_name, lower(i.index_name) as index_name, i.uniqueness, lower(i.tablespace_name) as tablespace_name, lower(c.column_name) as column_name, e.column_expression as column_expression\n        FROM all_indexes\#{db_link} i\n        JOIN all_ind_columns\#{db_link} c on c.index_name = i.index_name and c.index_owner = i.owner\n        LEFT OUTER JOIN all_ind_expressions\#{db_link} e on e.index_name = i.index_name and e.index_owner = i.owner and e.column_position = c.column_position\n       WHERE i.owner = '\#{owner}'\n         AND i.table_owner = '\#{owner}'\n         AND NOT EXISTS (SELECT uc.index_name FROM all_constraints uc WHERE uc.index_name = i.index_name AND uc.owner = i.owner AND uc.constraint_type = 'P')\n        ORDER BY i.index_name, c.column_position\n    SQL\n\n    current_index = nil\n    self.all_schema_indexes = []\n\n    result.each do |row|\n      # have to keep track of indexes because above query returns dups\n      # there is probably a better query we could figure out\n      if current_index != row['index_name']\n        self.all_schema_indexes << ::ActiveRecord::ConnectionAdapters::OracleEnhancedIndexDefinition.new(row['table_name'], row['index_name'], row['uniqueness'] == \"UNIQUE\", row['tablespace_name'], [])\n        current_index = row['index_name']\n      end\n      self.all_schema_indexes.last.columns << (row['column_expression'].nil? ? row['column_name'] : row['column_expression'].gsub('\"','').downcase)\n    end\n  end\n\n  # Return the indexes just for the requested table, since AR is structured that way\n  table_name = table_name.downcase\n  all_schema_indexes.select{|i| i.table == table_name}\nend\n")

#insert_fixture(fixture, table_name) ⇒ Object

Inserts the given fixture into the table. Overridden to properly handle lobs.



708
709
710
711
712
713
714
715
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 708

def insert_fixture(fixture, table_name) #:nodoc:
  super

  klass = fixture.class_name.constantize rescue nil
  if klass.respond_to?(:ancestors) && klass.ancestors.include?(ActiveRecord::Base)
    write_lobs(table_name, klass, fixture)
  end
end

#is_date_column?(name, table_name = nil) ⇒ Boolean

instance method uses at first check if column type defined at class level



383
384
385
386
387
388
389
390
391
392
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 383

def is_date_column?(name, table_name = nil) #:nodoc:
  case get_type_for_column(table_name, name)
  when nil
    self.class.is_date_column?(name, table_name)
  when :date
    true
  else
    false
  end
end

#lob_order_by_expression(klass, order) ⇒ Object

change LOB column for ORDER BY clause just first 100 characters are taken for ordering



746
747
748
749
750
751
752
753
754
755
756
757
758
759
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 746

def lob_order_by_expression(klass, order) #:nodoc:
  return order if order.nil?
  changed = false
  new_order = order.to_s.strip.split(/, */).map do |order_by_col|
    column_name, asc_desc = order_by_col.split(/ +/)
    if column = klass.columns.detect { |col| col.name == column_name && col.sql_type =~ /LOB$/i}
      changed = true
      "DBMS_LOB.SUBSTR(#{column_name},100,1) #{asc_desc}"
    else
      order_by_col
    end
  end.join(', ')
  changed ? new_order : order
end

#native_database_types ⇒ Object

:nodoc:



464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 464

def native_database_types #:nodoc:
  {
    :primary_key => "NUMBER(38) NOT NULL PRIMARY KEY",
    :string      => { :name => "VARCHAR2", :limit => 255 },
    :text        => { :name => "CLOB" },
    :integer     => { :name => "NUMBER", :limit => 38 },
    :float       => { :name => "NUMBER" },
    :decimal     => { :name => "DECIMAL" },
    :datetime    => { :name => "DATE" },
    # changed to native TIMESTAMP type
    # :timestamp   => { :name => "DATE" },
    :timestamp   => { :name => "TIMESTAMP" },
    :time        => { :name => "DATE" },
    :date        => { :name => "DATE" },
    :binary      => { :name => "BLOB" },
    # if emulate_booleans_from_strings then store booleans in VARCHAR2
    :boolean     => emulate_booleans_from_strings ?
      { :name => "VARCHAR2", :limit => 1 } : { :name => "NUMBER", :limit => 1 }
  }
end

#next_sequence_value(sequence_name) ⇒ Object

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?).



652
653
654
655
656
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 652

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

#pk_and_sequence_for(table_name) ⇒ Object

Find a table’s primary key and sequence. Note: Only primary key is implemented - sequence will be nil.



1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1220

def pk_and_sequence_for(table_name) #:nodoc:
  (owner, table_name, db_link) = @connection.describe(table_name)

  # changed select from all_constraints to user_constraints - much faster in large data dictionaries
  pks = select_values("    select cc.column_name\n      from user_constraints\#{db_link} c, user_cons_columns\#{db_link} cc\n     where c.owner = '\#{owner}'\n       and c.table_name = '\#{table_name}'\n       and c.constraint_type = 'P'\n       and cc.owner = c.owner\n       and cc.constraint_name = c.constraint_name\n  SQL\n\n  # only support single column keys\n  pks.size == 1 ? [oracle_downcase(pks.first), nil] : nil\nend\n", 'Primary Key')

#prefetch_primary_key?(table_name = nil) ⇒ Boolean

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



690
691
692
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 690

def prefetch_primary_key?(table_name = nil)
  ! @@do_not_prefetch_primary_key[table_name.to_s]
end

#quote(value, column = nil) ⇒ Object

:nodoc:



525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 525

def quote(value, column = nil) #:nodoc:
  if value && column
    case column.type
    when :text, :binary
      %Q{empty_#{ column.sql_type.downcase rescue 'blob' }()}
    # NLS_DATE_FORMAT independent TIMESTAMP support
    when :timestamp
      quote_timestamp_with_to_timestamp(value)
    # NLS_DATE_FORMAT independent DATE support
    when :date, :time, :datetime
      quote_date_with_to_date(value)
    else
      super
    end
  elsif value.acts_like?(:date)
    quote_date_with_to_date(value)
  elsif value.acts_like?(:time)
    value.to_i == value.to_f ? quote_date_with_to_date(value) : quote_timestamp_with_to_timestamp(value)
  else
    super
  end
end

#quote_column_name(name) ⇒ Object

QUOTING ==================================================

see: abstract/quoting.rb



496
497
498
499
500
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 496

def quote_column_name(name) #:nodoc:
  # camelCase column names need to be quoted; not that anyone using Oracle
  # would really do this, but handling this case means we pass the test...
  @quoted_column_names[name] = name.to_s =~ /[A-Z]/ ? "\"#{name}\"" : quote_oracle_reserved_words(name)
end

#quote_date_with_to_date(value) ⇒ Object

:nodoc:



558
559
560
561
562
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 558

def quote_date_with_to_date(value) #:nodoc:
  # should support that composite_primary_keys gem will pass date as string
  value = quoted_date(value) if value.acts_like?(:date) || value.acts_like?(:time)
  "TO_DATE('#{value}','YYYY-MM-DD HH24:MI:SS')"
end

#quote_string(s) ⇒ Object

:nodoc:



521
522
523
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 521

def quote_string(s) #:nodoc:
  s.gsub(/'/, "''")
end

#quote_table_name(name) ⇒ Object

:nodoc:



512
513
514
515
516
517
518
519
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 512

def quote_table_name(name) #:nodoc:
  # abstract_adapter calls quote_column_name from quote_table_name, so prevent that
  @quoted_table_names[name] ||= if self.class.valid_table_name?(name)
    name
  else
    "\"#{name}\""
  end
end

#quote_timestamp_with_to_timestamp(value) ⇒ Object

:nodoc:



564
565
566
567
568
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 564

def quote_timestamp_with_to_timestamp(value) #:nodoc:
  # add up to 9 digits of fractional seconds to inserted time
  value = "#{quoted_date(value)}:#{("%.6f"%value.to_f).split('.')[1]}" if value.acts_like?(:time)
  "TO_TIMESTAMP('#{value}','YYYY-MM-DD HH24:MI:SS:FF6')"
end

#quoted_false ⇒ Object

:nodoc:



553
554
555
556
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 553

def quoted_false #:nodoc:
  return "'#{self.class.boolean_to_string(false)}'" if emulate_booleans_from_strings
  "0"
end

#quoted_true ⇒ Object

:nodoc:



548
549
550
551
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 548

def quoted_true #:nodoc:
  return "'#{self.class.boolean_to_string(true)}'" if emulate_booleans_from_strings
  "1"
end

#raw_connection ⇒ Object

return raw OCI8 or JDBC connection



585
586
587
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 585

def raw_connection
  @connection.raw_connection
end

#reconnect! ⇒ Object

Reconnects to the database.



601
602
603
604
605
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 601

def reconnect! #:nodoc:
  @connection.reset!
rescue OracleEnhancedConnectionException => e
  @logger.warn "#{adapter_name} automatic reconnection failed: #{e.message}" if @logger
end

#remove_column(table_name, column_name) ⇒ Object

:nodoc:



1177
1178
1179
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1177

def remove_column(table_name, column_name) #:nodoc:
  execute "ALTER TABLE #{quote_table_name(table_name)} DROP COLUMN #{quote_column_name(column_name)}"
end

#remove_index(table_name, options = {}) ⇒ Object

clear cached indexes when removing index



1104
1105
1106
1107
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1104

def remove_index(table_name, options = {}) #:nodoc:
  self.all_schema_indexes = nil
  execute "DROP INDEX #{index_name(table_name, options)}"
end

#rename_column(table_name, column_name, new_column_name) ⇒ Object

:nodoc:



1173
1174
1175
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1173

def rename_column(table_name, column_name, new_column_name) #:nodoc:
  execute "ALTER TABLE #{quote_table_name(table_name)} RENAME COLUMN #{quote_column_name(column_name)} to #{quote_column_name(new_column_name)}"
end

#rename_table(name, new_name) ⇒ Object

:nodoc:



1071
1072
1073
1074
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1071

def rename_table(name, new_name) #:nodoc:
  execute "RENAME #{quote_table_name(name)} TO #{quote_table_name(new_name)}"
  execute "RENAME #{quote_table_name("#{name}_seq")} TO #{quote_table_name("#{new_name}_seq")}" rescue nil
end

#rollback_db_transaction ⇒ Object

:nodoc:



668
669
670
671
672
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 668

def rollback_db_transaction #:nodoc:
  @connection.rollback
ensure
  @connection.autocommit = true
end

#select_rows(sql, name = nil) ⇒ Object

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



624
625
626
627
628
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 624

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

#set_type_for_columns(table_name, column_type, *args) ⇒ Object

set explicit type for specified table columns



849
850
851
852
853
854
855
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 849

def set_type_for_columns(table_name, column_type, *args) #:nodoc:
  @@table_column_type ||= {}
  @@table_column_type[table_name] ||= {}
  args.each do |col|
    @@table_column_type[table_name][col.to_s.downcase] = column_type
  end
end

#string_to_date_format ⇒ Object

:singleton-method: Specify non-default date format that should be used when assigning string values to :date columns, e.g.:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.string_to_date_format = 


440
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 440

cattr_accessor :string_to_date_format

#string_to_time_format ⇒ Object

:singleton-method: Specify non-default time format that should be used when assigning string values to :datetime columns, e.g.:

ActiveRecord::ConnectionAdapters::OracleEnhancedAdapter.string_to_time_format = 


448
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 448

cattr_accessor :string_to_time_format

#structure_drop ⇒ Object

:nodoc:



1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1411

def structure_drop #:nodoc:
  s = select_all("select sequence_name from user_sequences order by 1").inject("") do |drop, seq|
    drop << "drop sequence #{seq.to_a.first.last};\n\n"
  end

  # changed select from user_tables to all_tables - much faster in large data dictionaries
  select_all("select table_name from all_tables where owner = sys_context('userenv','session_user') order by 1").inject(s) do |drop, table|
    drop << "drop table #{table.to_a.first.last} cascade constraints;\n\n"
  end
end

#structure_dump ⇒ Object

:nodoc:



1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1238

def structure_dump #:nodoc:
  s = select_all("select sequence_name from user_sequences order by 1").inject("") do |structure, seq|
    structure << "create sequence #{seq.to_a.first.last}#{STATEMENT_TOKEN}"
  end

  # changed select from user_tables to all_tables - much faster in large data dictionaries
  select_all("select table_name from all_tables where owner = sys_context('userenv','session_user') order by 1").inject(s) do |structure, table|
    table_name = table['table_name']
    virtual_columns = virtual_columns_for(table_name)
    ddl = "create#{ ' global temporary' if temporary?(table_name)} table #{table_name} (\n "
    cols = select_all(%Q{
      select column_name, data_type, data_length, char_used, char_length, data_precision, data_scale, data_default, nullable
      from user_tab_columns
      where table_name = '#{table_name}'
      order by column_id
    }).map do |row|
      if(v = virtual_columns.find {|col| col['column_name'] == row['column_name']})
        structure_dump_virtual_column(row, v['data_default'])
      else
        structure_dump_column(row)
      end
    end
    ddl << cols.join(",\n ")
    ddl << structure_dump_constraints(table_name)
    ddl << "\n)#{STATEMENT_TOKEN}"
    structure << ddl
    structure << structure_dump_indexes(table_name)
  end
end

#structure_dump_column(column) ⇒ Object 



1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1282

def structure_dump_column(column)
  col = "#{column['column_name'].downcase} #{column['data_type'].downcase}"
  if column['data_type'] =='NUMBER' and !column['data_precision'].nil?
    col << "(#{column['data_precision'].to_i}"
    col << ",#{column['data_scale'].to_i}" if !column['data_scale'].nil?
    col << ')'
  elsif column['data_type'].include?('CHAR')
    length = column['char_used'] == 'C' ? column['char_length'].to_i : column['data_length'].to_i
    col <<  "(#{length})"
  end
  col << " default #{column['data_default']}" if !column['data_default'].nil?
  col << ' not null' if column['nullable'] == 'N'
  col  
end

#structure_dump_constraints(table) ⇒ Object 



1297
1298
1299
1300
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1297

def structure_dump_constraints(table)
  out = [structure_dump_primary_key(table), structure_dump_unique_keys(table)].flatten.compact
  out.length > 0 ? ",\n#{out.join(",\n")}" : ''
end

#structure_dump_db_stored_code ⇒ Object

Extract all stored procedures, packages, synonyms and views.



1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1355

def structure_dump_db_stored_code #:nodoc:
  structure = ""
  select_all("select distinct name, type 
               from all_source 
              where type in ('PROCEDURE', 'PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'TRIGGER', 'TYPE') 
                and  owner = sys_context('userenv','session_user') order by type").inject("") do |structure, source|
      ddl = "create or replace   \n "
      lines = select_all(%Q{
              select text
                from all_source
               where name = '#{source['name']}'
                 and type = '#{source['type']}'
                 and owner = sys_context('userenv','session_user')
               order by line 
            }).map do |row|
        ddl << row['text'] if row['text'].size > 1
      end
      ddl << ";" unless ddl.strip.last == ";"
      structure << ddl << STATEMENT_TOKEN
  end

  # export views 
  select_all("select view_name, text from user_views").inject(structure) do |structure, view|
    ddl = "create or replace view #{view['view_name']} AS\n "
    # any views with empty lines will cause OCI to barf when loading. remove blank lines =/ 
    ddl << view['text'].gsub(/^\n/, '') 
    structure << ddl << STATEMENT_TOKEN
  end

  # export synonyms 
  select_all("select owner, synonym_name, table_name, table_owner 
                from all_synonyms  
               where owner = sys_context('userenv','session_user') ").inject(structure) do |structure, synonym|
    ddl = "create or replace #{synonym['owner'] == 'PUBLIC' ? 'PUBLIC' : '' } SYNONYM #{synonym['synonym_name']} for #{synonym['table_owner']}.#{synonym['table_name']}"
    structure << ddl << STATEMENT_TOKEN
  end
end

#structure_dump_fk_constraints ⇒ Object 



1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1340

def structure_dump_fk_constraints
  fks = select_all("select table_name from all_tables where owner = sys_context('userenv','session_user') order by 1").map do |table|
    if respond_to?(:foreign_keys) && (foreign_keys = foreign_keys(table["table_name"])).any?
      foreign_keys.map do |fk|
        column = fk.options[:column] || "#{fk.to_table.to_s.singularize}_id"
        constraint_name = foreign_key_constraint_name(fk.from_table, column, fk.options)
        sql = "ALTER TABLE #{quote_table_name(fk.from_table)} ADD CONSTRAINT #{quote_column_name(constraint_name)} "
        sql << "#{foreign_key_definition(fk.to_table, fk.options)}"
      end
    end
  end.flatten.compact.join(STATEMENT_TOKEN)
  fks.length > 1 ? "#{fks}#{STATEMENT_TOKEN}" : ''
end

#structure_dump_indexes(table_name) ⇒ Object 



1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1393

def structure_dump_indexes(table_name)
  statements = indexes(table_name).map do |options|
  #def add_index(table_name, column_name, options = {})
    column_names = options[:columns]
    options = {:name => options[:name], :unique => options[:unique]}
    index_name   = index_name(table_name, :column => column_names)
    if Hash === options # legacy support, since this param was a string
      index_type = options[:unique] ? "UNIQUE" : ""
      index_name = options[:name] || index_name
    else
      index_type = options
    end
    quoted_column_names = column_names.map { |e| quote_column_name(e) }.join(", ")
    "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{quoted_column_names})"
  end
  statements.length > 0 ? "#{statements.join(STATEMENT_TOKEN)}#{STATEMENT_TOKEN}" : ''
end

#structure_dump_primary_key(table) ⇒ Object 



1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1302

def structure_dump_primary_key(table)
  opts = {:name => '', :cols => []}
  pks = select_all("    select a.constraint_name, a.column_name, a.position\n      from user_cons_columns a \n      join user_constraints c  \n        on a.constraint_name = c.constraint_name \n     where c.table_name = '\#{table.upcase}' \n       and c.constraint_type = 'P'\n       and c.owner = sys_context('userenv', 'session_user')\n  SQL\n  pks.each do |row|\n    opts[:name] = row['constraint_name']\n    opts[:cols][row['position']-1] = row['column_name']\n  end\n  opts[:cols].length > 0 ? \" CONSTRAINT \#{opts[:name]} PRIMARY KEY (\#{opts[:cols].join(',')})\" : nil\nend\n", "Primary Keys")

#structure_dump_unique_keys(table) ⇒ Object 



1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1320

def structure_dump_unique_keys(table)
  keys = {}
  uks = select_all("    select a.constraint_name, a.column_name, a.position\n      from user_cons_columns a \n      join user_constraints c  \n        on a.constraint_name = c.constraint_name \n     where c.table_name = '\#{table.upcase}' \n       and c.constraint_type = 'U'\n       and c.owner = sys_context('userenv', 'session_user')\n  SQL\n  uks.each do |uk|\n    keys[uk['constraint_name']] ||= []\n    keys[uk['constraint_name']][uk['position']-1] = uk['column_name']\n  end\n  keys.map do |k,v|\n    \" CONSTRAINT \#{k} UNIQUE (\#{v.join(',')})\"\n  end\nend\n", "Primary Keys")

#structure_dump_virtual_column(column, data_default) ⇒ Object 



1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1268

def structure_dump_virtual_column(column, data_default)
  data_default = data_default.gsub(/"/, '')
  col = "#{column['column_name'].downcase} #{column['data_type'].downcase}"
  if column['data_type'] =='NUMBER' and !column['data_precision'].nil?
    col << "(#{column['data_precision'].to_i}"
    col << ",#{column['data_scale'].to_i}" if !column['data_scale'].nil?
    col << ')'
  elsif column['data_type'].include?('CHAR')
    length = column['char_used'] == 'C' ? column['char_length'].to_i : column['data_length'].to_i
    col <<  "(#{length})"
  end
  col << " GENERATED ALWAYS AS (#{data_default}) VIRTUAL"
end

#supports_migrations? ⇒ Boolean

:nodoc:



460
461
462
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 460

def supports_migrations? #:nodoc:
  true
end

#table_alias_length ⇒ Object

:nodoc:



488
489
490
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 488

def table_alias_length #:nodoc:
  IDENTIFIER_MAX_LENGTH
end

#table_comment(table_name) ⇒ Object

:nodoc:



1191
1192
1193
1194
1195
1196
1197
1198
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1191

def table_comment(table_name) #:nodoc:
  (owner, table_name, db_link) = @connection.describe(table_name)
  select_value "    SELECT comments FROM all_tab_comments\#{db_link}\n    WHERE owner = '\#{owner}'\n      AND table_name = '\#{table_name}'\n  SQL\nend\n"

#tables(name = nil) ⇒ Object

:nodoc:



775
776
777
778
779
780
781
782
783
784
785
786
787
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 775

def tables(name = nil) #:nodoc:
  # changed select from user_tables to all_tables - much faster in large data dictionaries
  sql = "    select decode(table_name,upper(table_name),lower(table_name),table_name) name \n      from all_tables \n      where owner = sys_context('userenv','session_user')\n      and table_name not in (\n        select queue_table \n          from all_queue_tables \n          where owner = sys_context('userenv', 'session_user'))\n  SQL\n  select_all(sql).map {|t| t['name']}\nend\n"

#tablespace(table_name) ⇒ Object 



883
884
885
886
887
888
889
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 883

def tablespace(table_name)
  select_value "    SELECT tablespace_name \n    FROM user_tables\n    WHERE table_name='\#{table_name.to_s.upcase}'\n  SQL\nend\n"

#temp_table_drop ⇒ Object 



1422
1423
1424
1425
1426
1427
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1422

def temp_table_drop
  # changed select from user_tables to all_tables - much faster in large data dictionaries
  select_all("select table_name from all_tables where owner = sys_context('userenv','session_user') and temporary = 'Y' order by 1").inject('') do |drop, table|
    drop << "drop table #{table.to_a.first.last} cascade constraints;\n\n"
  end
end

#temporary?(table_name) ⇒ Boolean 



1484
1485
1486
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1484

def temporary?(table_name)
  select_value("select temporary from user_tables where table_name = '#{table_name.upcase}'") == 'Y'
end

#type_to_sql(type, limit = nil, precision = nil, scale = nil) ⇒ Object

Maps logical Rails types to Oracle-specific data types.



1211
1212
1213
1214
1215
1216
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 1211

def type_to_sql(type, limit = nil, precision = nil, scale = nil) #:nodoc:
  # Ignore options for :text and :binary columns
  return super(type, nil, nil, nil) if ['text', 'binary'].include?(type.to_s)

  super
end

#write_lobs(table_name, klass, attributes) ⇒ Object

Writes LOB values from attributes, as indicated by the LOB columns of klass.



718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
 
# File 'lib/active_record/connection_adapters/oracle_enhanced_adapter.rb', line 718

def write_lobs(table_name, klass, attributes) #:nodoc:
  # is class with composite primary key>
  is_with_cpk = klass.respond_to?(:composite?) && klass.composite?
  if is_with_cpk
    id = klass.primary_key.map {|pk| attributes[pk.to_s] }
  else
    id = quote(attributes[klass.primary_key])
  end
  klass.columns.select { |col| col.sql_type =~ /LOB$/i }.each do |col|
    value = attributes[col.name]
    # changed sequence of next two lines - should check if value is nil before converting to yaml
    next if value.nil?  || (value == '')
    value = value.to_yaml if col.text? && klass.serialized_attributes[col.name]
    uncached do
      if is_with_cpk
        lob = select_one("SELECT #{col.name} FROM #{table_name} WHERE #{klass.composite_where_clause(id)} FOR UPDATE",
                          'Writable Large Object')[col.name]
      else
        lob = select_one("SELECT #{col.name} FROM #{table_name} WHERE #{klass.primary_key} = #{id} FOR UPDATE",
                         'Writable Large Object')[col.name]
      end
      @connection.write_lob(lob, value.to_s, col.type == :binary)
    end
  end
end