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
BITAND_PROC = 
lambda{|a, b| Sequel.lit(["CAST(BITAND(", ", ", ") AS INTEGER)"], a, b)}
SPACE = 
' '.freeze
APOS = 
"'".freeze
APOS_RE = 
/'/.freeze
DOUBLE_APOS = 
"''".freeze
FROM = 
' FROM '.freeze
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
SKIP_LOCKED = 
" SKIP LOCKED".freeze

Instance Method Summary collapse

Instance Method Details

#complex_expression_sql_append(sql, op, args) ⇒ Object 



354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 354

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

#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.



389
390
391
392
393
394
395
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 389

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) 


405
406
407
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 405

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:



398
399
400
401
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 398

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) 


453
454
455
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 453

def recursive_cte_requires_column_aliases?
  true
end

#requires_sql_standard_datetimes? ⇒ Boolean

Oracle requires SQL standard datetimes

Returns:

  • (Boolean) 


410
411
412
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 410

def requires_sql_standard_datetimes?
  true
end

#select_sql ⇒ Object

Handle LIMIT by using a unlimited subselect filtered with ROWNUM.



422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 422

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.



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

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

#server_version ⇒ Object

The version of the database server



527
528
529
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 527

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

#supports_cte?(type = :select) ⇒ Boolean

Returns:

  • (Boolean) 


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

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

#supports_derived_column_lists? ⇒ Boolean

Oracle does not support derived column lists

Returns:

  • (Boolean) 


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

def supports_derived_column_lists?
  false
end

#supports_group_cube? ⇒ Boolean

Oracle supports GROUP BY CUBE

Returns:

  • (Boolean) 


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

def supports_group_cube?
  true
end

#supports_group_rollup? ⇒ Boolean

Oracle supports GROUP BY ROLLUP

Returns:

  • (Boolean) 


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

def supports_group_rollup?
  true
end

#supports_grouping_sets? ⇒ Boolean

Oracle supports GROUPING SETS

Returns:

  • (Boolean) 


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

def supports_grouping_sets?
  true
end

#supports_intersect_except_all? ⇒ Boolean

Oracle does not support INTERSECT ALL or EXCEPT ALL

Returns:

  • (Boolean) 


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

def supports_intersect_except_all?
  false
end

#supports_is_true? ⇒ Boolean

Oracle does not support IS TRUE.

Returns:

  • (Boolean) 


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

def supports_is_true?
  false
end

#supports_limits_in_correlated_subqueries? ⇒ Boolean

Oracle does not support limits in correlated subqueries.

Returns:

  • (Boolean) 


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

def supports_limits_in_correlated_subqueries?
  false
end

#supports_offsets_in_correlated_subqueries? ⇒ Boolean

Oracle does not support offsets in correlated subqueries.

Returns:

  • (Boolean) 


497
498
499
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 497

def supports_offsets_in_correlated_subqueries?
  false
end

#supports_regexp? ⇒ Boolean

Oracle supports pattern matching via regular expressions

Returns:

  • (Boolean) 


532
533
534
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 532

def supports_regexp?
  server_version >= 10010002
end

#supports_select_all_and_column? ⇒ Boolean

Oracle does not support SELECT *, column

Returns:

  • (Boolean) 


502
503
504
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 502

def supports_select_all_and_column?
  false
end

#supports_skip_locked? ⇒ Boolean

Oracle supports SKIP LOCKED.

Returns:

  • (Boolean) 


507
508
509
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 507

def supports_skip_locked?
  true
end

#supports_timestamp_timezones? ⇒ Boolean

Oracle supports timezones in literal timestamps.

Returns:

  • (Boolean) 


512
513
514
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 512

def supports_timestamp_timezones?
  true
end

#supports_where_true? ⇒ Boolean

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

Returns:

  • (Boolean) 


517
518
519
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 517

def supports_where_true?
  false
end

#supports_window_functions? ⇒ Boolean

Oracle supports window functions

Returns:

  • (Boolean) 


522
523
524
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 522

def supports_window_functions?
  true
end