module Sequel::Oracle::DatasetMethods
Constants
- BITAND_PROC
- ROW_NUMBER_EXPRESSION
Public Instance Methods
# 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
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 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
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
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
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
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle
12 is used.
# 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
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
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
# File lib/sequel/adapters/shared/oracle.rb 436 def supports_cte?(type=:select) 437 type == :select 438 end
Oracle
does not support derived column lists
# File lib/sequel/adapters/shared/oracle.rb 441 def supports_derived_column_lists? 442 false 443 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 447 def supports_fetch_next_rows? 448 server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked]) 449 end
Oracle
supports GROUP BY CUBE
# File lib/sequel/adapters/shared/oracle.rb 452 def supports_group_cube? 453 true 454 end
Oracle
supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/oracle.rb 457 def supports_group_rollup? 458 true 459 end
Oracle
supports GROUPING SETS
# File lib/sequel/adapters/shared/oracle.rb 462 def supports_grouping_sets? 463 true 464 end
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
Oracle
does not support IS TRUE.
# File lib/sequel/adapters/shared/oracle.rb 472 def supports_is_true? 473 false 474 end
Oracle
supports MERGE
# File lib/sequel/adapters/shared/oracle.rb 482 def supports_merge? 483 true 484 end
Oracle
supports NOWAIT.
# File lib/sequel/adapters/shared/oracle.rb 487 def supports_nowait? 488 true 489 end
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
Oracle
does not support SELECT *, column
# File lib/sequel/adapters/shared/oracle.rb 497 def supports_select_all_and_column? 498 false 499 end
Oracle
supports SKIP LOCKED.
# File lib/sequel/adapters/shared/oracle.rb 502 def supports_skip_locked? 503 true 504 end
Oracle
supports timezones in literal timestamps.
# File lib/sequel/adapters/shared/oracle.rb 507 def supports_timestamp_timezones? 508 true 509 end
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
Oracle
supports window functions
# File lib/sequel/adapters/shared/oracle.rb 517 def supports_window_functions? 518 true 519 end
Private Instance Methods
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
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
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, 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
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
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
# File lib/sequel/adapters/shared/oracle.rb 617 def empty_from_sql 618 ' FROM DUAL' 619 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 625 def emulate_function?(name) 626 name == :char_length 627 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 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
If this dataset is associated with a sequence, return the most recently inserted sequence value.
# 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
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
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
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
Oracle
uses 'N' for false values.
# File lib/sequel/adapters/shared/oracle.rb 667 def literal_false 668 "'N'" 669 end
Oracle
uses 'Y' for true values.
# File lib/sequel/adapters/shared/oracle.rb 677 def literal_true 678 "'Y'" 679 end
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
# 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
Use SKIP LOCKED if skipping locked rows.
# 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
Oracle
supports quoted function names.
# File lib/sequel/adapters/shared/oracle.rb 716 def supports_quoted_function_names? 717 true 718 end