Module: Sequel::Oracle::DatasetMethods

Included in:
JDBC::Oracle::Dataset, Dataset
Defined in:
lib/sequel/adapters/shared/oracle.rb

Constant Summary collapse

ROW_NUMBER_EXPRESSION = 
LiteralString.new('ROWNUM').freeze
SPACE = 
Dataset::SPACE
APOS = 
Dataset::APOS
APOS_RE = 
Dataset::APOS_RE
DOUBLE_APOS = 
Dataset::DOUBLE_APOS
FROM = 
Dataset::FROM
TIMESTAMP_FORMAT = 
"TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'".freeze
TIMESTAMP_OFFSET_FORMAT = 
"%+03i:%02i".freeze
BOOL_FALSE = 
"'N'".freeze
BOOL_TRUE = 
"'Y'".freeze
HSTAR = 
"H*".freeze
DUAL = 
' FROM DUAL'.freeze
BITAND_PROC = 
lambda{|a, b| Sequel.lit(["CAST(BITAND(", ", ", ") AS INTEGER)"], a, b)}
SKIP_LOCKED = 
" SKIP LOCKED".freeze

Instance Method Summary collapse

Instance Method Details

#complex_expression_sql_append(sql, op, args) ⇒ Object 



313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 313

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) ⇒ Object

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.



348
349
350
351
352
353
354
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 348

def constant_sql_append(sql, c)
  if c == :CURRENT_TIME
    super(sql, :CURRENT_TIMESTAMP)
  else
    super
  end
end

#empty? ⇒ Boolean

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

Returns:

  • (Boolean) 


364
365
366
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 364

def empty?
  db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil
end

#except(dataset, opts = OPTS) ⇒ Object

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

Raises:



357
358
359
360
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 357

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? ⇒ Boolean

Oracle requires recursive CTEs to have column aliases.

Returns:

  • (Boolean) 


412
413
414
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 412

def recursive_cte_requires_column_aliases?
  true
end

#requires_sql_standard_datetimes? ⇒ Boolean

Oracle requires SQL standard datetimes

Returns:

  • (Boolean) 


369
370
371
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 369

def requires_sql_standard_datetimes?
  true
end

#select_sql ⇒ Object

Handle LIMIT by using a unlimited subselect filtered with ROWNUM.



381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 381

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) ⇒ Object

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.



376
377
378
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 376

def sequence(s)
  clone(:sequence=>s)
end

#server_version ⇒ Object

The version of the database server



486
487
488
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 486

def server_version
  db.server_version(@opts[:server])
end

#supports_cte?(type = :select) ⇒ Boolean

Returns:

  • (Boolean) 


416
417
418
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 416

def supports_cte?(type=:select)
  type == :select
end

#supports_derived_column_lists? ⇒ Boolean

Oracle does not support derived column lists

Returns:

  • (Boolean) 


421
422
423
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 421

def supports_derived_column_lists?
  false
end

#supports_group_cube? ⇒ Boolean

Oracle supports GROUP BY CUBE

Returns:

  • (Boolean) 


426
427
428
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 426

def supports_group_cube?
  true
end

#supports_group_rollup? ⇒ Boolean

Oracle supports GROUP BY ROLLUP

Returns:

  • (Boolean) 


431
432
433
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 431

def supports_group_rollup?
  true
end

#supports_grouping_sets? ⇒ Boolean

Oracle supports GROUPING SETS

Returns:

  • (Boolean) 


436
437
438
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 436

def supports_grouping_sets?
  true
end

#supports_intersect_except_all? ⇒ Boolean

Oracle does not support INTERSECT ALL or EXCEPT ALL

Returns:

  • (Boolean) 


441
442
443
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 441

def supports_intersect_except_all?
  false
end

#supports_is_true? ⇒ Boolean

Oracle does not support IS TRUE.

Returns:

  • (Boolean) 


446
447
448
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 446

def supports_is_true?
  false
end

#supports_limits_in_correlated_subqueries? ⇒ Boolean

Oracle does not support limits in correlated subqueries.

Returns:

  • (Boolean) 


451
452
453
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 451

def supports_limits_in_correlated_subqueries?
  false
end

#supports_offsets_in_correlated_subqueries? ⇒ Boolean

Oracle does not support offsets in correlated subqueries.

Returns:

  • (Boolean) 


456
457
458
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 456

def supports_offsets_in_correlated_subqueries?
  false
end

#supports_regexp? ⇒ Boolean

Oracle supports pattern matching via regular expressions

Returns:

  • (Boolean) 


491
492
493
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 491

def supports_regexp?
  server_version >= 10010002
end

#supports_select_all_and_column? ⇒ Boolean

Oracle does not support SELECT *, column

Returns:

  • (Boolean) 


461
462
463
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 461

def supports_select_all_and_column?
  false
end

#supports_skip_locked? ⇒ Boolean

Oracle supports SKIP LOCKED.

Returns:

  • (Boolean) 


466
467
468
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 466

def supports_skip_locked?
  true
end

#supports_timestamp_timezones? ⇒ Boolean

Oracle supports timezones in literal timestamps.

Returns:

  • (Boolean) 


471
472
473
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 471

def supports_timestamp_timezones?
  true
end

#supports_where_true? ⇒ Boolean

Oracle does not support WHERE ‘Y’ for WHERE TRUE.

Returns:

  • (Boolean) 


476
477
478
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 476

def supports_where_true?
  false
end

#supports_window_functions? ⇒ Boolean

Oracle supports window functions

Returns:

  • (Boolean) 


481
482
483
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 481

def supports_window_functions?
  true
end