module Sequel::Oracle::DatasetMethods
Constants
- BITAND_PROC
- ROW_NUMBER_EXPRESSION
Public Instance Methods
Source
# 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
Source
# 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
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 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
Use a custom expression with EXISTS to determine whether a dataset is empty.
Source
# 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
Oracle
uses MINUS instead of EXCEPT, and doesn’t support EXCEPT ALL
Source
# File lib/sequel/adapters/shared/oracle.rb 448 def recursive_cte_requires_column_aliases? 449 true 450 end
Oracle
requires recursive CTEs to have column aliases.
Source
Source
# 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
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle
12 is used.
Source
# File lib/sequel/adapters/shared/oracle.rb 408 def sequence(s) 409 clone(:sequence=>s) 410 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 538 def server_version 539 db.server_version(@opts[:server]) 540 end
The version of the database server
Source
# File lib/sequel/adapters/shared/oracle.rb 452 def supports_cte?(type=:select) 453 type == :select 454 end
Source
# File lib/sequel/adapters/shared/oracle.rb 457 def supports_derived_column_lists? 458 false 459 end
Oracle
does not support derived column lists
Source
# File lib/sequel/adapters/shared/oracle.rb 463 def supports_fetch_next_rows? 464 server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked]) 465 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 468 def supports_group_cube? 469 true 470 end
Oracle
supports GROUP BY CUBE
Source
# File lib/sequel/adapters/shared/oracle.rb 473 def supports_group_rollup? 474 true 475 end
Oracle
supports GROUP BY ROLLUP
Source
# File lib/sequel/adapters/shared/oracle.rb 478 def supports_grouping_sets? 479 true 480 end
Oracle
supports GROUPING SETS
Source
# File lib/sequel/adapters/shared/oracle.rb 483 def supports_intersect_except_all? 484 false 485 end
Oracle
does not support INTERSECT ALL or EXCEPT ALL
Source
# File lib/sequel/adapters/shared/oracle.rb 488 def supports_is_true? 489 false 490 end
Oracle
does not support IS TRUE.
Source
# File lib/sequel/adapters/shared/oracle.rb 498 def supports_merge? 499 true 500 end
Oracle
supports MERGE
Source
# File lib/sequel/adapters/shared/oracle.rb 503 def supports_nowait? 504 true 505 end
Oracle
supports NOWAIT.
Source
# File lib/sequel/adapters/shared/oracle.rb 543 def supports_regexp? 544 server_version >= 10010002 545 end
Oracle
10+ supports pattern matching via regular expressions
Source
# File lib/sequel/adapters/shared/oracle.rb 513 def supports_select_all_and_column? 514 false 515 end
Oracle
does not support SELECT *, column
Source
# File lib/sequel/adapters/shared/oracle.rb 518 def supports_skip_locked? 519 true 520 end
Oracle
supports SKIP LOCKED.
Source
# File lib/sequel/adapters/shared/oracle.rb 523 def supports_timestamp_timezones? 524 true 525 end
Oracle
supports timezones in literal timestamps.
Source
# File lib/sequel/adapters/shared/oracle.rb 528 def supports_where_true? 529 false 530 end
Oracle
does not support WHERE ‘Y’ for WHERE TRUE.
Source
# File lib/sequel/adapters/shared/oracle.rb 533 def supports_window_functions? 534 true 535 end
Oracle
supports window functions
Private Instance Methods
Source
# 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
Handle Oracle’s non-standard MERGE WHEN condition syntax.
Source
# 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
Handle Oracle’s non standard MERGE syntax
Source
# 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
Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.
Source
# File lib/sequel/adapters/shared/oracle.rb 615 def allow_preparing_prepared_statements? 616 true 617 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 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
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 629 def default_timestamp_format 630 "'%Y-%m-%d %H:%M:%S.%6N %:z'" 631 end
The strftime format to use when literalizing the time.
Source
# File lib/sequel/adapters/shared/oracle.rb 633 def empty_from_sql 634 ' FROM DUAL' 635 end
Source
# File lib/sequel/adapters/shared/oracle.rb 641 def emulate_function?(name) 642 name == :char_length 643 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 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
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 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
If this dataset is associated with a sequence, return the most recently inserted sequence value.
Source
# File lib/sequel/adapters/shared/oracle.rb 668 def insert_supports_empty_values? 669 false 670 end
Oracle
doesn’t support empty values when inserting.
Source
# File lib/sequel/adapters/shared/oracle.rb 673 def literal_blob_append(sql, v) 674 sql << "'" << v.unpack("H*").first << "'" 675 end
Use string in hex format for blob data.
Source
# File lib/sequel/adapters/shared/oracle.rb 678 def literal_false 679 "'N'" 680 end
Oracle
uses ‘N’ for false values.
Source
Source
# File lib/sequel/adapters/shared/oracle.rb 688 def literal_true 689 "'Y'" 690 end
Oracle
uses ‘Y’ for true values.
Source
# File lib/sequel/adapters/shared/oracle.rb 693 def multi_insert_sql_strategy 694 :union 695 end
Oracle
can insert multiple rows using a UNION
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
Source
# 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
Use SKIP LOCKED if skipping locked rows.
Source
# File lib/sequel/adapters/shared/oracle.rb 727 def supports_quoted_function_names? 728 true 729 end
Oracle
supports quoted function names.