module Sequel::Oracle::DatasetMethods
Constants
- BITAND_PROC
- ROW_NUMBER_EXPRESSION
Public Instance Methods
Source
# 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
Source
# 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
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.
Source
# 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
Use a custom expression with EXISTS to determine whether a dataset is empty.
Source
# 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
Oracle
uses MINUS instead of EXCEPT, and doesn’t support EXCEPT ALL
Source
# File lib/sequel/adapters/shared/oracle.rb 447 def recursive_cte_requires_column_aliases? 448 true 449 end
Oracle
requires recursive CTEs to have column aliases.
Source
Source
# 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
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle
12 is used.
Source
# File lib/sequel/adapters/shared/oracle.rb 407 def sequence(s) 408 clone(:sequence=>s) 409 end
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.
Source
# File lib/sequel/adapters/shared/oracle.rb 537 def server_version 538 db.server_version(@opts[:server]) 539 end
The version of the database server
Source
# File lib/sequel/adapters/shared/oracle.rb 451 def supports_cte?(type=:select) 452 type == :select 453 end
Source
# File lib/sequel/adapters/shared/oracle.rb 456 def supports_derived_column_lists? 457 false 458 end
Oracle
does not support derived column lists
Source
# File lib/sequel/adapters/shared/oracle.rb 462 def supports_fetch_next_rows? 463 server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked]) 464 end
Oracle
supports FETCH NEXT ROWS since 12c, but it doesn’t work when locking or when skipping locked rows.
Source
# File lib/sequel/adapters/shared/oracle.rb 467 def supports_group_cube? 468 true 469 end
Oracle
supports GROUP BY CUBE
Source
# File lib/sequel/adapters/shared/oracle.rb 472 def supports_group_rollup? 473 true 474 end
Oracle
supports GROUP BY ROLLUP
Source
# File lib/sequel/adapters/shared/oracle.rb 477 def supports_grouping_sets? 478 true 479 end
Oracle
supports GROUPING SETS
Source
# File lib/sequel/adapters/shared/oracle.rb 482 def supports_intersect_except_all? 483 false 484 end
Oracle
does not support INTERSECT ALL or EXCEPT ALL
Source
# File lib/sequel/adapters/shared/oracle.rb 487 def supports_is_true? 488 false 489 end
Oracle
does not support IS TRUE.
Source
# File lib/sequel/adapters/shared/oracle.rb 497 def supports_merge? 498 true 499 end
Oracle
supports MERGE
Source
# File lib/sequel/adapters/shared/oracle.rb 502 def supports_nowait? 503 true 504 end
Oracle
supports NOWAIT.
Source
# File lib/sequel/adapters/shared/oracle.rb 542 def supports_regexp? 543 server_version >= 10010002 544 end
Oracle
10+ supports pattern matching via regular expressions
Source
# File lib/sequel/adapters/shared/oracle.rb 512 def supports_select_all_and_column? 513 false 514 end
Oracle
does not support SELECT *, column
Source
# File lib/sequel/adapters/shared/oracle.rb 517 def supports_skip_locked? 518 true 519 end
Oracle
supports SKIP LOCKED.
Source
# File lib/sequel/adapters/shared/oracle.rb 522 def supports_timestamp_timezones? 523 true 524 end
Oracle
supports timezones in literal timestamps.
Source
# File lib/sequel/adapters/shared/oracle.rb 527 def supports_where_true? 528 false 529 end
Oracle
does not support WHERE ‘Y’ for WHERE TRUE.
Source
# File lib/sequel/adapters/shared/oracle.rb 532 def supports_window_functions? 533 true 534 end
Oracle
supports window functions
Private Instance Methods
Source
# 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
Handle Oracle’s non-standard MERGE WHEN condition syntax.
Source
# 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
Handle Oracle’s non standard MERGE syntax
Source
# 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
Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.
Source
# File lib/sequel/adapters/shared/oracle.rb 614 def allow_preparing_prepared_statements? 615 true 616 end
Allow preparing prepared statements, since determining the prepared sql to use for a prepared statement requires calling prepare on that statement.
Source
# 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
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.
Source
# File lib/sequel/adapters/shared/oracle.rb 628 def default_timestamp_format 629 "'%Y-%m-%d %H:%M:%S.%6N %:z'" 630 end
The strftime format to use when literalizing the time.
Source
# File lib/sequel/adapters/shared/oracle.rb 632 def empty_from_sql 633 ' FROM DUAL' 634 end
Source
# File lib/sequel/adapters/shared/oracle.rb 640 def emulate_function?(name) 641 name == :char_length 642 end
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.
Source
# 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
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.
Source
# 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
If this dataset is associated with a sequence, return the most recently inserted sequence value.
Source
# File lib/sequel/adapters/shared/oracle.rb 667 def insert_supports_empty_values? 668 false 669 end
Oracle
doesn’t support empty values when inserting.
Source
# File lib/sequel/adapters/shared/oracle.rb 672 def literal_blob_append(sql, v) 673 sql << "'" << v.unpack("H*").first << "'" 674 end
Use string in hex format for blob data.
Source
# File lib/sequel/adapters/shared/oracle.rb 677 def literal_false 678 "'N'" 679 end
Oracle
uses ‘N’ for false values.
Source
Source
# File lib/sequel/adapters/shared/oracle.rb 687 def literal_true 688 "'Y'" 689 end
Oracle
uses ‘Y’ for true values.
Source
# File lib/sequel/adapters/shared/oracle.rb 692 def multi_insert_sql_strategy 693 :union 694 end
Oracle
can insert multiple rows using a UNION
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
Source
# 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
Use SKIP LOCKED if skipping locked rows.
Source
# File lib/sequel/adapters/shared/oracle.rb 726 def supports_quoted_function_names? 727 true 728 end
Oracle
supports quoted function names.