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
336 def complex_expression_sql_append(sql, op, args)
337   case op
338   when :&
339     complex_expression_arg_pairs_append(sql, args, &BITAND_PROC)
340   when :|
341     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)}
342   when :^
343     complex_expression_arg_pairs_append(sql, args) do |*x|
344       s1 = complex_expression_arg_pairs(x){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)}
345       s2 = complex_expression_arg_pairs(x, &BITAND_PROC)
346       Sequel.lit(["(", " - ", ")"], s1, s2)
347     end
348   when :~, :'!~', :'~*', :'!~*'
349     raise InvalidOperation, "Pattern matching via regular expressions is not supported in this Oracle version" unless supports_regexp?
350     if op == :'!~' || op == :'!~*'
351       sql << 'NOT '
352     end
353     sql << 'REGEXP_LIKE('
354     literal_append(sql, args[0])
355     sql << ','
356     literal_append(sql, args[1])
357     if op == :'~*' || op == :'!~*'
358       sql << ", 'i'"
359     end
360     sql << ')'
361   when :%, :<<, :>>, :'B~'
362     complex_expression_emulate_append(sql, op, args)
363   else
364     super
365   end
366 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
371 def constant_sql_append(sql, c)
372   if c == :CURRENT_TIME
373     super(sql, :CURRENT_TIMESTAMP)
374   else
375     super
376   end
377 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
387 def empty?
388   if @opts[:sql]
389     naked.each{return false}
390     true
391   else
392     db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil
393   end
394 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
380 def except(dataset, opts=OPTS)
381   raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all]
382   compound_clone(:minus, dataset, opts)
383 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
444 def recursive_cte_requires_column_aliases?
445   true
446 end
requires_sql_standard_datetimes?() click to toggle source

Oracle requires SQL standard datetimes

    # File lib/sequel/adapters/shared/oracle.rb
397 def requires_sql_standard_datetimes?
398   true
399 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
410 def select_sql
411   return super if @opts[:sql]
412   return super if supports_fetch_next_rows?
413 
414   o = @opts[:offset]
415   if o && o != 0
416     columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns
417     dsa1 = dataset_alias(1)
418     rn = row_number_column
419     limit = @opts[:limit]
420     ds = unlimited.
421       from_self(:alias=>dsa1).
422       select_append(ROW_NUMBER_EXPRESSION.as(rn)).
423       from_self(:alias=>dsa1).
424       select(*columns).
425       where(SQL::Identifier.new(rn) > o)
426     ds = ds.where(SQL::Identifier.new(rn) <= Sequel.+(o, limit)) if limit
427     sql = @opts[:append_sql] || String.new
428     subselect_sql_append(sql, ds)
429     sql
430   elsif limit = @opts[:limit]
431     ds = unlimited
432     # Lock doesn't work in subselects, so don't use a subselect when locking.
433     # Don't use a subselect if custom SQL is used, as it breaks somethings.
434     ds = ds.from_self unless @opts[:lock]
435     sql = @opts[:append_sql] || String.new
436     subselect_sql_append(sql, ds.where(SQL::ComplexExpression.new(:<=, ROW_NUMBER_EXPRESSION, limit)))
437     sql
438   else
439     super
440   end
441 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
404 def sequence(s)
405   clone(:sequence=>s)
406 end
server_version() click to toggle source

The version of the database server

    # File lib/sequel/adapters/shared/oracle.rb
534 def server_version
535   db.server_version(@opts[:server])
536 end
supports_cte?(type=:select) click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
448 def supports_cte?(type=:select)
449   type == :select
450 end
supports_derived_column_lists?() click to toggle source

Oracle does not support derived column lists

    # File lib/sequel/adapters/shared/oracle.rb
453 def supports_derived_column_lists?
454   false
455 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
459 def supports_fetch_next_rows?
460   server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked])
461 end
supports_group_cube?() click to toggle source

Oracle supports GROUP BY CUBE

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

Oracle supports GROUP BY ROLLUP

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

Oracle supports GROUPING SETS

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

Oracle does not support IS TRUE.

    # File lib/sequel/adapters/shared/oracle.rb
484 def supports_is_true?
485   false
486 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
489 def supports_limits_in_correlated_subqueries?
490   false
491 end
supports_merge?() click to toggle source

Oracle supports MERGE

    # File lib/sequel/adapters/shared/oracle.rb
494 def supports_merge?
495   true
496 end
supports_nowait?() click to toggle source

Oracle supports NOWAIT.

    # File lib/sequel/adapters/shared/oracle.rb
499 def supports_nowait?
500   true
501 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
504 def supports_offsets_in_correlated_subqueries?
505   false
506 end
supports_regexp?() click to toggle source

Oracle 10+ supports pattern matching via regular expressions

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

Oracle does not support SELECT *, column

    # File lib/sequel/adapters/shared/oracle.rb
509 def supports_select_all_and_column?
510   false
511 end
supports_skip_locked?() click to toggle source

Oracle supports SKIP LOCKED.

    # File lib/sequel/adapters/shared/oracle.rb
514 def supports_skip_locked?
515   true
516 end
supports_timestamp_timezones?() click to toggle source

Oracle supports timezones in literal timestamps.

    # File lib/sequel/adapters/shared/oracle.rb
519 def supports_timestamp_timezones?
520   true
521 end
supports_where_true?() click to toggle source

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

    # File lib/sequel/adapters/shared/oracle.rb
524 def supports_where_true?
525   false
526 end
supports_window_functions?() click to toggle source

Oracle supports window functions

    # File lib/sequel/adapters/shared/oracle.rb
529 def supports_window_functions?
530   true
531 end

Private Instance Methods

_merge_when_conditions_sql(sql, data) click to toggle source

Handle Oracle's non-standard MERGE WHEN condition syntax.

    # File lib/sequel/adapters/shared/oracle.rb
602 def _merge_when_conditions_sql(sql, data)
603   if data.has_key?(:conditions)
604     sql << " WHERE "
605     literal_append(sql, _normalize_merge_when_conditions(data[:conditions]))
606   end
607 end
_merge_when_sql(sql) click to toggle source

Handle Oracle's non standard MERGE syntax

    # File lib/sequel/adapters/shared/oracle.rb
561 def _merge_when_sql(sql)
562   raise Error, "no WHEN [NOT] MATCHED clauses provided for MERGE" unless merge_when = @opts[:merge_when]
563   insert = update = delete = nil
564   types = merge_when.map{|d| d[:type]}
565   raise Error, "Oracle does not support multiple INSERT, UPDATE, or DELETE clauses in MERGE" if types != types.uniq
566 
567   merge_when.each do |data|
568     case data[:type]
569     when :insert
570       insert = data
571     when :update
572       update = data
573     else # when :delete
574       delete = data
575     end
576   end
577 
578   if delete
579     raise Error, "Oracle does not support DELETE without UPDATE clause in MERGE" unless update
580     raise Error, "Oracle does not support DELETE without conditions clause in MERGE" unless delete.has_key?(:conditions)
581   end
582 
583   if update
584     sql << " WHEN MATCHED"
585     _merge_update_sql(sql, update)
586     _merge_when_conditions_sql(sql, update)
587 
588     if delete
589       sql << " DELETE"
590       _merge_when_conditions_sql(sql, delete)
591     end
592   end
593 
594   if insert
595     sql << " WHEN NOT MATCHED"
596     _merge_insert_sql(sql, insert)
597     _merge_when_conditions_sql(sql, insert)
598   end
599 end
_normalize_merge_when_conditions(conditions) click to toggle source

Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.

    # File lib/sequel/adapters/shared/oracle.rb
547 def _normalize_merge_when_conditions(conditions)
548   case conditions
549   when nil, false
550     {1=>0}
551   when true
552     {1=>1}
553   when Sequel::SQL::DelayedEvaluation
554     Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))}
555   else
556     conditions
557   end
558 end
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
611 def allow_preparing_prepared_statements?
612   true
613 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
618 def as_sql_append(sql, aliaz, column_aliases=nil)
619   raise Error, "oracle does not support derived column lists" if column_aliases
620   sql << ' '
621   quote_identifier_append(sql, aliaz)
622 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

    # File lib/sequel/adapters/shared/oracle.rb
625 def default_timestamp_format
626   "TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'"
627 end
empty_from_sql() click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
629 def empty_from_sql
630   ' FROM DUAL'
631 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
637 def emulate_function?(name)
638   name == :char_length
639 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
646 def emulate_function_sql_append(sql, f)
647   if f.name == :char_length
648     literal_append(sql, Sequel::SQL::Function.new(:length, Sequel.join([f.args.first, 'x'])) - 1)
649   end
650 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
654 def execute_insert(sql, opts=OPTS)
655   opts = Hash[opts]
656   if f = @opts[:from]
657     opts[:table] = f.first
658   end
659   opts[:sequence] = @opts[:sequence]
660   super
661 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
664 def format_timestamp_offset(hour, minute)
665   sprintf("%+03i:%02i", hour, minute)
666 end
insert_supports_empty_values?() click to toggle source

Oracle doesn't support empty values when inserting.

    # File lib/sequel/adapters/shared/oracle.rb
669 def insert_supports_empty_values?
670   false
671 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
674 def literal_blob_append(sql, v)
675   sql << "'" << v.unpack("H*").first << "'"
676 end
literal_false() click to toggle source

Oracle uses 'N' for false values.

    # File lib/sequel/adapters/shared/oracle.rb
679 def literal_false
680   "'N'"
681 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
684 def literal_string_append(sql, v)
685   sql << "'" << v.gsub("'", "''") << "'"
686 end
literal_true() click to toggle source

Oracle uses 'Y' for true values.

    # File lib/sequel/adapters/shared/oracle.rb
689 def literal_true
690   "'Y'"
691 end
multi_insert_sql_strategy() click to toggle source

Oracle can insert multiple rows using a UNION

    # File lib/sequel/adapters/shared/oracle.rb
694 def multi_insert_sql_strategy
695   :union
696 end
select_limit_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
698 def select_limit_sql(sql)
699   return unless supports_fetch_next_rows?
700 
701   if offset = @opts[:offset]
702     sql << " OFFSET "
703     literal_append(sql, offset)
704     sql << " ROWS"
705   end
706 
707   if limit = @opts[:limit]
708     sql << " FETCH NEXT "
709     literal_append(sql, limit)
710     sql << " ROWS ONLY"
711   end
712 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
715 def select_lock_sql(sql)
716   super
717 
718   if @opts[:lock]
719     if @opts[:skip_locked]
720       sql << " SKIP LOCKED"
721     elsif @opts[:nowait]
722       sql << " NOWAIT"
723     end
724   end
725 end
supports_quoted_function_names?() click to toggle source

Oracle supports quoted function names.

    # File lib/sequel/adapters/shared/oracle.rb
728 def supports_quoted_function_names?
729   true
730 end