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 db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil 389 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 439 def recursive_cte_requires_column_aliases? 440 true 441 end
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle
12 is used.
# File lib/sequel/adapters/shared/oracle.rb 405 def select_sql 406 return super if @opts[:sql] 407 return super if supports_fetch_next_rows? 408 409 o = @opts[:offset] 410 if o && o != 0 411 columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns 412 dsa1 = dataset_alias(1) 413 rn = row_number_column 414 limit = @opts[:limit] 415 ds = unlimited. 416 from_self(:alias=>dsa1). 417 select_append(ROW_NUMBER_EXPRESSION.as(rn)). 418 from_self(:alias=>dsa1). 419 select(*columns). 420 where(SQL::Identifier.new(rn) > o) 421 ds = ds.where(SQL::Identifier.new(rn) <= Sequel.+(o, limit)) if limit 422 sql = @opts[:append_sql] || String.new 423 subselect_sql_append(sql, ds) 424 sql 425 elsif limit = @opts[:limit] 426 ds = unlimited 427 # Lock doesn't work in subselects, so don't use a subselect when locking. 428 # Don't use a subselect if custom SQL is used, as it breaks somethings. 429 ds = ds.from_self unless @opts[:lock] 430 sql = @opts[:append_sql] || String.new 431 subselect_sql_append(sql, ds.where(SQL::ComplexExpression.new(:<=, ROW_NUMBER_EXPRESSION, limit))) 432 sql 433 else 434 super 435 end 436 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 399 def sequence(s) 400 clone(:sequence=>s) 401 end
The version of the database server
# File lib/sequel/adapters/shared/oracle.rb 529 def server_version 530 db.server_version(@opts[:server]) 531 end
# File lib/sequel/adapters/shared/oracle.rb 443 def supports_cte?(type=:select) 444 type == :select 445 end
Oracle
does not support derived column lists
# File lib/sequel/adapters/shared/oracle.rb 448 def supports_derived_column_lists? 449 false 450 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 454 def supports_fetch_next_rows? 455 server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked]) 456 end
Oracle
supports GROUP BY CUBE
# File lib/sequel/adapters/shared/oracle.rb 459 def supports_group_cube? 460 true 461 end
Oracle
supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/oracle.rb 464 def supports_group_rollup? 465 true 466 end
Oracle
supports GROUPING SETS
# File lib/sequel/adapters/shared/oracle.rb 469 def supports_grouping_sets? 470 true 471 end
Oracle
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/oracle.rb 474 def supports_intersect_except_all? 475 false 476 end
Oracle
does not support IS TRUE.
# File lib/sequel/adapters/shared/oracle.rb 479 def supports_is_true? 480 false 481 end
Oracle
supports MERGE
# File lib/sequel/adapters/shared/oracle.rb 489 def supports_merge? 490 true 491 end
Oracle
supports NOWAIT.
# File lib/sequel/adapters/shared/oracle.rb 494 def supports_nowait? 495 true 496 end
Oracle
10+ supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/oracle.rb 534 def supports_regexp? 535 server_version >= 10010002 536 end
Oracle
does not support SELECT *, column
# File lib/sequel/adapters/shared/oracle.rb 504 def supports_select_all_and_column? 505 false 506 end
Oracle
supports SKIP LOCKED.
# File lib/sequel/adapters/shared/oracle.rb 509 def supports_skip_locked? 510 true 511 end
Oracle
supports timezones in literal timestamps.
# File lib/sequel/adapters/shared/oracle.rb 514 def supports_timestamp_timezones? 515 true 516 end
Oracle
does not support WHERE 'Y' for WHERE TRUE.
# File lib/sequel/adapters/shared/oracle.rb 519 def supports_where_true? 520 false 521 end
Oracle
supports window functions
# File lib/sequel/adapters/shared/oracle.rb 524 def supports_window_functions? 525 true 526 end
Private Instance Methods
Handle Oracle's non-standard MERGE WHEN condition syntax.
# File lib/sequel/adapters/shared/oracle.rb 597 def _merge_when_conditions_sql(sql, data) 598 if data.has_key?(:conditions) 599 sql << " WHERE " 600 literal_append(sql, _normalize_merge_when_conditions(data[:conditions])) 601 end 602 end
Handle Oracle's non standard MERGE syntax
# File lib/sequel/adapters/shared/oracle.rb 556 def _merge_when_sql(sql) 557 raise Error, "no WHEN [NOT] MATCHED clauses provided for MERGE" unless merge_when = @opts[:merge_when] 558 insert = update = delete = nil 559 types = merge_when.map{|d| d[:type]} 560 raise Error, "Oracle does not support multiple INSERT, UPDATE, or DELETE clauses in MERGE" if types != types.uniq 561 562 merge_when.each do |data| 563 case data[:type] 564 when :insert 565 insert = data 566 when :update 567 update = data 568 else # when :delete 569 delete = data 570 end 571 end 572 573 if delete 574 raise Error, "Oracle does not support DELETE without UPDATE clause in MERGE" unless update 575 raise Error, "Oracle does not support DELETE without conditions clause in MERGE" unless delete.has_key?(:conditions) 576 end 577 578 if update 579 sql << " WHEN MATCHED" 580 _merge_update_sql(sql, update) 581 _merge_when_conditions_sql(sql, update) 582 583 if delete 584 sql << " DELETE" 585 _merge_when_conditions_sql(sql, delete) 586 end 587 end 588 589 if insert 590 sql << " WHEN NOT MATCHED" 591 _merge_insert_sql(sql, insert) 592 _merge_when_conditions_sql(sql, insert) 593 end 594 end
Handle nil, false, and true MERGE WHEN conditions to avoid non-boolean type error.
# File lib/sequel/adapters/shared/oracle.rb 542 def _normalize_merge_when_conditions(conditions) 543 case conditions 544 when nil, false 545 {1=>0} 546 when true 547 {1=>1} 548 when Sequel::SQL::DelayedEvaluation 549 Sequel.delay{_normalize_merge_when_conditions(conditions.call(self))} 550 else 551 conditions 552 end 553 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 606 def allow_preparing_prepared_statements? 607 true 608 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 613 def as_sql_append(sql, aliaz, column_aliases=nil) 614 raise Error, "oracle does not support derived column lists" if column_aliases 615 sql << ' ' 616 quote_identifier_append(sql, aliaz) 617 end
The strftime format to use when literalizing the time.
# File lib/sequel/adapters/shared/oracle.rb 620 def default_timestamp_format 621 "TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'" 622 end
# File lib/sequel/adapters/shared/oracle.rb 624 def empty_from_sql 625 ' FROM DUAL' 626 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 632 def emulate_function?(name) 633 name == :char_length 634 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 641 def emulate_function_sql_append(sql, f) 642 if f.name == :char_length 643 literal_append(sql, Sequel::SQL::Function.new(:length, Sequel.join([f.args.first, 'x'])) - 1) 644 end 645 end
If this dataset is associated with a sequence, return the most recently inserted sequence value.
# File lib/sequel/adapters/shared/oracle.rb 649 def execute_insert(sql, opts=OPTS) 650 opts = Hash[opts] 651 if f = @opts[:from] 652 opts[:table] = f.first 653 end 654 opts[:sequence] = @opts[:sequence] 655 super 656 end
Use a colon for the timestamp offset, since Oracle
appears to require it.
# File lib/sequel/adapters/shared/oracle.rb 659 def format_timestamp_offset(hour, minute) 660 sprintf("%+03i:%02i", hour, minute) 661 end
Oracle
doesn't support empty values when inserting.
# File lib/sequel/adapters/shared/oracle.rb 664 def insert_supports_empty_values? 665 false 666 end
Use string in hex format for blob data.
# File lib/sequel/adapters/shared/oracle.rb 669 def literal_blob_append(sql, v) 670 sql << "'" << v.unpack("H*").first << "'" 671 end
Oracle
uses 'N' for false values.
# File lib/sequel/adapters/shared/oracle.rb 674 def literal_false 675 "'N'" 676 end
Oracle
uses 'Y' for true values.
# File lib/sequel/adapters/shared/oracle.rb 684 def literal_true 685 "'Y'" 686 end
Oracle
can insert multiple rows using a UNION
# File lib/sequel/adapters/shared/oracle.rb 689 def multi_insert_sql_strategy 690 :union 691 end
# File lib/sequel/adapters/shared/oracle.rb 693 def select_limit_sql(sql) 694 return unless supports_fetch_next_rows? 695 696 if offset = @opts[:offset] 697 sql << " OFFSET " 698 literal_append(sql, offset) 699 sql << " ROWS" 700 end 701 702 if limit = @opts[:limit] 703 sql << " FETCH NEXT " 704 literal_append(sql, limit) 705 sql << " ROWS ONLY" 706 end 707 end
Use SKIP LOCKED if skipping locked rows.
# File lib/sequel/adapters/shared/oracle.rb 710 def select_lock_sql(sql) 711 super 712 713 if @opts[:lock] 714 if @opts[:skip_locked] 715 sql << " SKIP LOCKED" 716 elsif @opts[:nowait] 717 sql << " NOWAIT" 718 end 719 end 720 end
Oracle
supports quoted function names.
# File lib/sequel/adapters/shared/oracle.rb 723 def supports_quoted_function_names? 724 true 725 end