module Sequel::Oracle

Public Class Methods

mock_adapter_setup(db) click to toggle source
# File lib/sequel/adapters/shared/oracle.rb, line 9
def self.mock_adapter_setup(db)
  db.instance_eval do
    @server_version = 11000000
    @primary_key_sequences = {}
  end
end

Public Instance Methods

complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
# File lib/sequel/adapters/shared/oracle.rb, line 339
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.at(0))
    sql << ','
    literal_append(sql, args.at(1))
    if op == :'~*' || op == :'!~*'
      sql << ", 'i'"
    end
    sql << ')'
  when :%, :<<, :>>, :'B~'
    complex_expression_emulate_append(sql, op, args)
  else
    super
  end
end
constant_sql_append(sql, c) click to toggle source

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.

Calls superclass method
# File lib/sequel/adapters/shared/oracle.rb, line 374
def constant_sql_append(sql, c)
  if c == :CURRENT_TIME
    super(sql, :CURRENT_TIMESTAMP)
  else
    super
  end
end
empty?() click to toggle source

Use a custom expression with EXISTS to determine whether a dataset is empty.

# File lib/sequel/adapters/shared/oracle.rb, line 390
def empty?
  db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil
end
except(dataset, opts=OPTS) click to toggle source

Oracle uses MINUS instead of EXCEPT, and doesn't support EXCEPT ALL

# File lib/sequel/adapters/shared/oracle.rb, line 383
def except(dataset, opts=OPTS)
  raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all]
  compound_clone(:minus, dataset, opts)
end
recursive_cte_requires_column_aliases?() click to toggle source

Oracle requires recursive CTEs to have column aliases.

# File lib/sequel/adapters/shared/oracle.rb, line 438
def recursive_cte_requires_column_aliases?
  true
end
requires_sql_standard_datetimes?() click to toggle source

Oracle requires SQL standard datetimes

# File lib/sequel/adapters/shared/oracle.rb, line 395
def requires_sql_standard_datetimes?
  true
end
select_sql() click to toggle source

Handle LIMIT by using a unlimited subselect filtered with ROWNUM.

Calls superclass method
# File lib/sequel/adapters/shared/oracle.rb, line 407
def select_sql
  return super if @opts[:sql]
  if o = @opts[:offset]
    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 = clone(:limit=>nil)
    # 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
sequence(s) click to toggle source

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 402
def sequence(s)
  clone(:sequence=>s)
end
server_version() click to toggle source

The version of the database server

# File lib/sequel/adapters/shared/oracle.rb, line 512
def server_version
  db.server_version(@opts[:server])
end
supports_cte?(type=:select) click to toggle source
# File lib/sequel/adapters/shared/oracle.rb, line 442
def supports_cte?(type=:select)
  type == :select
end
supports_derived_column_lists?() click to toggle source

Oracle does not support derived column lists

# File lib/sequel/adapters/shared/oracle.rb, line 447
def supports_derived_column_lists?
  false
end
supports_group_cube?() click to toggle source

Oracle supports GROUP BY CUBE

# File lib/sequel/adapters/shared/oracle.rb, line 452
def supports_group_cube?
  true
end
supports_group_rollup?() click to toggle source

Oracle supports GROUP BY ROLLUP

# File lib/sequel/adapters/shared/oracle.rb, line 457
def supports_group_rollup?
  true
end
supports_grouping_sets?() click to toggle source

Oracle supports GROUPING SETS

# File lib/sequel/adapters/shared/oracle.rb, line 462
def supports_grouping_sets?
  true
end
supports_intersect_except_all?() click to toggle source

Oracle does not support INTERSECT ALL or EXCEPT ALL

# File lib/sequel/adapters/shared/oracle.rb, line 467
def supports_intersect_except_all?
  false
end
supports_is_true?() click to toggle source

Oracle does not support IS TRUE.

# File lib/sequel/adapters/shared/oracle.rb, line 472
def supports_is_true?
  false
end
supports_limits_in_correlated_subqueries?() click to toggle source

Oracle does not support limits in correlated subqueries.

# File lib/sequel/adapters/shared/oracle.rb, line 477
def supports_limits_in_correlated_subqueries?
  false
end
supports_offsets_in_correlated_subqueries?() click to toggle source

Oracle does not support offsets in correlated subqueries.

# File lib/sequel/adapters/shared/oracle.rb, line 482
def supports_offsets_in_correlated_subqueries?
  false
end
supports_regexp?() click to toggle source

Oracle supports pattern matching via regular expressions

# File lib/sequel/adapters/shared/oracle.rb, line 517
def supports_regexp?
  server_version >= 10010002
end
supports_select_all_and_column?() click to toggle source

Oracle does not support SELECT *, column

# File lib/sequel/adapters/shared/oracle.rb, line 487
def supports_select_all_and_column?
  false
end
supports_skip_locked?() click to toggle source

Oracle supports SKIP LOCKED.

# File lib/sequel/adapters/shared/oracle.rb, line 492
def supports_skip_locked?
  true
end
supports_timestamp_timezones?() click to toggle source

Oracle supports timezones in literal timestamps.

# File lib/sequel/adapters/shared/oracle.rb, line 497
def supports_timestamp_timezones?
  true
end
supports_where_true?() click to toggle source

Oracle does not support WHERE 'Y' for WHERE TRUE.

# File lib/sequel/adapters/shared/oracle.rb, line 502
def supports_where_true?
  false
end
supports_window_functions?() click to toggle source

Oracle supports window functions

# File lib/sequel/adapters/shared/oracle.rb, line 507
def supports_window_functions?
  true
end

Private Instance Methods

allow_preparing_prepared_statements?() click to toggle source

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 525
def allow_preparing_prepared_statements?
  true
end
as_sql_append(sql, aliaz, column_aliases=nil) click to toggle source

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.

# File lib/sequel/adapters/shared/oracle.rb, line 531
def as_sql_append(sql, aliaz, column_aliases=nil)
  raise Error, "oracle does not support derived column lists" if column_aliases
  sql << SPACE
  quote_identifier_append(sql, aliaz)
end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

# File lib/sequel/adapters/shared/oracle.rb, line 538
def default_timestamp_format
  TIMESTAMP_FORMAT
end
empty_from_sql() click to toggle source
# File lib/sequel/adapters/shared/oracle.rb, line 542
def empty_from_sql
  DUAL
end
emulate_function?(name) click to toggle source

There is no function on Microsoft SQL Server 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 550
def emulate_function?(name)
  name == :char_length
end
emulate_function_sql_append(sql, f) click to toggle source

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 559
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
execute_insert(sql, opts=OPTS) click to toggle source

If this dataset is associated with a sequence, return the most recently inserted sequence value.

Calls superclass method
# File lib/sequel/adapters/shared/oracle.rb, line 567
def execute_insert(sql, opts=OPTS)
  opts = Hash[opts]
  if f = @opts[:from]
    opts[:table] = f.first
  end
  opts[:sequence] = @opts[:sequence]
  super
end
format_timestamp_offset(hour, minute) click to toggle source

Use a colon for the timestamp offset, since Oracle appears to require it.

# File lib/sequel/adapters/shared/oracle.rb, line 577
def format_timestamp_offset(hour, minute)
  sprintf(TIMESTAMP_OFFSET_FORMAT, hour, minute)
end
insert_supports_empty_values?() click to toggle source

Oracle doesn't support empty values when inserting.

# File lib/sequel/adapters/shared/oracle.rb, line 582
def insert_supports_empty_values?
  false
end
literal_blob_append(sql, v) click to toggle source

Use string in hex format for blob data.

# File lib/sequel/adapters/shared/oracle.rb, line 587
def literal_blob_append(sql, v)
  sql << APOS << v.unpack(HSTAR).first << APOS
end
literal_false() click to toggle source

Oracle uses 'N' for false values.

# File lib/sequel/adapters/shared/oracle.rb, line 592
def literal_false
  BOOL_FALSE
end
literal_string_append(sql, v) click to toggle source

Oracle uses the SQL standard of only doubling ' inside strings.

# File lib/sequel/adapters/shared/oracle.rb, line 597
def literal_string_append(sql, v)
  sql << APOS << v.gsub(APOS_RE, DOUBLE_APOS) << APOS
end
literal_true() click to toggle source

Oracle uses 'Y' for true values.

# File lib/sequel/adapters/shared/oracle.rb, line 602
def literal_true
  BOOL_TRUE
end
multi_insert_sql_strategy() click to toggle source

Oracle can insert multiple rows using a UNION

# File lib/sequel/adapters/shared/oracle.rb, line 607
def multi_insert_sql_strategy
  :union
end
select_lock_sql(sql) click to toggle source

Use SKIP LOCKED if skipping locked rows.

Calls superclass method
# File lib/sequel/adapters/shared/oracle.rb, line 612
def select_lock_sql(sql)
  super

  if @opts[:skip_locked]
    sql << SKIP_LOCKED
  end
end
supports_quoted_function_names?() click to toggle source

Oracle supports quoted function names.

# File lib/sequel/adapters/shared/oracle.rb, line 621
def supports_quoted_function_names?
  true
end