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

Oracle requires SQL standard datetimes

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

The version of the database server

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

Oracle does not support derived column lists

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

Oracle supports GROUP BY CUBE

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

Oracle supports GROUP BY ROLLUP

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

Oracle supports GROUPING SETS

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

Oracle does not support IS TRUE.

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

Oracle supports MERGE

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

Oracle supports NOWAIT.

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

Oracle 10+ supports pattern matching via regular expressions

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

Oracle does not support SELECT *, column

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

Oracle supports SKIP LOCKED.

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

Oracle supports timezones in literal timestamps.

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

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

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

Oracle supports window functions

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

Handle Oracle’s non standard MERGE syntax

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

The strftime format to use when literalizing the time.

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

Oracle doesn’t support empty values when inserting.

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

Oracle uses ‘N’ for false values.

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

Oracle uses ‘Y’ for true values.

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

Oracle can insert multiple rows using a UNION

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

Oracle supports quoted function names.

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