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

Oracle requires SQL standard datetimes

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

The version of the database server

    # File lib/sequel/adapters/shared/oracle.rb
538 def server_version
539   db.server_version(@opts[:server])
540 end
supports_cte?(type=:select) click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
452 def supports_cte?(type=:select)
453   type == :select
454 end
supports_derived_column_lists?() click to toggle source

Oracle does not support derived column lists

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

Oracle supports GROUP BY CUBE

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

Oracle supports GROUP BY ROLLUP

    # File lib/sequel/adapters/shared/oracle.rb
473 def supports_group_rollup?
474   true
475 end
supports_grouping_sets?() click to toggle source

Oracle supports GROUPING SETS

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

Oracle does not support IS TRUE.

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

Oracle supports MERGE

    # File lib/sequel/adapters/shared/oracle.rb
498 def supports_merge?
499   true
500 end
supports_nowait?() click to toggle source

Oracle supports NOWAIT.

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

Oracle 10+ supports pattern matching via regular expressions

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

Oracle does not support SELECT *, column

    # File lib/sequel/adapters/shared/oracle.rb
513 def supports_select_all_and_column?
514   false
515 end
supports_skip_locked?() click to toggle source

Oracle supports SKIP LOCKED.

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

Oracle supports timezones in literal timestamps.

    # File lib/sequel/adapters/shared/oracle.rb
523 def supports_timestamp_timezones?
524   true
525 end
supports_where_true?() click to toggle source

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

    # File lib/sequel/adapters/shared/oracle.rb
528 def supports_where_true?
529   false
530 end
supports_window_functions?() click to toggle source

Oracle supports window functions

    # File lib/sequel/adapters/shared/oracle.rb
533 def supports_window_functions?
534   true
535 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
606 def _merge_when_conditions_sql(sql, data)
607   if data.has_key?(:conditions)
608     sql << " WHERE "
609     literal_append(sql, _normalize_merge_when_conditions(data[:conditions]))
610   end
611 end
_merge_when_sql(sql) click to toggle source

Handle Oracle’s non standard MERGE syntax

    # File lib/sequel/adapters/shared/oracle.rb
565 def _merge_when_sql(sql)
566   raise Error, "no WHEN [NOT] MATCHED clauses provided for MERGE" unless merge_when = @opts[:merge_when]
567   insert = update = delete = nil
568   types = merge_when.map{|d| d[:type]}
569   raise Error, "Oracle does not support multiple INSERT, UPDATE, or DELETE clauses in MERGE" if types != types.uniq
570 
571   merge_when.each do |data|
572     case data[:type]
573     when :insert
574       insert = data
575     when :update
576       update = data
577     else # when :delete
578       delete = data
579     end
580   end
581 
582   if delete
583     raise Error, "Oracle does not support DELETE without UPDATE clause in MERGE" unless update
584     raise Error, "Oracle does not support DELETE without conditions clause in MERGE" unless delete.has_key?(:conditions)
585   end
586 
587   if update
588     sql << " WHEN MATCHED"
589     _merge_update_sql(sql, update)
590     _merge_when_conditions_sql(sql, update)
591 
592     if delete
593       sql << " DELETE"
594       _merge_when_conditions_sql(sql, delete)
595     end
596   end
597 
598   if insert
599     sql << " WHEN NOT MATCHED"
600     _merge_insert_sql(sql, insert)
601     _merge_when_conditions_sql(sql, insert)
602   end
603 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
551 def _normalize_merge_when_conditions(conditions)
552   case conditions
553   when nil, false
554     {1=>0}
555   when true
556     {1=>1}
557   when Sequel::SQL::DelayedEvaluation
558     Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))}
559   else
560     conditions
561   end
562 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
615 def allow_preparing_prepared_statements?
616   true
617 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
622 def as_sql_append(sql, aliaz, column_aliases=nil)
623   raise Error, "oracle does not support derived column lists" if column_aliases
624   sql << ' '
625   quote_identifier_append(sql, aliaz)
626 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

    # File lib/sequel/adapters/shared/oracle.rb
629 def default_timestamp_format
630   "'%Y-%m-%d %H:%M:%S.%6N %:z'"
631 end
empty_from_sql() click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
633 def empty_from_sql
634   ' FROM DUAL'
635 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
641 def emulate_function?(name)
642   name == :char_length
643 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
650 def emulate_function_sql_append(sql, f)
651   if f.name == :char_length
652     literal_append(sql, Sequel::SQL::Function.new(:length, Sequel.join([f.args.first, 'x'])) - 1)
653   end
654 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
658 def execute_insert(sql, opts=OPTS)
659   opts = Hash[opts]
660   if f = @opts[:from]
661     opts[:table] = f.first
662   end
663   opts[:sequence] = @opts[:sequence]
664   super
665 end
insert_supports_empty_values?() click to toggle source

Oracle doesn’t support empty values when inserting.

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

Oracle uses ‘N’ for false values.

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

Oracle uses ‘Y’ for true values.

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

Oracle can insert multiple rows using a UNION

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

Oracle supports quoted function names.

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