module Sequel::Oracle
Public Class Methods
# File lib/sequel/adapters/shared/oracle.rb, line 9 def self.mock_adapter_setup(db) db.instance_exec do @server_version = 11000000 @primary_key_sequences = {} end end
Public Instance Methods
# File lib/sequel/adapters/shared/oracle.rb, line 323 def complex_expression_sql_append(sql, op, args) case op when :& complex_expression_arg_pairs_append(sql, args, &BITAND_PROC) when :| complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)} when :^ complex_expression_arg_pairs_append(sql, args) do |*x| s1 = complex_expression_arg_pairs(x){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)} s2 = complex_expression_arg_pairs(x, &BITAND_PROC) Sequel.lit(["(", " - ", ")"], s1, s2) end when :~, :'!~', :'~*', :'!~*' raise InvalidOperation, "Pattern matching via regular expressions is not supported in this Oracle version" unless supports_regexp? if op == :'!~' || op == :'!~*' sql << 'NOT ' end sql << 'REGEXP_LIKE(' literal_append(sql, args[0]) sql << ',' literal_append(sql, args[1]) if op == :'~*' || op == :'!~*' sql << ", 'i'" end sql << ')' when :%, :<<, :>>, :'B~' complex_expression_emulate_append(sql, op, args) else super end end
Oracle doesn't support CURRENT_TIME, as it doesn't have a type for storing just time values without a date, so use CURRENT_TIMESTAMP in its place.
# File lib/sequel/adapters/shared/oracle.rb, line 358 def constant_sql_append(sql, c) if c == :CURRENT_TIME super(sql, :CURRENT_TIMESTAMP) else super end end
Use a custom expression with EXISTS to determine whether a dataset is empty.
# File lib/sequel/adapters/shared/oracle.rb, line 374 def empty? db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil end
Oracle uses MINUS instead of EXCEPT, and doesn't support EXCEPT ALL
# File lib/sequel/adapters/shared/oracle.rb, line 367 def except(dataset, opts=OPTS) raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all] compound_clone(:minus, dataset, opts) end
Oracle requires recursive CTEs to have column aliases.
# File lib/sequel/adapters/shared/oracle.rb, line 442 def recursive_cte_requires_column_aliases? true end
# File lib/sequel/adapters/shared/oracle.rb, line 425 def select_limit_sql(sql) return unless supports_fetch_next_rows? if offset = @opts[:offset] sql << " OFFSET " literal_append(sql, offset) sql << " ROWS" end if limit = @opts[:limit] sql << " FETCH NEXT " literal_append(sql, limit) sql << " ROWS ONLY" end end
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle 12 is used.
# File lib/sequel/adapters/shared/oracle.rb, line 392 def select_sql return super if @opts[:sql] return super if supports_fetch_next_rows? o = @opts[:offset] if o && o != 0 columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns dsa1 = dataset_alias(1) rn = row_number_column limit = @opts[:limit] ds = unlimited. from_self(:alias=>dsa1). select_append(ROW_NUMBER_EXPRESSION.as(rn)). from_self(:alias=>dsa1). select(*columns). where(SQL::Identifier.new(rn) > o) ds = ds.where(SQL::Identifier.new(rn) <= Sequel.+(o, limit)) if limit sql = @opts[:append_sql] || String.new subselect_sql_append(sql, ds) sql elsif limit = @opts[:limit] ds = unlimited # Lock doesn't work in subselects, so don't use a subselect when locking. # Don't use a subselect if custom SQL is used, as it breaks somethings. ds = ds.from_self unless @opts[:lock] sql = @opts[:append_sql] || String.new subselect_sql_append(sql, ds.where(SQL::ComplexExpression.new(:<=, ROW_NUMBER_EXPRESSION, limit))) sql else super end end
Create a copy of this dataset associated to the given sequence name, which will be used when calling insert to find the most recently inserted value for the sequence.
# File lib/sequel/adapters/shared/oracle.rb, line 386 def sequence(s) clone(:sequence=>s) end
The version of the database server
# File lib/sequel/adapters/shared/oracle.rb, line 527 def server_version db.server_version(@opts[:server]) end
# File lib/sequel/adapters/shared/oracle.rb, line 446 def supports_cte?(type=:select) type == :select end
Oracle does not support derived column lists
# File lib/sequel/adapters/shared/oracle.rb, line 451 def supports_derived_column_lists? false end
Oracle supports FETCH NEXT ROWS since 12c, but it doesn't work when locking or when skipping locked rows.
# File lib/sequel/adapters/shared/oracle.rb, line 457 def supports_fetch_next_rows? server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked]) end
Oracle supports GROUP BY CUBE
# File lib/sequel/adapters/shared/oracle.rb, line 462 def supports_group_cube? true end
Oracle supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/oracle.rb, line 467 def supports_group_rollup? true end
Oracle supports GROUPING SETS
# File lib/sequel/adapters/shared/oracle.rb, line 472 def supports_grouping_sets? true end
Oracle does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/oracle.rb, line 477 def supports_intersect_except_all? false end
Oracle does not support IS TRUE.
# File lib/sequel/adapters/shared/oracle.rb, line 482 def supports_is_true? false end
Oracle supports NOWAIT.
# File lib/sequel/adapters/shared/oracle.rb, line 492 def supports_nowait? true end
Oracle 10+ supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/oracle.rb, line 532 def supports_regexp? server_version >= 10010002 end
Oracle does not support SELECT *, column
# File lib/sequel/adapters/shared/oracle.rb, line 502 def supports_select_all_and_column? false end
Oracle supports SKIP LOCKED.
# File lib/sequel/adapters/shared/oracle.rb, line 507 def supports_skip_locked? true end
Oracle supports timezones in literal timestamps.
# File lib/sequel/adapters/shared/oracle.rb, line 512 def supports_timestamp_timezones? true end
Oracle does not support WHERE 'Y' for WHERE TRUE.
# File lib/sequel/adapters/shared/oracle.rb, line 517 def supports_where_true? false end
Oracle supports window functions
# File lib/sequel/adapters/shared/oracle.rb, line 522 def supports_window_functions? true end
Private Instance Methods
Allow preparing prepared statements, since determining the prepared sql to use for a prepared statement requires calling prepare on that statement.
# File lib/sequel/adapters/shared/oracle.rb, line 540 def allow_preparing_prepared_statements? true end
Oracle doesn't support the use of AS when aliasing a dataset. It doesn't require the use of AS anywhere, so this disables it in all cases. Oracle also does not support derived column lists in aliases.
# File lib/sequel/adapters/shared/oracle.rb, line 547 def as_sql_append(sql, aliaz, column_aliases=nil) raise Error, "oracle does not support derived column lists" if column_aliases sql << ' ' quote_identifier_append(sql, aliaz) end
The strftime format to use when literalizing the time.
# File lib/sequel/adapters/shared/oracle.rb, line 554 def default_timestamp_format "TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'" end
# File lib/sequel/adapters/shared/oracle.rb, line 558 def empty_from_sql ' FROM DUAL' end
There is no function on Oracle that does character length and respects trailing spaces (datalength respects trailing spaces, but counts bytes instead of characters). Use a hack to work around the trailing spaces issue.
# File lib/sequel/adapters/shared/oracle.rb, line 566 def emulate_function?(name) name == :char_length end
Oracle treats empty strings like NULL values, and doesn't support char_length, so make char_length use length with a nonempty string. Unfortunately, as Oracle treats the empty string as NULL, there is no way to get trim to return an empty string instead of nil if the string only contains spaces.
# File lib/sequel/adapters/shared/oracle.rb, line 575 def emulate_function_sql_append(sql, f) if f.name == :char_length literal_append(sql, Sequel::SQL::Function.new(:length, Sequel.join([f.args.first, 'x'])) - 1) end end
If this dataset is associated with a sequence, return the most recently inserted sequence value.
# File lib/sequel/adapters/shared/oracle.rb, line 583 def execute_insert(sql, opts=OPTS) opts = Hash[opts] if f = @opts[:from] opts[:table] = f.first end opts[:sequence] = @opts[:sequence] super end
Use a colon for the timestamp offset, since Oracle appears to require it.
# File lib/sequel/adapters/shared/oracle.rb, line 593 def format_timestamp_offset(hour, minute) sprintf("%+03i:%02i", hour, minute) end
Oracle doesn't support empty values when inserting.
# File lib/sequel/adapters/shared/oracle.rb, line 598 def insert_supports_empty_values? false end
Use string in hex format for blob data.
# File lib/sequel/adapters/shared/oracle.rb, line 603 def literal_blob_append(sql, v) sql << "'" << v.unpack("H*").first << "'" end
Oracle uses 'N' for false values.
# File lib/sequel/adapters/shared/oracle.rb, line 608 def literal_false "'N'" end
Oracle uses 'Y' for true values.
# File lib/sequel/adapters/shared/oracle.rb, line 618 def literal_true "'Y'" end
Oracle can insert multiple rows using a UNION
# File lib/sequel/adapters/shared/oracle.rb, line 623 def multi_insert_sql_strategy :union end
Use SKIP LOCKED if skipping locked rows.
# File lib/sequel/adapters/shared/oracle.rb, line 628 def select_lock_sql(sql) super if @opts[:lock] if @opts[:skip_locked] sql << " SKIP LOCKED" elsif @opts[:nowait] sql << " NOWAIT" end end end
Oracle supports quoted function names.
# File lib/sequel/adapters/shared/oracle.rb, line 641 def supports_quoted_function_names? true end