changes for Oracle support - OracleCompiler and corresponding tests with Oracle syntax

36 files changed, 892 insertions, 141 deletions

diff --git a/Rakefile b/Rakefile
index fba44e620f..a1384196a2 100644
--- a/Rakefile
+++ b/Rakefile
@@ -20,13 +20,13 @@ else
   end
 
   namespace :spec do
-    for adapter in %w[mysql sqlite3 postgresql]
+    for adapter in %w[mysql sqlite3 postgresql oracle]
       desc "Run specs with the #{adapter} database adapter"
       Spec::Rake::SpecTask.new(adapter) do |t|
         t.spec_opts = ['--options', "\"#{File.dirname(__FILE__)}/spec/spec.opts\""]
         t.libs << "#{File.dirname(__FILE__)}/vendor/rails/activerecord/lib"
         t.libs << "#{File.dirname(__FILE__)}/spec"
-        t.warning = true
+        # t.warning = true
         t.spec_files =
           ["spec/connections/#{adapter}_connection.rb"] +
           ["spec/schemas/#{adapter}_schema.rb"] +
diff --git a/arel.gemspec b/arel.gemspec
index b5267cf333..24a73d78cd 100644
--- a/arel.gemspec
+++ b/arel.gemspec
@@ -6,7 +6,7 @@ Gem::Specification.new do |s|
 
   s.required_rubygems_version = Gem::Requirement.new(">= 0") if s.respond_to? :required_rubygems_version=
   s.authors = ["Bryan Helmkamp", "Nick Kallen", "Emilio Tagua"]
-  s.date = %q{2010-02-05}
+  s.date = %q{2010-02-13}
   s.description = %q{Arel is a Relational Algebra for Ruby. It 1) simplifies the generation complex
 of SQL queries and it 2) adapts to various RDBMS systems. It is intended to be
 a framework framework; that is, you can build your own ORM with it, focusing on
@@ -146,6 +146,7 @@ and query generation.}
     "spec/arel/engines/sql/unit/relations/update_spec.rb",
     "spec/arel/engines/sql/unit/relations/where_spec.rb",
     "spec/connections/mysql_connection.rb",
+    "spec/connections/oracle_connection.rb",
     "spec/connections/postgresql_connection.rb",
     "spec/connections/sqlite3_connection.rb",
     "spec/doubles/hash.rb",
@@ -153,6 +154,7 @@ and query generation.}
     "spec/matchers/disambiguate_attributes.rb",
     "spec/matchers/hash_the_same_as.rb",
     "spec/schemas/mysql_schema.rb",
+    "spec/schemas/oracle_schema.rb",
     "spec/schemas/postgresql_schema.rb",
     "spec/schemas/sqlite3_schema.rb",
     "spec/spec.opts",
@@ -221,6 +223,7 @@ and query generation.}
     "spec/arel/engines/sql/unit/relations/update_spec.rb",
     "spec/arel/engines/sql/unit/relations/where_spec.rb",
     "spec/connections/mysql_connection.rb",
+    "spec/connections/oracle_connection.rb",
     "spec/connections/postgresql_connection.rb",
     "spec/connections/sqlite3_connection.rb",
     "spec/doubles/hash.rb",
@@ -228,6 +231,7 @@ and query generation.}
     "spec/matchers/disambiguate_attributes.rb",
     "spec/matchers/hash_the_same_as.rb",
     "spec/schemas/mysql_schema.rb",
+    "spec/schemas/oracle_schema.rb",
     "spec/schemas/postgresql_schema.rb",
     "spec/schemas/sqlite3_schema.rb",
     "spec/spec_helper.rb"
diff --git a/lib/arel/engines/sql/compilers/oracle_compiler.rb b/lib/arel/engines/sql/compilers/oracle_compiler.rb
new file mode 100644
index 0000000000..1f6b82dd2f
--- /dev/null
+++ b/lib/arel/engines/sql/compilers/oracle_compiler.rb
@@ -0,0 +1,101 @@
+module Arel
+  module SqlCompiler
+    class OracleCompiler < GenericCompiler
+
+      def select_sql
+        where_clauses_array = where_clauses
+        if limit_or_offset = !taken.blank? || !skipped.blank?
+          # if need to select first records without ORDER BY and GROUP BY
+          # then can use simple ROWNUM in WHERE clause
+          if skipped.blank? && groupings.blank? && orders.blank?
+            where_clauses_array << "ROWNUM <= #{taken}" if !taken.blank? && skipped.blank? && groupings.blank? && orders.blank?
+            limit_or_offset = false
+          end
+        end
+
+        # when limit or offset subquery is used then cannot use FOR UPDATE directly
+        # and need to construct separate subquery for primary key
+        if use_subquery_for_lock = limit_or_offset && !locked.blank?
+          primary_key = begin
+            engine.quote_column_name(table.name.classify.constantize.primary_key)
+          rescue NameError
+            engine.quote_column_name("id")
+          end
+          select_attributes_string = primary_key
+        else
+          select_attributes_string = select_clauses.join(', ')
+        end
+
+        # OracleEnhanced adapter workaround when ORDER BY is used with columns not
+        # present in DISTINCT columns list
+        order_clauses_array = if select_attributes_string =~ /DISTINCT.*FIRST_VALUE/ && !orders.blank?
+          order = order_clauses.join(', ').split(',').map { |s| s.strip }.reject(&:blank?)
+          order = order.zip((0...order.size).to_a).map { |s,i| "alias_#{i}__ #{'DESC' if s =~ /\bdesc$/i}" }
+        else
+          order_clauses
+        end
+
+        query = build_query \
+          "SELECT     #{select_attributes_string}",
+          "FROM       #{from_clauses}",
+          (joins(self)                                   unless joins(self).blank? ),
+          ("WHERE     #{where_clauses_array.join(" AND ")}"    unless where_clauses_array.blank?      ),
+          ("GROUP BY  #{group_clauses.join(', ')}"       unless groupings.blank?   ),
+          ("HAVING    #{having_clauses.join(', ')}"      unless havings.blank?     ),
+          ("ORDER BY  #{order_clauses_array.join(', ')}" unless order_clauses_array.blank? )
+
+        # Use existing method from oracle_enhanced adapter to implement limit and offset using subqueries
+        engine.add_limit_offset!(query, :limit => taken, :offset => skipped) if limit_or_offset
+
+        if use_subquery_for_lock
+          build_query \
+            "SELECT     #{select_clauses.join(', ')}",
+            "FROM       #{from_clauses}",
+            "WHERE      #{primary_key} IN (#{query})",
+            "#{locked}"
+        elsif !locked.blank?
+          build_query query, "#{locked}"
+        else
+          query
+        end
+      end
+
+      def delete_sql
+        where_clauses_array = wheres.collect(&:to_sql)
+        where_clauses_array << "ROWNUM <= #{taken}" unless taken.blank?
+        build_query \
+          "DELETE",
+          "FROM #{table_sql}",
+          ("WHERE #{where_clauses_array.join(' AND ')}" unless where_clauses_array.blank? )
+      end
+
+    protected
+
+      def build_update_conditions_sql
+        conditions = ""
+        where_clauses_array = wheres.collect(&:to_sql)
+        # if need to select first records without ORDER BY
+        # then can use simple ROWNUM in WHERE clause
+        if !taken.blank? && orders.blank?
+          where_clauses_array << "ROWNUM <= #{taken}"
+        end
+        conditions << " WHERE #{where_clauses_array.join(' AND ')}" unless where_clauses_array.blank?
+        unless taken.blank?
+          conditions = limited_update_conditions(conditions, taken)
+        end
+        conditions
+      end
+
+      def limited_update_conditions(conditions, taken)
+        # need to add ORDER BY only if just taken ones should be updated
+        conditions << " ORDER BY #{order_clauses.join(', ')}" unless orders.blank?
+        quoted_primary_key = engine.quote_column_name(primary_key)
+        subquery = "SELECT #{quoted_primary_key} FROM #{engine.connection.quote_table_name table.name} #{conditions}"
+        # Use existing method from oracle_enhanced adapter to get taken records when ORDER BY is used
+        engine.add_limit_offset!(subquery, :limit => taken) unless orders.blank?
+        "WHERE #{quoted_primary_key} IN (#{subquery})"
+      end
+
+    end
+  end
+end
diff --git a/lib/arel/engines/sql/engine.rb b/lib/arel/engines/sql/engine.rb
index f0991f0a0f..054de21a70 100644
--- a/lib/arel/engines/sql/engine.rb
+++ b/lib/arel/engines/sql/engine.rb
@@ -11,7 +11,13 @@ module Arel
       end
 
       def adapter_name
-        @adapter_name ||= connection.adapter_name
+        @adapter_name ||= case (name = connection.adapter_name)
+        # map OracleEnanced adapter to Oracle
+        when /Oracle/
+          'Oracle'
+        else
+          name
+        end
       end
 
       def method_missing(method, *args, &block)
diff --git a/lib/arel/engines/sql/formatters.rb b/lib/arel/engines/sql/formatters.rb
index 8da362ef3b..892f0c29ee 100644
--- a/lib/arel/engines/sql/formatters.rb
+++ b/lib/arel/engines/sql/formatters.rb
@@ -93,7 +93,7 @@ module Arel
 
     class TableReference < Formatter
       def select(select_sql, table)
-        "(#{select_sql}) AS #{quote_table_name(name_for(table))}"
+        "(#{select_sql})#{as_keyword}#{quote_table_name(name_for(table))}"
       end
 
       def table(table)
@@ -101,9 +101,16 @@ module Arel
           table.name
         else
           quote_table_name(table.name) +
-            (table.name != name_for(table) ? " AS " + quote_table_name(name_for(table)) : '')
+            (table.name != name_for(table) ? as_keyword + quote_table_name(name_for(table)) : '')
         end
       end
+
+      private
+
+      def as_keyword
+        # AS keyword should not be used before table alias in Oracle
+        as_keyword = engine.adapter_name == "Oracle" ? " " : " AS "
+      end
     end
 
     class Attribute < WhereCondition
diff --git a/lib/arel/engines/sql/relations/compiler.rb b/lib/arel/engines/sql/relations/compiler.rb
index 597ed88683..fc8d484276 100644
--- a/lib/arel/engines/sql/relations/compiler.rb
+++ b/lib/arel/engines/sql/relations/compiler.rb
@@ -21,20 +21,55 @@ module Arel
           ("#{locked}"                                   unless locked.blank?)
       end
 
-      def limited_update_conditions(conditions, taken)
-        conditions << " LIMIT #{taken}"
-        quoted_primary_key = engine.quote_table_name(primary_key)
-        "WHERE #{quoted_primary_key} IN (SELECT #{quoted_primary_key} FROM #{engine.connection.quote_table_name table.name} #{conditions})"
+      def delete_sql
+        build_query \
+          "DELETE",
+          "FROM #{table_sql}",
+          ("WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank? ),
+          (add_limit_on_delete(taken)                        unless taken.blank?  )
       end
 
       def add_limit_on_delete(taken)
         "LIMIT #{taken}"
       end
 
+      def insert_sql(include_returning = true)
+        insertion_attributes_values_sql = if record.is_a?(Value)
+          record.value
+        else
+          attributes = record.keys.sort_by do |attribute|
+            attribute.name.to_s
+          end
+
+          first = attributes.collect do |key|
+            engine.quote_column_name(key.name)
+          end.join(', ')
+
+          second = attributes.collect do |key|
+            key.format(record[key])
+          end.join(', ')
+
+          build_query "(#{first})", "VALUES (#{second})"
+        end
+
+        build_query \
+          "INSERT",
+          "INTO #{table_sql}",
+          insertion_attributes_values_sql,
+          ("RETURNING #{engine.quote_column_name(primary_key)}" if include_returning && compiler.supports_insert_with_returning?)
+      end
+
       def supports_insert_with_returning?
         false
       end
 
+      def update_sql
+        build_query \
+          "UPDATE #{table_sql} SET",
+          assignment_sql,
+          build_update_conditions_sql
+      end
+
     protected
       def method_missing(method, *args, &block)
         relation.send(method, *args, &block)
@@ -44,6 +79,39 @@ module Arel
         parts.compact.join(" ")
       end
 
+      def assignment_sql
+        if assignments.respond_to?(:collect)
+          attributes = assignments.keys.sort_by do |attribute|
+            attribute.name.to_s
+          end
+
+          attributes.map do |attribute|
+            value = assignments[attribute]
+            "#{engine.quote_column_name(attribute.name)} = #{attribute.format(value)}"
+          end.join(", ")
+        else
+          assignments.value
+        end
+      end
+
+      def build_update_conditions_sql
+        conditions = ""
+        conditions << " WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank?
+        conditions << " ORDER BY #{order_clauses.join(', ')}" unless orders.blank?
+
+        unless taken.blank?
+          conditions = limited_update_conditions(conditions, taken)
+        end
+
+        conditions
+      end
+
+      def limited_update_conditions(conditions, taken)
+        conditions << " LIMIT #{taken}"
+        quoted_primary_key = engine.quote_column_name(primary_key)
+        "WHERE #{quoted_primary_key} IN (SELECT #{quoted_primary_key} FROM #{engine.connection.quote_table_name table.name} #{conditions})"
+      end
+
     end
 
   end
diff --git a/lib/arel/engines/sql/relations/writes.rb b/lib/arel/engines/sql/relations/writes.rb
index 54eaee9ead..4ed817f85d 100644
--- a/lib/arel/engines/sql/relations/writes.rb
+++ b/lib/arel/engines/sql/relations/writes.rb
@@ -1,77 +1,19 @@
 module Arel
   class Deletion < Compound
     def to_sql
-      build_query \
-        "DELETE",
-        "FROM #{table_sql}",
-        ("WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank? ),
-        (compiler.add_limit_on_delete(taken)               unless taken.blank?  )
+      compiler.delete_sql
     end
   end
 
   class Insert < Compound
     def to_sql(include_returning = true)
-      insertion_attributes_values_sql = if record.is_a?(Value)
-        record.value
-      else
-        attributes = record.keys.sort_by do |attribute|
-          attribute.name.to_s
-        end
-
-        first = attributes.collect do |key|
-          engine.quote_column_name(key.name)
-        end.join(', ')
-
-        second = attributes.collect do |key|
-          key.format(record[key])
-        end.join(', ')
-
-        build_query "(#{first})", "VALUES (#{second})"
-      end
-
-      build_query \
-        "INSERT",
-        "INTO #{table_sql}",
-        insertion_attributes_values_sql,
-        ("RETURNING #{engine.quote_column_name(primary_key)}" if include_returning && compiler.supports_insert_with_returning?)
+      compiler.insert_sql(include_returning)
     end
   end
 
   class Update < Compound
     def to_sql
-      build_query \
-        "UPDATE #{table_sql} SET",
-        assignment_sql,
-        build_update_conditions_sql
-    end
-
-  protected
-
-    def assignment_sql
-      if assignments.respond_to?(:collect)
-        attributes = assignments.keys.sort_by do |attribute|
-          attribute.name.to_s
-        end
-
-        attributes.map do |attribute|
-          value = assignments[attribute]
-          "#{engine.quote_column_name(attribute.name)} = #{attribute.format(value)}"
-        end.join(", ")
-      else
-        assignments.value
-      end
-    end
-
-    def build_update_conditions_sql
-      conditions = ""
-      conditions << " WHERE #{wheres.collect(&:to_sql).join(' AND ')}" unless wheres.blank?
-      conditions << " ORDER BY #{order_clauses.join(', ')}" unless orders.blank?
-
-      unless taken.blank?
-        conditions = compiler.limited_update_conditions(conditions,taken)
-      end
-
-      conditions
+      compiler.update_sql
     end
   end
 end
diff --git a/spec/arel/engines/memory/integration/joins/cross_engine_spec.rb b/spec/arel/engines/memory/integration/joins/cross_engine_spec.rb
index db754d9cec..0dfcff1ee8 100644
--- a/spec/arel/engines/memory/integration/joins/cross_engine_spec.rb
+++ b/spec/arel/engines/memory/integration/joins/cross_engine_spec.rb
@@ -12,6 +12,11 @@ module Arel
       @photos.delete
       @photos.insert(@photos[:id] => 1, @photos[:user_id] => 1, @photos[:camera_id] => 6)
       @photos.insert(@photos[:id] => 2, @photos[:user_id] => 2, @photos[:camera_id] => 42)
+      # Oracle adapter returns database integers as Ruby integers and not strings
+      @adapter_returns_integer = false
+      adapter_is :oracle do
+        @adapter_returns_integer = true
+      end
     end
 
     describe 'when the in memory relation is on the left' do
@@ -22,8 +27,8 @@ module Arel
           .project(@users[:name], @photos[:camera_id]) \
         .let do |relation|
           relation.call.should == [
-            Row.new(relation, ['bryan', '6']),
-            Row.new(relation, ['emilio', '42'])
+            Row.new(relation, ['bryan', @adapter_returns_integer ? 6 : '6']),
+            Row.new(relation, ['emilio', @adapter_returns_integer ? 42 : '42'])
           ]
         end
       end
@@ -37,8 +42,8 @@ module Arel
           .project(@users[:name], @photos[:camera_id]) \
         .let do |relation|
           relation.call.should == [
-            Row.new(relation, ['bryan', '6']),
-            Row.new(relation, ['emilio', '42'])
+            Row.new(relation, ['bryan', @adapter_returns_integer ? 6 : '6']),
+            Row.new(relation, ['emilio', @adapter_returns_integer ? 42 : '42'])
           ]
         end
       end
diff --git a/spec/arel/engines/sql/integration/joins/with_adjacency_spec.rb b/spec/arel/engines/sql/integration/joins/with_adjacency_spec.rb
index 37afb1a8f2..47d37415ae 100644
--- a/spec/arel/engines/sql/integration/joins/with_adjacency_spec.rb
+++ b/spec/arel/engines/sql/integration/joins/with_adjacency_spec.rb
@@ -22,7 +22,16 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME"
+              FROM "USERS"
+                INNER JOIN "USERS" "USERS_2"
+                  ON "USERS"."ID" = "USERS_2"."ID"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name"
               FROM "users"
@@ -48,7 +57,16 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME"
+                FROM "USERS"
+                  INNER JOIN "USERS" "USERS_2"
+                    ON "USERS"."ID" = "USERS_2"."ID" AND "USERS_2"."ID" = 1
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name"
                 FROM "users"
@@ -76,7 +94,16 @@ module Arel
                 })
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{
+                  SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME"
+                  FROM "USERS"
+                  INNER JOIN "USERS" "USERS_2"
+                    ON "USERS_2"."ID" = "USERS"."ID" AND "USERS_2"."ID" = 1
+                })
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{
                   SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name"
                   FROM "users"
@@ -113,7 +140,18 @@ module Arel
                 })
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{
+                  SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME", "USERS_3"."ID", "USERS_3"."NAME"
+                  FROM "USERS"
+                    INNER JOIN "USERS" "USERS_2"
+                      ON "USERS"."ID" = "USERS_2"."ID"
+                    INNER JOIN "USERS" "USERS_3"
+                      ON "USERS_2"."ID" = "USERS_3"."ID"
+                })
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{
                   SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name", "users_3"."id", "users_3"."name"
                   FROM "users"
@@ -144,7 +182,18 @@ module Arel
                 })
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{
+                  SELECT "USERS"."ID", "USERS"."NAME", "USERS_2"."ID", "USERS_2"."NAME", "USERS_3"."ID", "USERS_3"."NAME"
+                  FROM "USERS"
+                    INNER JOIN "USERS" "USERS_2"
+                      ON "USERS"."ID" = "USERS_2"."ID"
+                    INNER JOIN "USERS" "USERS_3"
+                      ON "USERS_2"."ID" = "USERS_3"."ID"
+                })
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{
                   SELECT "users"."id", "users"."name", "users_2"."id", "users_2"."name", "users_3"."id", "users_3"."name"
                   FROM "users"
diff --git a/spec/arel/engines/sql/integration/joins/with_aggregations_spec.rb b/spec/arel/engines/sql/integration/joins/with_aggregations_spec.rb
index 5ed530508a..1ed056ca59 100644
--- a/spec/arel/engines/sql/integration/joins/with_aggregations_spec.rb
+++ b/spec/arel/engines/sql/integration/joins/with_aggregations_spec.rb
@@ -29,7 +29,16 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS_EXTERNAL"."ID", "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CAMERA_ID"
+              FROM "USERS"
+              INNER JOIN (SELECT "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID" FROM "PHOTOS" WHERE ROWNUM <= 3) "PHOTOS_EXTERNAL"
+                ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name", "photos_external"."id", "photos_external"."user_id", "photos_external"."camera_id"
               FROM "users"
@@ -52,7 +61,16 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT"
+                FROM "USERS"
+                  INNER JOIN (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL"
+                    ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID"
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "users"."id", "users"."name", "photos_external"."user_id", "photos_external"."cnt"
                 FROM "users"
@@ -76,7 +94,16 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT", "USERS"."ID", "USERS"."NAME"
+                FROM (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL"
+                  INNER JOIN "USERS"
+                    ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID"
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "photos_external"."user_id", "photos_external"."cnt", "users"."id", "users"."name"
                 FROM (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" GROUP BY "photos"."user_id") AS "photos_external"
@@ -101,7 +128,16 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT", "PHOTOS_EXTERNAL_2"."USER_ID", "PHOTOS_EXTERNAL_2"."CNT"
+                FROM (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" GROUP BY  "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL"
+                  INNER JOIN (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL_2"
+                    ON "PHOTOS_EXTERNAL_2"."USER_ID" = "PHOTOS_EXTERNAL"."USER_ID"
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "photos_external"."user_id", "photos_external"."cnt", "photos_external_2"."user_id", "photos_external_2"."cnt"
                 FROM (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" GROUP BY  "photos"."user_id") AS "photos_external"
@@ -126,7 +162,16 @@ module Arel
                 })
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{
+                  SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT"
+                  FROM "USERS"
+                    INNER JOIN (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" WHERE "PHOTOS"."USER_ID" = 1 GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL"
+                      ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID"
+                })
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{
                   SELECT "users"."id", "users"."name", "photos_external"."user_id", "photos_external"."cnt"
                   FROM "users"
@@ -150,7 +195,16 @@ module Arel
                 })
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{
+                  SELECT "PHOTOS_EXTERNAL"."USER_ID", "PHOTOS_EXTERNAL"."CNT", "USERS"."ID", "USERS"."NAME"
+                  FROM (SELECT "PHOTOS"."USER_ID", COUNT("PHOTOS"."ID") AS "CNT" FROM "PHOTOS" WHERE "PHOTOS"."USER_ID" = 1 GROUP BY "PHOTOS"."USER_ID") "PHOTOS_EXTERNAL"
+                    INNER JOIN "USERS"
+                      ON "USERS"."ID" = "PHOTOS_EXTERNAL"."USER_ID"
+                })
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{
                   SELECT "photos_external"."user_id", "photos_external"."cnt", "users"."id", "users"."name"
                   FROM (SELECT "photos"."user_id", COUNT("photos"."id") AS "cnt" FROM "photos" WHERE "photos"."user_id" = 1 GROUP BY "photos"."user_id") AS "photos_external"
diff --git a/spec/arel/engines/sql/integration/joins/with_compounds_spec.rb b/spec/arel/engines/sql/integration/joins/with_compounds_spec.rb
index 5909716542..65fe49d128 100644
--- a/spec/arel/engines/sql/integration/joins/with_compounds_spec.rb
+++ b/spec/arel/engines/sql/integration/joins/with_compounds_spec.rb
@@ -26,7 +26,16 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID"
+                FROM "USERS"
+                INNER JOIN "PHOTOS"
+                  ON "USERS"."ID" = "PHOTOS"."USER_ID" AND asdf
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "users"."id", "users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id"
                 FROM "users"
@@ -59,7 +68,18 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID"
+                FROM "USERS"
+                INNER JOIN "PHOTOS"
+                  ON "USERS"."ID" = "PHOTOS"."USER_ID"
+                WHERE "USERS"."ID" = 1
+                  AND "USERS"."ID" = 1
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "users"."id", "users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id"
                 FROM "users"
@@ -90,7 +110,17 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID"
+                FROM "USERS"
+                INNER JOIN "PHOTOS"
+                  ON "USERS"."ID" = "PHOTOS"."USER_ID"
+                GROUP BY "USERS"."ID"
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "users"."id", "users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id"
                 FROM "users"
diff --git a/spec/arel/engines/sql/unit/predicates/binary_spec.rb b/spec/arel/engines/sql/unit/predicates/binary_spec.rb
index 08f3310f8e..72c8e44888 100644
--- a/spec/arel/engines/sql/unit/predicates/binary_spec.rb
+++ b/spec/arel/engines/sql/unit/predicates/binary_spec.rb
@@ -37,6 +37,10 @@ module Arel
               adapter_is :sqlite3 do
                 sql.should be_like(%Q{("users"."id" <=> 1 OR "users"."name" <=> 'name')})
               end
+
+              adapter_is :oracle do
+                sql.should be_like(%Q{("USERS"."ID" <=> 1 OR "USERS"."NAME" <=> 'name')})
+              end
             end
           end
         end
@@ -57,6 +61,10 @@ module Arel
               adapter_is :postgresql do
                 sql.should be_like(%Q{("users"."id" <=> 1 AND "users"."name" <=> E'name')})
               end
+
+              adapter_is :oracle do
+                sql.should be_like(%Q{("USERS"."ID" <=> 1 AND "USERS"."NAME" <=> 'name')})
+              end
             end
           end
         end
@@ -71,7 +79,11 @@ module Arel
               sql.should be_like(%Q{`users`.`id` <=> `users`.`name`})
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{"USERS"."ID" <=> "USERS"."NAME"})
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{"users"."id" <=> "users"."name"})
             end
           end
@@ -90,7 +102,11 @@ module Arel
                 sql.should be_like(%Q{`users`.`id` <=> 1})
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{"USERS"."ID" <=> 1})
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{"users"."id" <=> 1})
               end
             end
@@ -111,6 +127,10 @@ module Arel
               adapter_is :postgresql do
                 sql.should be_like(%Q{"users"."name" <=> E'1-asdf'})
               end
+
+              adapter_is :oracle do
+                sql.should be_like(%Q{"USERS"."NAME" <=> '1-asdf'})
+              end
             end
           end
         end
diff --git a/spec/arel/engines/sql/unit/predicates/equality_spec.rb b/spec/arel/engines/sql/unit/predicates/equality_spec.rb
index 7a5cb42c85..bfd61185f2 100644
--- a/spec/arel/engines/sql/unit/predicates/equality_spec.rb
+++ b/spec/arel/engines/sql/unit/predicates/equality_spec.rb
@@ -19,7 +19,11 @@ module Arel
               sql.should be_like(%Q{`users`.`id` = `photos`.`user_id`})
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{"USERS"."ID" = "PHOTOS"."USER_ID"})
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{"users"."id" = "photos"."user_id"})
             end
           end
@@ -37,7 +41,11 @@ module Arel
               sql.should be_like(%Q{`users`.`id` IS NULL})
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{"USERS"."ID" IS NULL})
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{"users"."id" IS NULL})
             end
           end
@@ -52,7 +60,11 @@ module Arel
               sql.should be_like(%Q{`users`.`id` IS NULL})
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{"USERS"."ID" IS NULL})
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{"users"."id" IS NULL})
             end
           end
diff --git a/spec/arel/engines/sql/unit/predicates/in_spec.rb b/spec/arel/engines/sql/unit/predicates/in_spec.rb
index a1c42f714e..5d9b2cdcaa 100644
--- a/spec/arel/engines/sql/unit/predicates/in_spec.rb
+++ b/spec/arel/engines/sql/unit/predicates/in_spec.rb
@@ -22,7 +22,11 @@ module Arel
                 sql.should be_like(%Q{`users`.`id` IN (1, 2, 3)})
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{"USERS"."ID" IN (1, 2, 3)})
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{"users"."id" IN (1, 2, 3)})
               end
             end
@@ -40,7 +44,11 @@ module Arel
                 sql.should be_like(%Q{`users`.`id` IN (1, 2, 3)})
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{"USERS"."ID" IN (1, 2, 3)})
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{"users"."id" IN (1, 2, 3)})
               end
             end
@@ -58,7 +66,11 @@ module Arel
                 sql.should be_like(%Q{`users`.`id` IN (NULL)})
               end
 
-              adapter_is_not :mysql do
+              adapter_is :oracle do
+                sql.should be_like(%Q{"USERS"."ID" IN (NULL)})
+              end
+
+              adapter_is_not :mysql, :oracle do
                 sql.should be_like(%Q{"users"."id" IN (NULL)})
               end
             end
@@ -78,7 +90,11 @@ module Arel
               sql.should be_like(%Q{`users`.`id` BETWEEN 1 AND 2})
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{"USERS"."ID" BETWEEN 1 AND 2})
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{"users"."id" BETWEEN 1 AND 2})
             end
           end
@@ -109,6 +125,10 @@ module Arel
             adapter_is :postgresql do
               sql.should be_like(%Q{"developers"."created_at" BETWEEN '2010-01-01 00:00:00.000000' AND '2010-02-01 00:00:00.000000'})
             end
+
+            adapter_is :oracle do
+              sql.should be_like(%Q{"DEVELOPERS"."CREATED_AT" BETWEEN TO_TIMESTAMP('2010-01-01 00:00:00:000000','YYYY-MM-DD HH24:MI:SS:FF6') AND TO_TIMESTAMP('2010-02-01 00:00:00:000000','YYYY-MM-DD HH24:MI:SS:FF6')})
+            end
           end
         end
 
@@ -122,7 +142,13 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                "USERS"."ID" IN (SELECT "USERS"."ID", "USERS"."NAME" FROM "USERS")
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 "users"."id" IN (SELECT "users"."id", "users"."name" FROM "users")
               })
diff --git a/spec/arel/engines/sql/unit/predicates/predicates_spec.rb b/spec/arel/engines/sql/unit/predicates/predicates_spec.rb
index 81c348357c..e6130cf267 100644
--- a/spec/arel/engines/sql/unit/predicates/predicates_spec.rb
+++ b/spec/arel/engines/sql/unit/predicates/predicates_spec.rb
@@ -33,6 +33,12 @@ module Arel
                 ("users"."id" = 1 AND "users"."name" = E'name')
               })
             end
+
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                ("USERS"."ID" = 1 AND "USERS"."NAME" = 'name')
+              })
+            end
           end
         end
       end
@@ -59,6 +65,12 @@ module Arel
                 ("users"."id" = 1 OR "users"."name" = E'name')
               })
             end
+
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                ("USERS"."ID" = 1 OR "USERS"."NAME" = 'name')
+              })
+            end
           end
         end
       end
diff --git a/spec/arel/engines/sql/unit/primitives/attribute_spec.rb b/spec/arel/engines/sql/unit/primitives/attribute_spec.rb
index d24b6a9d13..9f864dd3a1 100644
--- a/spec/arel/engines/sql/unit/primitives/attribute_spec.rb
+++ b/spec/arel/engines/sql/unit/primitives/attribute_spec.rb
@@ -22,7 +22,11 @@ module Arel
             sql.should be_like(%Q{`users`.`id`})
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{"USERS"."ID"})
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{"users"."id"})
           end
         end
diff --git a/spec/arel/engines/sql/unit/primitives/expression_spec.rb b/spec/arel/engines/sql/unit/primitives/expression_spec.rb
index d4df4f8d5a..3b6a7314a2 100644
--- a/spec/arel/engines/sql/unit/primitives/expression_spec.rb
+++ b/spec/arel/engines/sql/unit/primitives/expression_spec.rb
@@ -15,7 +15,11 @@ module Arel
           sql.should be_like(%Q{COUNT(`users`.`id`) AS `alias`})
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{COUNT("USERS"."ID") AS "ALIAS"})
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{COUNT("users"."id") AS "alias"})
         end
       end
diff --git a/spec/arel/engines/sql/unit/primitives/literal_spec.rb b/spec/arel/engines/sql/unit/primitives/literal_spec.rb
index cf66a60be9..3bf60100f1 100644
--- a/spec/arel/engines/sql/unit/primitives/literal_spec.rb
+++ b/spec/arel/engines/sql/unit/primitives/literal_spec.rb
@@ -14,7 +14,11 @@ module Arel
           sql.should be_like(%Q{SELECT COUNT(*) AS count_id FROM `users`})
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{SELECT COUNT(*) AS count_id FROM "USERS"})
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{SELECT COUNT(*) AS count_id FROM "users"})
         end
       end
@@ -26,7 +30,11 @@ module Arel
           sql.should be_like(%Q{SELECT SUM(2 * credit_limit) AS sum_id FROM `users`})
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{SELECT SUM(2 * credit_limit) AS sum_id FROM "USERS"})
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{SELECT SUM(2 * credit_limit) AS sum_id FROM "users"})
         end
       end
diff --git a/spec/arel/engines/sql/unit/relations/alias_spec.rb b/spec/arel/engines/sql/unit/relations/alias_spec.rb
index 55d5a9b671..a6fd7ab036 100644
--- a/spec/arel/engines/sql/unit/relations/alias_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/alias_spec.rb
@@ -27,7 +27,17 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID"
+              FROM "USERS"
+              WHERE "USERS"."ID" = 1
+              GROUP BY "USERS"."ID"
+              ORDER BY "USERS"."ID" ASC
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id"
               FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/delete_spec.rb b/spec/arel/engines/sql/unit/relations/delete_spec.rb
index 0b1e2329b6..302a13c688 100644
--- a/spec/arel/engines/sql/unit/relations/delete_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/delete_spec.rb
@@ -14,7 +14,11 @@ module Arel
           sql.should be_like(%Q{DELETE FROM `users`})
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{DELETE FROM "USERS"})
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{DELETE FROM "users"})
         end
       end
@@ -30,7 +34,15 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            DELETE
+            FROM "USERS"
+            WHERE "USERS"."ID" = 1
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             DELETE
             FROM "users"
@@ -50,7 +62,15 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            DELETE
+            FROM "USERS"
+            WHERE ROWNUM <= 1
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             DELETE
             FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/from_spec.rb b/spec/arel/engines/sql/unit/relations/from_spec.rb
index a611cd65f4..0be3ac0f9a 100644
--- a/spec/arel/engines/sql/unit/relations/from_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/from_spec.rb
@@ -17,7 +17,14 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            SELECT "USERS"."ID", "USERS"."NAME"
+            FROM workers
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             SELECT "users"."id", "users"."name"
             FROM workers
@@ -37,7 +44,14 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            SELECT "USERS"."ID", "USERS"."NAME"
+            FROM users
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             SELECT "users"."id", "users"."name"
             FROM users
diff --git a/spec/arel/engines/sql/unit/relations/group_spec.rb b/spec/arel/engines/sql/unit/relations/group_spec.rb
index 703dc35be5..72a9f4e99e 100644
--- a/spec/arel/engines/sql/unit/relations/group_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/group_spec.rb
@@ -20,7 +20,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+              GROUP BY "USERS"."ID"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
@@ -42,7 +50,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+              GROUP BY asdf
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/having_spec.rb b/spec/arel/engines/sql/unit/relations/having_spec.rb
index 915ee3af08..fe6f3cc520 100644
--- a/spec/arel/engines/sql/unit/relations/having_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/having_spec.rb
@@ -20,7 +20,16 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "DEVELOPERS"."ID", "DEVELOPERS"."NAME", "DEVELOPERS"."SALARY", "DEVELOPERS"."DEPARTMENT", "DEVELOPERS"."CREATED_AT"
+              FROM "DEVELOPERS"
+              GROUP BY "DEVELOPERS"."DEPARTMENT"
+              HAVING MIN(salary) > 1000
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "developers"."id", "developers"."name", "developers"."salary", "developers"."department", "developers"."created_at"
               FROM "developers"
diff --git a/spec/arel/engines/sql/unit/relations/insert_spec.rb b/spec/arel/engines/sql/unit/relations/insert_spec.rb
index 69c5bb052c..4b412093e4 100644
--- a/spec/arel/engines/sql/unit/relations/insert_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/insert_spec.rb
@@ -46,6 +46,14 @@ module Arel
             RETURNING "id"
           })
         end
+
+        adapter_is :oracle do
+          @insertion.to_sql.should be_like(%Q{
+            INSERT
+            INTO "USERS"
+            ("ID", "NAME") VALUES (1, 'nick')
+          })
+        end
       end
 
       describe 'when given values whose types correspond to the types of the attributes' do
@@ -78,6 +86,14 @@ module Arel
               RETURNING "id"
             })
           end
+
+          adapter_is :oracle do
+            @insertion.to_sql.should be_like(%Q{
+              INSERT
+              INTO "USERS"
+              ("NAME") VALUES ('nick')
+            })
+          end
         end
       end
 
@@ -111,6 +127,15 @@ module Arel
               RETURNING "id"
             })
           end
+
+          adapter_is :oracle do
+            @insertion.to_sql.should be_like(%Q{
+              INSERT
+              INTO "USERS"
+              ("ID") VALUES (1)
+            })
+          end
+
         end
       end
     end
diff --git a/spec/arel/engines/sql/unit/relations/join_spec.rb b/spec/arel/engines/sql/unit/relations/join_spec.rb
index b926c2aaab..2d23a06397 100644
--- a/spec/arel/engines/sql/unit/relations/join_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/join_spec.rb
@@ -28,7 +28,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID"
+              FROM "USERS"
+                INNER JOIN "PHOTOS" ON "USERS"."ID" = "PHOTOS"."USER_ID"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id"
               FROM "users"
@@ -49,7 +57,15 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "SUPER_USERS"."ID", "SUPER_USERS"."NAME", "PHOTOS"."ID", "PHOTOS"."USER_ID", "PHOTOS"."CAMERA_ID"
+                FROM "USERS" "SUPER_USERS"
+                  INNER JOIN "PHOTOS" ON "SUPER_USERS"."ID" = "PHOTOS"."USER_ID"
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "super_users"."id", "super_users"."name", "photos"."id", "photos"."user_id", "photos"."camera_id"
                 FROM "users" AS "super_users"
@@ -71,7 +87,15 @@ module Arel
               })
             end
 
-            adapter_is_not :mysql do
+            adapter_is :oracle do
+              sql.should be_like(%Q{
+                SELECT "SUPER_USERS"."ID", "SUPER_USERS"."NAME", "SUPER_PHOTOS"."ID", "SUPER_PHOTOS"."USER_ID", "SUPER_PHOTOS"."CAMERA_ID"
+                FROM "USERS" "SUPER_USERS"
+                  INNER JOIN "PHOTOS" "SUPER_PHOTOS" ON "SUPER_USERS"."ID" = "SUPER_PHOTOS"."USER_ID"
+              })
+            end
+
+            adapter_is_not :mysql, :oracle do
               sql.should be_like(%Q{
                 SELECT "super_users"."id", "super_users"."name", "super_photos"."id", "super_photos"."user_id", "super_photos"."camera_id"
                 FROM "users" AS "super_users"
@@ -95,7 +119,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+                INNER JOIN asdf ON fdsa
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
@@ -119,7 +151,17 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+                INNER JOIN asdf ON fdsa
+                INNER JOIN lifo ON fifo
+                INNER JOIN hatful ON hallow
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/lock_spec.rb b/spec/arel/engines/sql/unit/relations/lock_spec.rb
index 23f8a9f577..72a8a2e457 100644
--- a/spec/arel/engines/sql/unit/relations/lock_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/lock_spec.rb
@@ -30,6 +30,24 @@ module Arel
             FROM "users"
           })
         end
+
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            SELECT "USERS"."ID", "USERS"."NAME"
+            FROM "USERS" FOR UPDATE
+          })
+
+          sql_with_order_by = @relation.order(@relation[:id]).take(1).lock.to_sql
+          sql_with_order_by.should be_like(%Q{
+            SELECT "USERS"."ID", "USERS"."NAME"
+            FROM "USERS"
+            WHERE "ID" IN (select * from
+                          (SELECT "ID" FROM "USERS" ORDER BY "USERS"."ID" ASC)
+                          where rownum <= 1)
+            FOR UPDATE
+          })
+
+        end
       end
 
       it "manufactures a select query locking with a given lock" do
@@ -55,6 +73,13 @@ module Arel
             FROM "users"
           })
         end
+
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            SELECT "USERS"."ID", "USERS"."NAME"
+            FROM "USERS" LOCK IN SHARE MODE
+          })
+        end
       end
     end
   end
diff --git a/spec/arel/engines/sql/unit/relations/order_spec.rb b/spec/arel/engines/sql/unit/relations/order_spec.rb
index 575e617021..3c9d9ef598 100644
--- a/spec/arel/engines/sql/unit/relations/order_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/order_spec.rb
@@ -20,7 +20,31 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+              ORDER BY "USERS"."ID" ASC
+            })
+
+            distinct_attributes = ActiveRecord::Base.connection.distinct('"USERS"."NAME"', '"USERS"."ID"')
+            @relation.project(distinct_attributes).order(@relation[:id]).to_sql.should be_like(%Q{
+              SELECT DISTINCT "USERS"."NAME",
+              FIRST_VALUE("USERS"."ID") OVER (PARTITION BY "USERS"."NAME" ORDER BY "USERS"."ID") AS alias_0__
+              FROM "USERS"
+              ORDER BY alias_0__
+            })
+
+            distinct_attributes = ActiveRecord::Base.connection.distinct('"USERS"."NAME"', '"USERS"."ID" DESC')
+            @relation.project(distinct_attributes).order('"USERS"."ID" DESC').to_sql.should be_like(%Q{
+              SELECT DISTINCT "USERS"."NAME",
+              FIRST_VALUE("USERS"."ID") OVER (PARTITION BY "USERS"."NAME" ORDER BY "USERS"."ID" DESC) AS alias_0__
+              FROM "USERS"
+              ORDER BY alias_0__ DESC
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
@@ -46,7 +70,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+              ORDER BY "USERS"."ID" ASC, "USERS"."NAME" ASC
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
@@ -72,7 +104,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+              ORDER BY asdf
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
@@ -99,7 +139,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+              ORDER BY "USERS"."NAME" ASC, "USERS"."ID" ASC
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/project_spec.rb b/spec/arel/engines/sql/unit/relations/project_spec.rb
index 70f2dff70a..e73c7775a1 100644
--- a/spec/arel/engines/sql/unit/relations/project_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/project_spec.rb
@@ -19,7 +19,14 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID"
+              FROM "USERS"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id"
               FROM "users"
@@ -42,7 +49,13 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT (SELECT "USERS"."NAME" FROM "USERS") AS "USERS" FROM "USERS"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT (SELECT "users"."name" FROM "users") AS "users" FROM "users"
             })
@@ -60,7 +73,13 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT asdf FROM "USERS"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT asdf FROM "users"
             })
@@ -79,7 +98,14 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT COUNT("USERS"."ID") AS count_id
+              FROM "USERS"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT COUNT("users"."id") AS count_id
               FROM "users"
@@ -97,7 +123,14 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT COUNT(DISTINCT "USERS"."ID") AS count_id
+              FROM "USERS"
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT COUNT(DISTINCT "users"."id") AS count_id
               FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/skip_spec.rb b/spec/arel/engines/sql/unit/relations/skip_spec.rb
index 2d606359ee..41b80d12d8 100644
--- a/spec/arel/engines/sql/unit/relations/skip_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/skip_spec.rb
@@ -19,7 +19,16 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            select * from (select raw_sql_.*, rownum raw_rnum_ from
+            (SELECT "USERS"."ID", "USERS"."NAME"
+            FROM "USERS") raw_sql_)
+            where raw_rnum_ > 4
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             SELECT "users"."id", "users"."name"
             FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/table_spec.rb b/spec/arel/engines/sql/unit/relations/table_spec.rb
index d8584ccaec..e497e1b6c2 100644
--- a/spec/arel/engines/sql/unit/relations/table_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/table_spec.rb
@@ -17,7 +17,14 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            SELECT "USERS"."ID", "USERS"."NAME"
+            FROM "USERS"
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             SELECT "users"."id", "users"."name"
             FROM "users"
@@ -37,7 +44,14 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            SELECT "SUPER_USERS"."ID", "SUPER_USERS"."NAME"
+            FROM "USERS" "SUPER_USERS"
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             SELECT "super_users"."id", "super_users"."name"
             FROM "users" AS "super_users"
@@ -55,7 +69,14 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            SELECT "USERS"."ID", "USERS"."NAME"
+            FROM "USERS"
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             SELECT "users"."id", "users"."name"
             FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/take_spec.rb b/spec/arel/engines/sql/unit/relations/take_spec.rb
index 2a8aea3d9d..9f2967a0bd 100644
--- a/spec/arel/engines/sql/unit/relations/take_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/take_spec.rb
@@ -19,7 +19,24 @@ module Arel
           })
         end
 
-        adapter_is_not :mysql do
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            SELECT "USERS"."ID", "USERS"."NAME"
+            FROM "USERS"
+            WHERE ROWNUM <= 4
+          })
+
+          sql_with_order_by = Take.new(@relation.order(@relation[:id]), @taken).to_sql
+          sql_with_order_by.should be_like(%Q{
+            select * from
+            (SELECT "USERS"."ID", "USERS"."NAME"
+            FROM "USERS"
+            ORDER BY "USERS"."ID" ASC)
+            where rownum <= 4
+          })
+        end
+
+        adapter_is_not :mysql, :oracle do
           sql.should be_like(%Q{
             SELECT "users"."id", "users"."name"
             FROM "users"
diff --git a/spec/arel/engines/sql/unit/relations/update_spec.rb b/spec/arel/engines/sql/unit/relations/update_spec.rb
index fd444e27a8..cc2ad9913b 100644
--- a/spec/arel/engines/sql/unit/relations/update_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/update_spec.rb
@@ -36,6 +36,13 @@ module Arel
             SET "id" = 1, "name" = E'nick'
           })
         end
+
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            UPDATE "USERS"
+            SET "ID" = 1, "NAME" = 'nick'
+          })
+        end
       end
 
       it "manufactures sql updating attributes when given a ranged relation" do
@@ -64,6 +71,23 @@ module Arel
             WHERE "id" IN (SELECT "id" FROM "users"  LIMIT 1)
           })
         end
+
+        adapter_is :oracle do
+          sql.should be_like(%Q{
+            UPDATE "USERS" SET
+            "NAME" = 'nick'
+            WHERE "ID" IN (SELECT "ID" FROM "USERS" WHERE ROWNUM <= 1)
+          })
+
+          sql_with_order_by = Update.new(@relation.order(@relation[:id]).take(1), @relation[:name] => "nick").to_sql
+          sql_with_order_by.should be_like(%Q{
+            UPDATE "USERS" SET
+            "NAME" = 'nick'
+            WHERE "ID" IN (select * from
+                          (SELECT "ID" FROM "USERS" ORDER BY "USERS"."ID" ASC)
+                          where rownum <= 1)
+          })
+        end
       end
 
       describe 'when given values whose types correspond to the types of the attributes' do
@@ -92,6 +116,13 @@ module Arel
               SET "name" = E'nick'
             })
           end
+
+          adapter_is :oracle do
+            @update.to_sql.should be_like(%Q{
+              UPDATE "USERS"
+              SET "NAME" = 'nick'
+            })
+          end
         end
       end
 
@@ -108,7 +139,14 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            @update.to_sql.should be_like(%Q{
+              UPDATE "USERS"
+              SET "ID" = 1
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             @update.to_sql.should be_like(%Q{
               UPDATE "users"
               SET "id" = 1
@@ -149,6 +187,14 @@ module Arel
               WHERE "users"."id" = 1
             })
           end
+
+          adapter_is :oracle do
+            @update.to_sql.should be_like(%Q{
+              UPDATE "USERS"
+              SET "NAME" = 'nick'
+              WHERE "USERS"."ID" = 1
+            })
+          end
         end
       end
     end
diff --git a/spec/arel/engines/sql/unit/relations/where_spec.rb b/spec/arel/engines/sql/unit/relations/where_spec.rb
index 8f9a8db4c7..5f559efad3 100644
--- a/spec/arel/engines/sql/unit/relations/where_spec.rb
+++ b/spec/arel/engines/sql/unit/relations/where_spec.rb
@@ -20,7 +20,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+              WHERE "USERS"."ID" = 1
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
@@ -42,7 +50,15 @@ module Arel
             })
           end
 
-          adapter_is_not :mysql do
+          adapter_is :oracle do
+            sql.should be_like(%Q{
+              SELECT "USERS"."ID", "USERS"."NAME"
+              FROM "USERS"
+              WHERE asdf
+            })
+          end
+
+          adapter_is_not :mysql, :oracle do
             sql.should be_like(%Q{
               SELECT "users"."id", "users"."name"
               FROM "users"
diff --git a/spec/connections/oracle_connection.rb b/spec/connections/oracle_connection.rb
new file mode 100644
index 0000000000..05be04e410
--- /dev/null
+++ b/spec/connections/oracle_connection.rb
@@ -0,0 +1,19 @@
+puts "Using native Oracle"
+require "active_record"
+require 'logger'
+
+# Prepend oracle_enhanced local development directory in front of load path
+$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../../../oracle-enhanced/lib"
+
+ActiveRecord::Base.logger = Logger.new("debug.log")
+
+ActiveRecord::Base.configurations = {
+  'unit' => {
+    :adapter  => 'oracle_enhanced',
+    :username => 'arel_unit',
+    :password => 'arel_unit',
+    :database => 'orcl',
+  }
+}
+
+ActiveRecord::Base.establish_connection 'unit'
diff --git a/spec/schemas/oracle_schema.rb b/spec/schemas/oracle_schema.rb
new file mode 100644
index 0000000000..c8207c8d98
--- /dev/null
+++ b/spec/schemas/oracle_schema.rb
@@ -0,0 +1,20 @@
+ActiveRecord::Schema.define do
+  suppress_messages do
+    create_table :users, :primary_key_trigger => true, :force => true do |t|
+      t.string :name, :limit => 255, :null => false
+    end
+
+    create_table :photos, :primary_key_trigger => true, :force => true do |t|
+      t.integer :user_id
+      t.integer :camera_id
+    end
+
+    create_table :developers, :primary_key_trigger => true, :force => true do |t|
+      t.string :name, :limit => 255, :null => false
+      t.integer :salary
+      t.string :department, :limit => 255, :null => false
+      t.timestamp :created_at, :null => false
+    end
+
+  end
+end
diff --git a/spec/spec_helper.rb b/spec/spec_helper.rb
index 6d99fa4038..1c45f34dfe 100644
--- a/spec/spec_helper.rb
+++ b/spec/spec_helper.rb
@@ -12,18 +12,21 @@ require 'arel'
 end
 
 module AdapterGuards
-  def adapter_is(name)
-    verify_adapter_name(name)
-    yield if name.to_s == adapter_name
+  def adapter_is(*names)
+    names = names.map(&:to_s)
+    names.each{|name| verify_adapter_name(name)}
+    yield if names.include? adapter_name
   end
 
-  def adapter_is_not(name)
-    verify_adapter_name(name)
-    yield if name.to_s != adapter_name
+  def adapter_is_not(*names)
+    names = names.map(&:to_s)
+    names.each{|name| verify_adapter_name(name)}
+    yield unless names.include? adapter_name
   end
 
   def adapter_name
     name = ActiveRecord::Base.configurations["unit"][:adapter]
+    name = 'oracle' if name == 'oracle_enhanced'
     verify_adapter_name(name)
     name
   end
@@ -33,7 +36,7 @@ module AdapterGuards
   end
 
   def valid_adapters
-    %w[mysql postgresql sqlite3]
+    %w[mysql postgresql sqlite3 oracle]
   end
 end
 
@@ -53,3 +56,9 @@ Spec::Runner.configure do |config|
     Arel::Table.engine = Arel::Sql::Engine.new(ActiveRecord::Base)
   end
 end
+
+# load corresponding adapter using ADAPTER environment variable when running single *_spec.rb file
+if adapter = ENV['ADAPTER']
+  require "#{dir}/connections/#{adapter}_connection.rb"
+  require "#{dir}/schemas/#{adapter}_schema.rb"
+end