module Sequel::Oracle::DatasetMethods

Constants

BITAND_PROC
ROW_NUMBER_EXPRESSION

Public Instance Methods

complex_expression_sql_append(sql, op, args) click to toggle source
Calls superclass method
    # File lib/sequel/adapters/shared/oracle.rb
323 def complex_expression_sql_append(sql, op, args)
324   case op
325   when :&
326     complex_expression_arg_pairs_append(sql, args, &BITAND_PROC)
327   when :|
328     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)}
329   when :^
330     complex_expression_arg_pairs_append(sql, args) do |*x|
331       s1 = complex_expression_arg_pairs(x){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)}
332       s2 = complex_expression_arg_pairs(x, &BITAND_PROC)
333       Sequel.lit(["(", " - ", ")"], s1, s2)
334     end
335   when :~, :'!~', :'~*', :'!~*'
336     raise InvalidOperation, "Pattern matching via regular expressions is not supported in this Oracle version" unless supports_regexp?
337     if op == :'!~' || op == :'!~*'
338       sql << 'NOT '
339     end
340     sql << 'REGEXP_LIKE('
341     literal_append(sql, args[0])
342     sql << ','
343     literal_append(sql, args[1])
344     if op == :'~*' || op == :'!~*'
345       sql << ", 'i'"
346     end
347     sql << ')'
348   when :%, :<<, :>>, :'B~'
349     complex_expression_emulate_append(sql, op, args)
350   else
351     super
352   end
353 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
358 def constant_sql_append(sql, c)
359   if c == :CURRENT_TIME
360     super(sql, :CURRENT_TIMESTAMP)
361   else
362     super
363   end
364 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
374 def empty?
375   db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil
376 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
367 def except(dataset, opts=OPTS)
368   raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all]
369   compound_clone(:minus, dataset, opts)
370 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
442 def recursive_cte_requires_column_aliases?
443   true
444 end
requires_sql_standard_datetimes?() click to toggle source

Oracle requires SQL standard datetimes

    # File lib/sequel/adapters/shared/oracle.rb
379 def requires_sql_standard_datetimes?
380   true
381 end
select_limit_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
425 def select_limit_sql(sql)
426   return unless supports_fetch_next_rows?
427 
428   if offset = @opts[:offset]
429     sql << " OFFSET "
430     literal_append(sql, offset)
431     sql << " ROWS"
432   end
433 
434   if limit = @opts[:limit]
435     sql << " FETCH NEXT "
436     literal_append(sql, limit)
437     sql << " ROWS ONLY"
438   end
439 end
select_sql() click to toggle source

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

Calls superclass method
    # File lib/sequel/adapters/shared/oracle.rb
392 def select_sql
393   return super if @opts[:sql]
394   return super if supports_fetch_next_rows?
395 
396   o = @opts[:offset]
397   if o && o != 0
398     columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns
399     dsa1 = dataset_alias(1)
400     rn = row_number_column
401     limit = @opts[:limit]
402     ds = unlimited.
403       from_self(:alias=>dsa1).
404       select_append(ROW_NUMBER_EXPRESSION.as(rn)).
405       from_self(:alias=>dsa1).
406       select(*columns).
407       where(SQL::Identifier.new(rn) > o)
408     ds = ds.where(SQL::Identifier.new(rn) <= Sequel.+(o, limit)) if limit
409     sql = @opts[:append_sql] || String.new
410     subselect_sql_append(sql, ds)
411     sql
412   elsif limit = @opts[:limit]
413     ds = unlimited
414     # Lock doesn't work in subselects, so don't use a subselect when locking.
415     # Don't use a subselect if custom SQL is used, as it breaks somethings.
416     ds = ds.from_self unless @opts[:lock]
417     sql = @opts[:append_sql] || String.new
418     subselect_sql_append(sql, ds.where(SQL::ComplexExpression.new(:<=, ROW_NUMBER_EXPRESSION, limit)))
419     sql
420   else
421     super
422   end
423 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
386 def sequence(s)
387   clone(:sequence=>s)
388 end
server_version() click to toggle source

The version of the database server

    # File lib/sequel/adapters/shared/oracle.rb
527 def server_version
528   db.server_version(@opts[:server])
529 end
supports_cte?(type=:select) click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
446 def supports_cte?(type=:select)
447   type == :select
448 end
supports_derived_column_lists?() click to toggle source

Oracle does not support derived column lists

    # File lib/sequel/adapters/shared/oracle.rb
451 def supports_derived_column_lists?
452   false
453 end
supports_fetch_next_rows?() click to toggle source

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
457 def supports_fetch_next_rows?
458   server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked])
459 end
supports_group_cube?() click to toggle source

Oracle supports GROUP BY CUBE

    # File lib/sequel/adapters/shared/oracle.rb
462 def supports_group_cube?
463   true
464 end
supports_group_rollup?() click to toggle source

Oracle supports GROUP BY ROLLUP

    # File lib/sequel/adapters/shared/oracle.rb
467 def supports_group_rollup?
468   true
469 end
supports_grouping_sets?() click to toggle source

Oracle supports GROUPING SETS

    # File lib/sequel/adapters/shared/oracle.rb
472 def supports_grouping_sets?
473   true
474 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
477 def supports_intersect_except_all?
478   false
479 end
supports_is_true?() click to toggle source

Oracle does not support IS TRUE.

    # File lib/sequel/adapters/shared/oracle.rb
482 def supports_is_true?
483   false
484 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
487 def supports_limits_in_correlated_subqueries?
488   false
489 end
supports_nowait?() click to toggle source

Oracle supports NOWAIT.

    # File lib/sequel/adapters/shared/oracle.rb
492 def supports_nowait?
493   true
494 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
497 def supports_offsets_in_correlated_subqueries?
498   false
499 end
supports_regexp?() click to toggle source

Oracle 10+ supports pattern matching via regular expressions

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

Oracle does not support SELECT *, column

    # File lib/sequel/adapters/shared/oracle.rb
502 def supports_select_all_and_column?
503   false
504 end
supports_skip_locked?() click to toggle source

Oracle supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/oracle.rb
507 def supports_skip_locked?
508   true
509 end
supports_timestamp_timezones?() click to toggle source

Oracle supports timezones in literal timestamps.

    # File lib/sequel/adapters/shared/oracle.rb
512 def supports_timestamp_timezones?
513   true
514 end
supports_where_true?() click to toggle source

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

    # File lib/sequel/adapters/shared/oracle.rb
517 def supports_where_true?
518   false
519 end
supports_window_functions?() click to toggle source

Oracle supports window functions

    # File lib/sequel/adapters/shared/oracle.rb
522 def supports_window_functions?
523   true
524 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
540 def allow_preparing_prepared_statements?
541   true
542 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. Oracle also does not support derived column lists in aliases.

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

The strftime format to use when literalizing the time.

    # File lib/sequel/adapters/shared/oracle.rb
554 def default_timestamp_format
555   "TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'"
556 end
empty_from_sql() click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
558 def empty_from_sql
559   ' FROM DUAL'
560 end
emulate_function?(name) click to toggle source

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
566 def emulate_function?(name)
567   name == :char_length
568 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
575 def emulate_function_sql_append(sql, f)
576   if f.name == :char_length
577     literal_append(sql, Sequel::SQL::Function.new(:length, Sequel.join([f.args.first, 'x'])) - 1)
578   end
579 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
583 def execute_insert(sql, opts=OPTS)
584   opts = Hash[opts]
585   if f = @opts[:from]
586     opts[:table] = f.first
587   end
588   opts[:sequence] = @opts[:sequence]
589   super
590 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
593 def format_timestamp_offset(hour, minute)
594   sprintf("%+03i:%02i", hour, minute)
595 end
insert_supports_empty_values?() click to toggle source

Oracle doesn't support empty values when inserting.

    # File lib/sequel/adapters/shared/oracle.rb
598 def insert_supports_empty_values?
599   false
600 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
603 def literal_blob_append(sql, v)
604   sql << "'" << v.unpack("H*").first << "'"
605 end
literal_false() click to toggle source

Oracle uses 'N' for false values.

    # File lib/sequel/adapters/shared/oracle.rb
608 def literal_false
609   "'N'"
610 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
613 def literal_string_append(sql, v)
614   sql << "'" << v.gsub("'", "''") << "'"
615 end
literal_true() click to toggle source

Oracle uses 'Y' for true values.

    # File lib/sequel/adapters/shared/oracle.rb
618 def literal_true
619   "'Y'"
620 end
multi_insert_sql_strategy() click to toggle source

Oracle can insert multiple rows using a UNION

    # File lib/sequel/adapters/shared/oracle.rb
623 def multi_insert_sql_strategy
624   :union
625 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
628 def select_lock_sql(sql)
629   super
630 
631   if @opts[:lock]
632     if @opts[:skip_locked]
633       sql << " SKIP LOCKED"
634     elsif @opts[:nowait]
635       sql << " NOWAIT"
636     end
637   end
638 end
supports_quoted_function_names?() click to toggle source

Oracle supports quoted function names.

    # File lib/sequel/adapters/shared/oracle.rb
641 def supports_quoted_function_names?
642   true
643 end