module Sequel::Oracle::DatasetMethods
Constants
- BITAND_PROC
- ROW_NUMBER_EXPRESSION
Public Instance Methods
# 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
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.
# 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
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
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
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
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle
12 is used.
# 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
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
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
# File lib/sequel/adapters/shared/oracle.rb 448 def supports_cte?(type=:select) 449 type == :select 450 end
Oracle
does not support derived column lists
# File lib/sequel/adapters/shared/oracle.rb 453 def supports_derived_column_lists? 454 false 455 end
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
Oracle
supports GROUP BY CUBE
# File lib/sequel/adapters/shared/oracle.rb 464 def supports_group_cube? 465 true 466 end
Oracle
supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/oracle.rb 469 def supports_group_rollup? 470 true 471 end
Oracle
supports GROUPING SETS
# File lib/sequel/adapters/shared/oracle.rb 474 def supports_grouping_sets? 475 true 476 end
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
Oracle
does not support IS TRUE.
# File lib/sequel/adapters/shared/oracle.rb 484 def supports_is_true? 485 false 486 end
Oracle
supports MERGE
# File lib/sequel/adapters/shared/oracle.rb 494 def supports_merge? 495 true 496 end
Oracle
supports NOWAIT.
# File lib/sequel/adapters/shared/oracle.rb 499 def supports_nowait? 500 true 501 end
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
Oracle
does not support SELECT *, column
# File lib/sequel/adapters/shared/oracle.rb 509 def supports_select_all_and_column? 510 false 511 end
Oracle
supports SKIP LOCKED.
# File lib/sequel/adapters/shared/oracle.rb 514 def supports_skip_locked? 515 true 516 end
Oracle
supports timezones in literal timestamps.
# File lib/sequel/adapters/shared/oracle.rb 519 def supports_timestamp_timezones? 520 true 521 end
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
Oracle
supports window functions
# File lib/sequel/adapters/shared/oracle.rb 529 def supports_window_functions? 530 true 531 end
Private Instance Methods
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
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
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, 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
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
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
# File lib/sequel/adapters/shared/oracle.rb 629 def empty_from_sql 630 ' FROM DUAL' 631 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.
# File lib/sequel/adapters/shared/oracle.rb 637 def emulate_function?(name) 638 name == :char_length 639 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.
# 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
If this dataset is associated with a sequence, return the most recently inserted sequence value.
# 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
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
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
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
Oracle
uses 'N' for false values.
# File lib/sequel/adapters/shared/oracle.rb 679 def literal_false 680 "'N'" 681 end
Oracle
uses 'Y' for true values.
# File lib/sequel/adapters/shared/oracle.rb 689 def literal_true 690 "'Y'" 691 end
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
# 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
Use SKIP LOCKED if skipping locked rows.
# 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
Oracle
supports quoted function names.
# File lib/sequel/adapters/shared/oracle.rb 728 def supports_quoted_function_names? 729 true 730 end