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
329 def complex_expression_sql_append(sql, op, args)
330   case op
331   when :&
332     complex_expression_arg_pairs_append(sql, args, &BITAND_PROC)
333   when :|
334     complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)}
335   when :^
336     complex_expression_arg_pairs_append(sql, args) do |*x|
337       s1 = complex_expression_arg_pairs(x){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)}
338       s2 = complex_expression_arg_pairs(x, &BITAND_PROC)
339       Sequel.lit(["(", " - ", ")"], s1, s2)
340     end
341   when :~, :'!~', :'~*', :'!~*'
342     raise InvalidOperation, "Pattern matching via regular expressions is not supported in this Oracle version" unless supports_regexp?
343     if op == :'!~' || op == :'!~*'
344       sql << 'NOT '
345     end
346     sql << 'REGEXP_LIKE('
347     literal_append(sql, args[0])
348     sql << ','
349     literal_append(sql, args[1])
350     if op == :'~*' || op == :'!~*'
351       sql << ", 'i'"
352     end
353     sql << ')'
354   when :%, :<<, :>>, :'B~'
355     complex_expression_emulate_append(sql, op, args)
356   else
357     super
358   end
359 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
364 def constant_sql_append(sql, c)
365   if c == :CURRENT_TIME
366     super(sql, :CURRENT_TIMESTAMP)
367   else
368     super
369   end
370 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
380 def empty?
381   db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil
382 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
373 def except(dataset, opts=OPTS)
374   raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all]
375   compound_clone(:minus, dataset, opts)
376 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
432 def recursive_cte_requires_column_aliases?
433   true
434 end
requires_sql_standard_datetimes?() click to toggle source

Oracle requires SQL standard datetimes

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

The version of the database server

    # File lib/sequel/adapters/shared/oracle.rb
522 def server_version
523   db.server_version(@opts[:server])
524 end
supports_cte?(type=:select) click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
436 def supports_cte?(type=:select)
437   type == :select
438 end
supports_derived_column_lists?() click to toggle source

Oracle does not support derived column lists

    # File lib/sequel/adapters/shared/oracle.rb
441 def supports_derived_column_lists?
442   false
443 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
447 def supports_fetch_next_rows?
448   server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked])
449 end
supports_group_cube?() click to toggle source

Oracle supports GROUP BY CUBE

    # File lib/sequel/adapters/shared/oracle.rb
452 def supports_group_cube?
453   true
454 end
supports_group_rollup?() click to toggle source

Oracle supports GROUP BY ROLLUP

    # File lib/sequel/adapters/shared/oracle.rb
457 def supports_group_rollup?
458   true
459 end
supports_grouping_sets?() click to toggle source

Oracle supports GROUPING SETS

    # File lib/sequel/adapters/shared/oracle.rb
462 def supports_grouping_sets?
463   true
464 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
467 def supports_intersect_except_all?
468   false
469 end
supports_is_true?() click to toggle source

Oracle does not support IS TRUE.

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

Oracle supports MERGE

    # File lib/sequel/adapters/shared/oracle.rb
482 def supports_merge?
483   true
484 end
supports_nowait?() click to toggle source

Oracle supports NOWAIT.

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

Oracle 10+ supports pattern matching via regular expressions

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

Oracle does not support SELECT *, column

    # File lib/sequel/adapters/shared/oracle.rb
497 def supports_select_all_and_column?
498   false
499 end
supports_skip_locked?() click to toggle source

Oracle supports SKIP LOCKED.

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

Oracle supports timezones in literal timestamps.

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

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

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

Oracle supports window functions

    # File lib/sequel/adapters/shared/oracle.rb
517 def supports_window_functions?
518   true
519 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
590 def _merge_when_conditions_sql(sql, data)
591   if data.has_key?(:conditions)
592     sql << " WHERE "
593     literal_append(sql, _normalize_merge_when_conditions(data[:conditions]))
594   end
595 end
_merge_when_sql(sql) click to toggle source

Handle Oracle's non standard MERGE syntax

    # File lib/sequel/adapters/shared/oracle.rb
549 def _merge_when_sql(sql)
550   raise Error, "no WHEN [NOT] MATCHED clauses provided for MERGE" unless merge_when = @opts[:merge_when]
551   insert = update = delete = nil
552   types = merge_when.map{|d| d[:type]}
553   raise Error, "Oracle does not support multiple INSERT, UPDATE, or DELETE clauses in MERGE" if types != types.uniq
554 
555   merge_when.each do |data|
556     case data[:type]
557     when :insert
558       insert = data
559     when :update
560       update = data
561     else # when :delete
562       delete = data
563     end
564   end
565 
566   if delete
567     raise Error, "Oracle does not support DELETE without UPDATE clause in MERGE" unless update
568     raise Error, "Oracle does not support DELETE without conditions clause in MERGE" unless delete.has_key?(:conditions)
569   end
570 
571   if update
572     sql << " WHEN MATCHED"
573     _merge_update_sql(sql, update)
574     _merge_when_conditions_sql(sql, update)
575 
576     if delete
577       sql << " DELETE"
578       _merge_when_conditions_sql(sql, delete)
579     end
580   end
581 
582   if insert
583     sql << " WHEN NOT MATCHED"
584     _merge_insert_sql(sql, insert)
585     _merge_when_conditions_sql(sql, insert)
586   end
587 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
535 def _normalize_merge_when_conditions(conditions)
536   case conditions
537   when nil, false
538     {1=>0}
539   when true
540     {1=>1}
541   when Sequel::SQL::DelayedEvaluation
542     Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))}
543   else
544     conditions
545   end
546 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
599 def allow_preparing_prepared_statements?
600   true
601 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
606 def as_sql_append(sql, aliaz, column_aliases=nil)
607   raise Error, "oracle does not support derived column lists" if column_aliases
608   sql << ' '
609   quote_identifier_append(sql, aliaz)
610 end
default_timestamp_format() click to toggle source

The strftime format to use when literalizing the time.

    # File lib/sequel/adapters/shared/oracle.rb
613 def default_timestamp_format
614   "TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'"
615 end
empty_from_sql() click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
617 def empty_from_sql
618   ' FROM DUAL'
619 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
625 def emulate_function?(name)
626   name == :char_length
627 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
634 def emulate_function_sql_append(sql, f)
635   if f.name == :char_length
636     literal_append(sql, Sequel::SQL::Function.new(:length, Sequel.join([f.args.first, 'x'])) - 1)
637   end
638 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
642 def execute_insert(sql, opts=OPTS)
643   opts = Hash[opts]
644   if f = @opts[:from]
645     opts[:table] = f.first
646   end
647   opts[:sequence] = @opts[:sequence]
648   super
649 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
652 def format_timestamp_offset(hour, minute)
653   sprintf("%+03i:%02i", hour, minute)
654 end
insert_supports_empty_values?() click to toggle source

Oracle doesn't support empty values when inserting.

    # File lib/sequel/adapters/shared/oracle.rb
657 def insert_supports_empty_values?
658   false
659 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
662 def literal_blob_append(sql, v)
663   sql << "'" << v.unpack("H*").first << "'"
664 end
literal_false() click to toggle source

Oracle uses 'N' for false values.

    # File lib/sequel/adapters/shared/oracle.rb
667 def literal_false
668   "'N'"
669 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
672 def literal_string_append(sql, v)
673   sql << "'" << v.gsub("'", "''") << "'"
674 end
literal_true() click to toggle source

Oracle uses 'Y' for true values.

    # File lib/sequel/adapters/shared/oracle.rb
677 def literal_true
678   "'Y'"
679 end
multi_insert_sql_strategy() click to toggle source

Oracle can insert multiple rows using a UNION

    # File lib/sequel/adapters/shared/oracle.rb
682 def multi_insert_sql_strategy
683   :union
684 end
select_limit_sql(sql) click to toggle source
    # File lib/sequel/adapters/shared/oracle.rb
686 def select_limit_sql(sql)
687   return unless supports_fetch_next_rows?
688 
689   if offset = @opts[:offset]
690     sql << " OFFSET "
691     literal_append(sql, offset)
692     sql << " ROWS"
693   end
694 
695   if limit = @opts[:limit]
696     sql << " FETCH NEXT "
697     literal_append(sql, limit)
698     sql << " ROWS ONLY"
699   end
700 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
703 def select_lock_sql(sql)
704   super
705 
706   if @opts[:lock]
707     if @opts[:skip_locked]
708       sql << " SKIP LOCKED"
709     elsif @opts[:nowait]
710       sql << " NOWAIT"
711     end
712   end
713 end
supports_quoted_function_names?() click to toggle source

Oracle supports quoted function names.

    # File lib/sequel/adapters/shared/oracle.rb
716 def supports_quoted_function_names?
717   true
718 end