Last Updated: February 25, 2016

Use a default Schema with the activerecord-oracle_enhanced-adapter gem

After a long time searching for a proper solution to be able to use a different schema on an Oracle database, I found that the code below works the best. You can put this in a file in config/initializers, update your database.yml and that is all.

What this does is add some functionality to the end of the oracle_enhanced #new_connection method, and send the 'alter session set current_schema = SCHEMA' query if a schema is given and the connection is made.

# The {OracleEnhancedOCIFactorySchemaExtensions} module adds Oracle Schema support
# to the Oracle Enhanced active record adapter. In the database.yml configuration file
# you can configure an optional default schema to be used for the connection.
#
# The configuration is as follows:
#
# production:
# adapter: oracle_enhanced
# host: localhost
# port: 1521
# database: /DATABASE_NAME
# schema: SCHEMA_NAME
# username: USERNAME
# password: PASSWORD
#
module OracleEnhancedOCIFactorySchemaExtensions
 def self.included(base)
 base.extend ClassMethods
 base.class_eval do
 class << self
 alias_method_chain :new_connection, :schema_extensions
 end
 end
 end

 module ClassMethods
 def new_connection_with_schema_extensions(config)
 schema = config[:schema] && config[:schema].to_s

 conn = new_connection_without_schema_extensions(config)

 conn.exec "alter session set current_schema = #{schema}" unless schema.blank?

 conn
 end
 end
end

ActiveRecord::ConnectionAdapters::OracleEnhancedOCIFactory.send :include, OracleEnhancedOCIFactorySchemaExtensions