ray_apps_blog

November 16, 2007

module ActiveRecord::ConnectionAdapters
  class OracleColumn
    def simplified_type(field_type)
      return :boolean if OracleAdapter.emulate_booleans && field_type == 'NUMBER(1)'
      case self.name
        # RSI: treat columns which end with 'date' as ruby date columns
        when /date$/i then :date
        # RSI: removed 'date' from regex
        when /time/i then :datetime
        # RSI: treat id columns (primary key) as integer
        when /^id$/i then :integer
        # RSI: treat _id columns (foreign key) as integer
        when /_id$/i then :integer
        else super
      end
    end
  end

  # RSI: patch to change selected results NUMBER to integer for primary and foreign keys
  class OracleAdapter
    def select(sql, name = nil)
      cursor = execute(sql, name)
      cols = cursor.get_col_names.map { |x| oracle_downcase(x) }
      rows = []

      while row = cursor.fetch
        hash = Hash.new

        cols.each_with_index do |col, i|
          hash[col] =
            case row[i]
            when OCI8::LOB
              name == 'Writable Large Object' ? row[i]: row[i].read
            when OraDate
              (row[i].hour == 0 and row[i].minute == 0 and row[i].second == 0) ?
              row[i].to_date : row[i].to_time
            else row[i]
            end unless col == 'raw_rnum_'
          # RSI: patch - convert to integer if column is ID or ends with _ID
          hash[col] = hash[col].to_i if (col =~ /^id$/i || col =~ /_id$/i) && hash[col]
        end

        rows << hash
      end

      rows
    ensure
      cursor.close if cursor
    end
  end
end

11 Comments »

1. 

   very yes!

   Comment by pt — March 7, 2008 @ 2:56 am | Reply

2. 

   that’s nice!

   Comment by Jesse Hu — April 1, 2008 @ 1:19 pm | Reply

3. 

   I tried your code by modifying the oracle-adapter.rb file
   and add my companies own legacy conventions. Here all keys end with _pk or _fk.

   def simplified_type(field_type)
   return :boolean if OracleAdapter.emulate_booleans && field_type == ‘NUMBER(1)’
   case field_type
   when /date|time/i then :datetime
   # RSI: treat columns which end with ‘date’ as ruby date columns
   when /date$/i then :date
   # RSI: removed ‘date’ from regex
   when /time/i then :datetime
   # RSI: treat id columns (primary key) and (foreign keys) as integer
   when /^id$/i then :integer
   when /_pk$/i then :integer
   when /_fk$/i then :integer
   else super
   end
   end
   def select(sql, name = nil)
   cursor = execute(sql, name)
   cols = cursor.get_col_names.map { |x| oracle_downcase(x) }
   rows = []

   while row = cursor.fetch
   hash = Hash.new

   cols.each_with_index do |col, i|
   hash[col] =
   case row[i]
   when OCI8::LOB
   name == ‘Writable Large Object’ ? row[i]: row[i].read
   when OraDate
   d = row[i]
   if emulate_dates && (d.hour == 0 && d.minute == 0 && d.second == 0)
   d.to_date
   else
   # see string_to_time; Time overflowing to DateTime, respecting the default timezone
   time_array = [d.year, d.month, d.day, d.hour, d.minute, d.second]
   begin
   Time.send(Base.default_timezone, *time_array)
   rescue
   zone_offset = if Base.default_timezone == :local then DateTime.now.offset else 0 end
   # Append zero calendar reform start to account for dates skipped by calendar reform
   DateTime.new(*time_array[0..5] << zone_offset << 0) rescue nil
   end
   end
   else row[i]
   end unless col == ‘raw_rnum_’
   # RSI: patch – convert to integer if column is ID or ends with _pk or _fk
   hash[col] = hash[col].to_i if (col =~ /^id$/i || col =~ /_pk$/i || col =~ /_fk/i) && hash[col]

   end

   rows << hash
   end

   rows
   ensure
   cursor.close if cursor
   end

   Comment by Simon — April 2, 2008 @ 1:40 pm | Reply

4. 

   oops. well this works find if you do a script/console session and trye ” y Table.find(:all)”
   keys don’t have the .0 on them. BUT if you look at it in scaffold like localhost:3000/tablename
   the .0′s are back

   just wondering in if you had any suggestions

   Comment by Simon — April 2, 2008 @ 1:43 pm | Reply

5. 

   It’s quite strange. Scaffold controller is doing the same Table.find(:all) and then for each record in view to_param method is called from named route helper method to get the parameter string.

   Try Table.find(:all).first.to_param – will it return integer or will it return .0 at the end?

   Comment by Raimonds Simanovskis — April 2, 2008 @ 2:32 pm | Reply

6. 

   Well apparently when you do the command “y Tablename.find(:all)” in the process of the ‘y’ command converting the data into yml format removes any .0 . And when i run the command without y it looks like this

   #<Job cus_uid_pk: #, cus_name: “HENRY JR TORRES”, svo_uid_pk: #, slo_uid_pk: #,

   ive also added this code into hopes of force integer handling
   im using rails 2.0.2 and oracle-adapter from http://svn.rubyonrails.org/rails/adapters/oracle/lib/active_record/connection_adapters/
   since the gem install is broken.
   I have also tried adding more code to force an integer relation

   select_all(table_cols, name).map do |row|
   limit, scale = row['limit'], row['scale']
   #patch map NUMBER , where column names ends with _pk or _fk to NUMBER(38)
   if (row['name'] =~ /_pk$/i || row['name'] =~ /_fk$/i )
   row['limit'] = 38
   end
   if limit || scale
   row['sql_type'] < 0 ? “,#{scale})” : “)”)
   end

   here is my whole oracle_adapter.rb

   require ‘active_record/connection_adapters/abstract_adapter’
   require ‘delegate’

   begin
   require_library_or_gem ‘oci8′ unless self.class.const_defined? :OCI8

   module ActiveRecord
   class Base
   def self.oracle_connection(config) #:nodoc:
   # Use OCI8AutoRecover instead of normal OCI8 driver.
   ConnectionAdapters::OracleAdapter.new OCI8AutoRecover.new(config), logger
   end

   # for backwards-compatibility
   def self.oci_connection(config) #:nodoc:
   config[:database] = config[:host]
   self.oracle_connection(config)
   end

   # After setting large objects to empty, select the OCI8::LOB
   # and write back the data.
   after_save :write_lobs
   def write_lobs #:nodoc:
   if connection.is_a?(ConnectionAdapters::OracleAdapter)
   connection.write_lobs(self.class.table_name, self.class, attributes)
   end
   end

   private :write_lobs
   end

   module ConnectionAdapters #:nodoc:
   class OracleColumn < Column #:nodoc:

   def type_cast(value)
   return guess_date_or_time(value) if type == :datetime && OracleAdapter.emulate_dates
   super
   end

   private
   def simplified_type(field_type)
   return :boolean if OracleAdapter.emulate_booleans && field_type == ‘NUMBER(1)’
   case field_type
   when /date|time/i then :datetime
   # RSI: treat columns which end with ‘date’ as ruby date columns
   when /date$/i then :date
   # RSI: removed ‘date’ from regex
   when /time/i then :datetime
   # RSI: treat id columns (primary key) and (foreign keys) as integer
   when /^id$/i then :integer
   when /_pk$/i then :integer
   when /_fk$/i then :integer
   else super
   end
   end

   def guess_date_or_time(value)
   (value.hour == 0 and value.min == 0 and value.sec == 0) ?
   Date.new(value.year, value.month, value.day) : value
   end
   end

   # This is an Oracle/OCI adapter for the ActiveRecord persistence
   # framework. It relies upon the OCI8 driver, which works with Oracle 8i
   # and above. Most recent development has been on Debian Linux against
   # a 10g database, ActiveRecord 1.12.1 and OCI8 0.1.13.
   # See: http://rubyforge.org/projects/ruby-oci8/
   #
   # 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.
   # * 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. If the column_name ends in _time it’s created as a Ruby Time.
   # Else if the hours/minutes/seconds are 0, I make it a Ruby Date. Else
   # it’s a Ruby Time. This is a bit nasty – but if you use Duck Typing
   # you’ll probably not care very much. In 9i and up it’s tempting to
   # map DATE to Date and TIMESTAMP to Time, but too many databases use
   # DATE for both. 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.
   # * Support for Oracle8 is limited by Rails’ use of ANSI join syntax, which
   # is supported in Oracle9i and later. You will need to use #finder_sql for
   # has_and_belongs_to_many associations to run against Oracle8.
   #
   # Required parameters:
   #
   # * :username
   # * :password
   # * :database
   class OracleAdapter “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” },
   :timestamp => { :name => “DATE” },
   :time => { :name => “DATE” },
   :date => { :name => “DATE” },
   :binary => { :name => “BLOB” },
   :boolean => { :name => “NUMBER”, :limit => 1 }
   }
   end

   def table_alias_length
   30
   end

   # QUOTING ==================================================
   #
   # see: abstract/quoting.rb

   # 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…
   def quote_column_name(name) #:nodoc:
   name.to_s =~ /[A-Z]/ ? “\”#{name}\”" : name
   end

   def quote_string(s) #:nodoc:
   s.gsub(/’/, “””)
   end

   def quote(value, column = nil) #:nodoc:
   if value && column && [:text, :binary].include?(column.type)
   %Q{empty_#{ column.sql_type.downcase rescue ‘blob’ }()}
   else
   super
   end
   end

   def quoted_true
   “1″
   end

   def quoted_false
   “0″
   end

   # CONNECTION MANAGEMENT ====================================
   #

   # Returns true if the connection is active.
   def active?
   # 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 OCIException
   false
   end

   # Reconnects to the database.
   def reconnect!
   @connection.reset!
   rescue OCIException => e
   @logger.warn “#{adapter_name} automatic reconnection failed: #{e.message}”
   end

   # Disconnects from the database.
   def disconnect!
   @connection.logoff rescue nil
   @connection.active = false
   end

   # DATABASE STATEMENTS ======================================
   #
   # see: abstract/database_statements.rb

   def execute(sql, name = nil) #:nodoc:
   log(sql, name) { @connection.exec sql }
   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?).
   def next_sequence_value(sequence_name)
   id = 0
   @connection.exec(“select #{sequence_name}.nextval id from dual”) { |r| id = r[0].to_i }
   id
   end

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

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

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

   def add_limit_offset!(sql, options) #:nodoc:
   offset = options[:offset] || 0

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

   # Returns true for Oracle adapter (since Oracle requires primary key
   # values to be pre-fetched before insert). See also #next_sequence_value.
   def prefetch_primary_key?(table_name = nil)
   true
   end

   def default_sequence_name(table, column) #:nodoc:
   “#{table}_seq”
   end

   # Inserts the given fixture into the table. Overridden to properly handle lobs.
   def insert_fixture(fixture, table_name)
   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

   # Writes LOB values from attributes, as indicated by the LOB columns of klass.
   def write_lobs(table_name, klass, attributes)
   id = quote(attributes[klass.primary_key])
   klass.columns.select { |col| col.sql_type =~ /LOB$/i }.each do |col|
   value = attributes[col.name]
   value = value.to_yaml if col.text? && klass.serialized_attributes[col.name]
   next if value.nil? || (value == ”)
   lob = select_one(“SELECT #{col.name} FROM #{table_name} WHERE #{klass.primary_key} = #{id}”,
   ‘Writable Large Object’)[col.name]
   lob.write value
   end
   end

   # SCHEMA STATEMENTS ========================================
   #
   # see: abstract/schema_statements.rb

   def current_database #:nodoc:
   select_one(“select sys_context(‘userenv’,'db_name’) db from dual”)["db"]
   end

   def tables(name = nil) #:nodoc:
   select_all(“select lower(table_name) from user_tables”).inject([]) do | tabs, t |
   tabs << t.to_a.first.last
   end
   end

   def indexes(table_name, name = nil) #:nodoc:
   result = select_all(<<-SQL, name)
   SELECT lower(i.index_name) as index_name, i.uniqueness, lower(c.column_name) as column_name
   FROM user_indexes i, user_ind_columns c
   WHERE i.table_name = ‘#{table_name.to_s.upcase}’
   AND c.index_name = i.index_name
   AND i.index_name NOT IN (SELECT uc.index_name FROM user_constraints uc WHERE uc.constraint_type = ‘P’)
   ORDER BY i.index_name, c.column_position
   SQL

   current_index = nil
   indexes = []

   result.each do |row|
   if current_index != row['index_name']
   indexes << IndexDefinition.new(table_name, row['index_name'], row['uniqueness'] == “UNIQUE”, [])
   current_index = row['index_name']
   end

   indexes.last.columns << row['column_name']
   end

   indexes
   end

   def columns(table_name, name = nil) #:nodoc:

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

   table_cols = <<-SQL
   select column_name as name, data_type as sql_type, data_default, nullable,
   decode(data_type, ‘NUMBER’, data_precision,
   ‘FLOAT’, data_precision,
   ‘VARCHAR2′, data_length,
   ‘CHAR’, data_length,
   null) as limit,
   decode(data_type, ‘NUMBER’, data_scale, null) as scale
   from all_tab_columns
   where owner = ‘#{owner}’
   and table_name = ‘#{table_name}’
   order by column_id
   SQL

   select_all(table_cols, name).map do |row|
   limit, scale = row['limit'], row['scale']
   if (row['name'] =~ /_pk$/i || row['name'] =~ /_fk$/i )
   row['limit'] = 38
   end
   if limit || scale
   row['sql_type'] < 0 ? “,#{scale})” : “)”)
   end
   # clean up odd default spacing from Oracle
   if row['data_default']
   row['data_default'].sub!(/^(.*?)\s*$/, ‘\1′)
   row['data_default'].sub!(/^’(.*)’$/, ‘\1′)
   row['data_default'] = nil if row['data_default'] =~ /^(null|empty_[bc]lob\(\))$/i
   end

   OracleColumn.new(oracle_downcase(row['name']),
   row['data_default'],
   row['sql_type'],
   row['nullable'] == ‘Y’)

   end
   end

   def create_table(name, options = {}) #:nodoc:
   super(name, options)
   seq_name = options[:sequence_name] || “#{name}_seq”
   execute “CREATE SEQUENCE #{seq_name} START WITH 10000″ unless options[:id] == false
   end

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

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

   def remove_index(table_name, options = {}) #:nodoc:
   execute “DROP INDEX #{index_name(table_name, options)}”
   end

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

   def change_column(table_name, column_name, type, options = {}) #:nodoc:
   change_column_sql = “ALTER TABLE #{table_name} MODIFY #{quote_column_name(column_name)} #{type_to_sql(type, options[:limit], options[:precision], options[:scale])}”
   add_column_options!(change_column_sql, options)
   execute(change_column_sql)
   end

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

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

   # Find a table’s primary key and sequence.
   # *Note*: Only primary key is implemented – sequence will be nil.
   def pk_and_sequence_for(table_name)
   (owner, table_name) = @connection.describe(table_name)

   pks = select_values(<<-SQL, ‘Primary Key’)
   select cc.column_name
   from all_constraints c, all_cons_columns cc
   where c.owner = ‘#{owner}’
   and c.table_name = ‘#{table_name}’
   and c.constraint_type = ‘P’
   and cc.owner = c.owner
   and cc.constraint_name = c.constraint_name
   SQL

   # only support single column keys
   pks.size == 1 ? [oracle_downcase(pks.first), nil] : nil
   end

   def structure_dump #:nodoc:
   s = select_all(“select sequence_name from user_sequences”).inject(“”) do |structure, seq|
   structure << “create sequence #{seq.to_a.first.last};\n\n”
   end

   select_all(“select table_name from user_tables”).inject(s) do |structure, table|
   ddl = “create table #{table.to_a.first.last} (\n ”
   cols = select_all(%Q{
   select column_name, data_type, data_length, data_precision, data_scale, data_default, nullable
   from user_tab_columns
   where table_name = ‘#{table.to_a.first.last}’
   order by column_id
   }).map do |row|
   col = “#{row['column_name'].downcase} #{row['data_type'].downcase}”
   if row['data_type'] ==’NUMBER’ and !row['data_precision'].nil?
   col << “(#{row['data_precision'].to_i}”
   col << “,#{row['data_scale'].to_i}” if !row['data_scale'].nil?
   col << ‘)’
   elsif row['data_type'].include?(‘CHAR’)
   col << “(#{row['data_length'].to_i})”
   end
   col << ” default #{row['data_default']}” if !row['data_default'].nil?
   col << ‘ not null’ if row['nullable'] == ‘N’
   col
   end
   ddl << cols.join(“,\n “)
   ddl << “);\n\n”
   structure << ddl
   end
   end

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

   select_all(“select table_name from user_tables”).inject(s) do |drop, table|
   drop << “drop table #{table.to_a.first.last} cascade constraints;\n\n”
   end
   end

   def add_column_options!(sql, options) #:nodoc:
   # handle case of defaults for CLOB columns, which would otherwise get “quoted” incorrectly
   if options_include_default?(options) && (column = options[:column]) && column.type == :text
   sql << ” DEFAULT #{quote(options.delete(:default))}”
   end
   super
   end

   # 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”)
   def distinct(columns, order_by)
   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

   # ORDER BY clause for the passed order option.
   #
   # Uses column aliases as defined by #distinct.
   def add_order_by_for_association_limiting!(sql, options)
   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

   private

   def select(sql, name = nil)
   cursor = execute(sql, name)
   cols = cursor.get_col_names.map { |x| oracle_downcase(x) }
   rows = []

   while row = cursor.fetch
   hash = Hash.new

   cols.each_with_index do |col, i|
   hash[col] =
   case row[i]
   when OCI8::LOB
   name == ‘Writable Large Object’ ? row[i]: row[i].read
   when OraDate
   d = row[i]
   if emulate_dates && (d.hour == 0 && d.minute == 0 && d.second == 0)
   d.to_date
   else
   # see string_to_time; Time overflowing to DateTime, respecting the default timezone
   time_array = [d.year, d.month, d.day, d.hour, d.minute, d.second]
   begin
   Time.send(Base.default_timezone, *time_array)
   rescue
   zone_offset = if Base.default_timezone == :local then DateTime.now.offset else 0 end
   # Append zero calendar reform start to account for dates skipped by calendar reform
   DateTime.new(*time_array[0..5] << zone_offset << 0) rescue nil
   end
   end
   else row[i]
   end unless col == ‘raw_rnum_’
   # RSI: patch – convert to integer if column is ID or ends with _pk or _fk
   if (col !~ /^session_id$/i) && (col =~ /^id$/i || col =~ /_pk$/i || col =~ /_fk$/i) && hash[col]
   hash[col] = hash[col].to_i
   end
   end

   rows << hash
   end

   rows
   ensure
   cursor.close if cursor
   end

   # Oracle column names by default are case-insensitive, but treated as upcase;
   # for neatness, we’ll downcase within Rails. EXCEPT that folks CAN quote
   # their column names when creating Oracle tables, which makes then case-sensitive.
   # I don’t know anybody who does this, but we’ll handle the theoretical case of a
   # camelCase column name. I imagine other dbs handle this different, since there’s a
   # unit test that’s currently failing test_oci.
   def oracle_downcase(column_name)
   column_name =~ /[a-z]/ ? column_name : column_name.downcase
   end

   end
   end
   end

   class OCI8 #:nodoc:

   # This OCI8 patch may not longer be required with the upcoming
   # release of version 0.2.
   class Cursor #:nodoc:
   alias :define_a_column_pre_ar :define_a_column
   def define_a_column(i)
   case do_ocicall(@ctx) { @parms[i - 1].attrGet(OCI_ATTR_DATA_TYPE) }
   when 8; @stmt.defineByPos(i, String, 65535) # Read LONG values
   when 187; @stmt.defineByPos(i, OraDate) # Read TIMESTAMP values
   when 108
   if @parms[i - 1].attrGet(OCI_ATTR_TYPE_NAME) == ‘XMLTYPE’
   @stmt.defineByPos(i, String, 65535)
   else
   raise ‘unsupported datatype’
   end
   else define_a_column_pre_ar i
   end
   end
   end

   # missing constant from oci8 = ’0.1.14′
   @desc.describeAny(@svc, name.to_s, OCI_PTYPE_UNK) rescue raise %Q{“DESC #{name}” failed; does it exist?}
   info = @desc.attrGet(OCI_ATTR_PARAM)

   case info.attrGet(OCI_ATTR_PTYPE)
   when OCI_PTYPE_TABLE, OCI_PTYPE_VIEW
   owner = info.attrGet(OCI_ATTR_OBJ_SCHEMA)
   table_name = info.attrGet(OCI_ATTR_OBJ_NAME)
   [owner, table_name]
   when OCI_PTYPE_SYN
   schema = info.attrGet(OCI_ATTR_SCHEMA_NAME)
   name = info.attrGet(OCI_ATTR_NAME)
   describe(schema + ‘.’ + name)
   else raise %Q{“DESC #{name}” failed; not a table or view.}
   end
   end

   end

   # The OracleConnectionFactory factors out the code necessary to connect and
   # configure an Oracle/OCI connection.
   class OracleConnectionFactory #:nodoc:
   def new_connection(username, password, database, async, prefetch_rows, cursor_sharing)
   conn = OCI8.new username, password, database
   conn.exec %q{alter session set nls_date_format = ‘YYYY-MM-DD HH24:MI:SS’}
   conn.exec %q{alter session set nls_timestamp_format = ‘YYYY-MM-DD HH24:MI:SS’} rescue nil
   conn.autocommit = true
   conn.non_blocking = true if async
   conn.prefetch_rows = prefetch_rows
   conn.exec “alter session set cursor_sharing = #{cursor_sharing}” rescue nil
   conn
   end
   end

   # The OCI8AutoRecover class enhances the OCI8 driver with auto-recover and
   # reset functionality. If a call to #exec fails, and autocommit is turned on
   # (ie., we’re not in the middle of a longer transaction), it will
   # automatically reconnect and try again. If autocommit is turned off,
   # this would be dangerous (as the earlier part of the implied transaction
   # may have failed silently if the connection died) — so instead the
   # connection is marked as dead, to be reconnected on it’s next use.
   class OCI8AutoRecover < DelegateClass(OCI8) #:nodoc:
   attr_accessor :active
   alias :active? :active

   cattr_accessor :auto_retry
   class < e
   raise unless LOST_CONNECTION_ERROR_CODES.include?(e.code)
   @active = false
   raise unless should_retry
   should_retry = false
   reset! rescue nil
   retry
   end
   end

   end

   rescue LoadError
   # OCI8 driver is unavailable.
   module ActiveRecord # :nodoc:
   class Base
   @@oracle_error_message = “Oracle/OCI libraries could not be loaded: #{$!.to_s}”
   def self.oracle_connection(config) # :nodoc:
   # Set up a reasonable error message
   raise LoadError, @@oracle_error_message
   end
   def self.oci_connection(config) # :nodoc:
   # Set up a reasonable error message
   raise LoadError, @@oracle_error_message
   end
   end
   end
   end

   Comment by Simon — April 2, 2008 @ 3:50 pm | Reply

7. 

   Also my view is create from a select statement that maps the output of pl/sql function

   PS Sorry for the HUGE cope paste

   Comment by Simon — April 2, 2008 @ 4:05 pm | Reply

8. 

   Ok found a solution i have no idea why it work but it works.
   all i had to do was add

   def simplified_type(field_type)
   return :boolean if OracleAdapter.emulate_booleans && field_type == ‘NUMBER(1)’
   case field_type
   when /date|time/i then :datetime
   # RSI: treat columns which end with ‘date’ as ruby date columns
   when /date$/i then :date
   # RSI: removed ‘date’ from regex
   when /time/i then :datetime
   # RSI: treat id columns (primary key) and (foreign keys) as integer
   when /^id$/i then :integer
   when /_pk$/i then :integer
   when /_fk$/i then :integer
   else super
   end
   x=5 #WTF! Why is this here? Oh wait some how I have to have a statement here any statement or it wont work?
   end

   I discovered this by chance because i removed :float all together and it still didn’t work but with that x=5 or puts “test” it some how works

   Comment by Simon — April 2, 2008 @ 5:32 pm | Reply

9. 

   Thanks for putting that description of how ruby sees the different Numbers! Luckily I can have the schema changed, or I’d be digging deeper into the patch. Saved me a lot of headache!

   Comment by Duke — May 1, 2008 @ 7:51 pm | Reply

10. [...] ActiveRecord Oracle enhanced adapter Filed under: oracle, rails, ruby — Raimonds Simanovskis @ 12:05 am In all Ruby on Rails on Oracle projects where I am using original ActiveRecord Oracle adapter I always create some “monkey patches” of Oracle adapter to support my needs. I have written about several of these patches in this blog (see 1, 2, 3). [...]

    Pingback by ActiveRecord Oracle enhanced adapter « ray_apps_blog — May 13, 2008 @ 12:06 am | Reply

11. 

    Thank you SO much for the NLS_LANG tip. It just saved my day!

    Comment by labria — December 16, 2008 @ 5:17 am | Reply

RSS feed for comments on this post. TrackBack URI

• About Ruby, Oracle, Mac and others…

• 

  

  

  

• Search for:

• Categories

  • apache (1)
  • business intelligence (1)
  • datamapper (1)
  • e-business suite (1)
  • java (2)
  • jruby (6)
  • mac (7)
  • oracle (40)
  • oracle-enhanced (12)
  • pl/sql (10)
  • rails (26)
  • ruby (39)
  • screencast (2)
  • testing (1)
  • windows (1)

• Recent Posts

  • Oracle enhanced adapter 1.3.0 is Rails 3 compatible
  • Please vote for my Ruby session proposals at Oracle OpenWorld
  • ruby-plsql 0.4.2 – better support for object types and types in packages
  • ActiveRecord Oracle enhanced adapter version 1.2.4
  • Screencasts of Oracle PL/SQL unit testing with Ruby

• Recent Comments

  	wdz on How to install Oracle Database…
  	JulesLt on How to setup Ruby and Oracle I…
  	JulesLt on ruby-plsql 0.4.2 – bette…
  	JulesLt on Some positive drag-and-drop…
  	JulesLt on How to install Oracle Database…