Module: Sequel::Oracle::DatasetMethods

Includes:
AutoCastDateAndTime
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)}

Instance Method Summary collapse

Instance Method Details

#complex_expression_sql_append(sql, op, args) ⇒ Object

[View source]

340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 340

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.

[View source]

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

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)
[View source]

391
392
393
394
395
396
397
398
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 391

def empty?
  if @opts[:sql]
    naked.each{return false}
    true
  else
    db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil
  end
end

#except(dataset, opts = OPTS) ⇒ Object

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

Raises:

[View source]

384
385
386
387
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 384

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)
[View source]

448
449
450
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 448

def recursive_cte_requires_column_aliases?
  true
end

#requires_sql_standard_datetimes? ⇒ Boolean

Oracle requires SQL standard datetimes

Returns:

  • (Boolean)
[View source]

401
402
403
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 401

def requires_sql_standard_datetimes?
  true
end

#select_sql ⇒ Object

Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle 12 is used.

[View source]

414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 414

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

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

[View source]

408
409
410
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 408

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

#server_version ⇒ Object

The version of the database server

[View source]

538
539
540
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 538

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

#supports_cte?(type = :select) ⇒ Boolean

Returns:

  • (Boolean)
[View source]

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

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

#supports_derived_column_lists? ⇒ Boolean

Oracle does not support derived column lists

Returns:

  • (Boolean)
[View source]

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

def supports_derived_column_lists?
  false
end

#supports_fetch_next_rows? ⇒ Boolean

Oracle supports FETCH NEXT ROWS since 12c, but it doesn’t work when locking or when skipping locked rows.

Returns:

  • (Boolean)
[View source]

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

def supports_fetch_next_rows?
  server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked])
end

#supports_group_cube? ⇒ Boolean

Oracle supports GROUP BY CUBE

Returns:

  • (Boolean)
[View source]

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

def supports_group_cube?
  true
end

#supports_group_rollup? ⇒ Boolean

Oracle supports GROUP BY ROLLUP

Returns:

  • (Boolean)
[View source]

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

def supports_group_rollup?
  true
end

#supports_grouping_sets? ⇒ Boolean

Oracle supports GROUPING SETS

Returns:

  • (Boolean)
[View source]

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

def supports_grouping_sets?
  true
end

#supports_intersect_except_all? ⇒ Boolean

Oracle does not support INTERSECT ALL or EXCEPT ALL

Returns:

  • (Boolean)
[View source]

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

def supports_intersect_except_all?
  false
end

#supports_is_true? ⇒ Boolean

Oracle does not support IS TRUE.

Returns:

  • (Boolean)
[View source]

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

def supports_is_true?
  false
end

#supports_limits_in_correlated_subqueries? ⇒ Boolean

Oracle does not support limits in correlated subqueries.

Returns:

  • (Boolean)
[View source]

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

def supports_limits_in_correlated_subqueries?
  false
end

#supports_merge? ⇒ Boolean

Oracle supports MERGE

Returns:

  • (Boolean)
[View source]

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

def supports_merge?
  true
end

#supports_nowait? ⇒ Boolean

Oracle supports NOWAIT.

Returns:

  • (Boolean)
[View source]

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

def supports_nowait?
  true
end

#supports_offsets_in_correlated_subqueries? ⇒ Boolean

Oracle does not support offsets in correlated subqueries.

Returns:

  • (Boolean)
[View source]

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

def supports_offsets_in_correlated_subqueries?
  false
end

#supports_regexp? ⇒ Boolean

Oracle 10+ supports pattern matching via regular expressions

Returns:

  • (Boolean)
[View source]

543
544
545
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 543

def supports_regexp?
  server_version >= 10010002
end

#supports_select_all_and_column? ⇒ Boolean

Oracle does not support SELECT *, column

Returns:

  • (Boolean)
[View source]

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

def supports_select_all_and_column?
  false
end

#supports_skip_locked? ⇒ Boolean

Oracle supports SKIP LOCKED.

Returns:

  • (Boolean)
[View source]

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

def supports_skip_locked?
  true
end

#supports_timestamp_timezones? ⇒ Boolean

Oracle supports timezones in literal timestamps.

Returns:

  • (Boolean)
[View source]

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

def supports_timestamp_timezones?
  true
end

#supports_where_true? ⇒ Boolean

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

Returns:

  • (Boolean)
[View source]

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

def supports_where_true?
  false
end

#supports_window_functions? ⇒ Boolean

Oracle supports window functions

Returns:

  • (Boolean)
[View source]

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

def supports_window_functions?
  true
end