module Sequel::Oracle::DatasetMethods
Constants
- BITAND_PROC
- ROW_NUMBER_EXPRESSION
Public Instance Methods
# File lib/sequel/adapters/shared/oracle.rb 323 def complex_expression_sql_append(sql, op, args) 324 case op 325 when :& 326 complex_expression_arg_pairs_append(sql, args, &BITAND_PROC) 327 when :| 328 complex_expression_arg_pairs_append(sql, args){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)} 329 when :^ 330 complex_expression_arg_pairs_append(sql, args) do |*x| 331 s1 = complex_expression_arg_pairs(x){|a, b| Sequel.lit(["(", " - ", " + ", ")"], a, complex_expression_arg_pairs([a, b], &BITAND_PROC), b)} 332 s2 = complex_expression_arg_pairs(x, &BITAND_PROC) 333 Sequel.lit(["(", " - ", ")"], s1, s2) 334 end 335 when :~, :'!~', :'~*', :'!~*' 336 raise InvalidOperation, "Pattern matching via regular expressions is not supported in this Oracle version" unless supports_regexp? 337 if op == :'!~' || op == :'!~*' 338 sql << 'NOT ' 339 end 340 sql << 'REGEXP_LIKE(' 341 literal_append(sql, args[0]) 342 sql << ',' 343 literal_append(sql, args[1]) 344 if op == :'~*' || op == :'!~*' 345 sql << ", 'i'" 346 end 347 sql << ')' 348 when :%, :<<, :>>, :'B~' 349 complex_expression_emulate_append(sql, op, args) 350 else 351 super 352 end 353 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 358 def constant_sql_append(sql, c) 359 if c == :CURRENT_TIME 360 super(sql, :CURRENT_TIMESTAMP) 361 else 362 super 363 end 364 end
Use a custom expression with EXISTS to determine whether a dataset is empty.
# File lib/sequel/adapters/shared/oracle.rb 374 def empty? 375 db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil 376 end
Oracle
uses MINUS instead of EXCEPT, and doesn't support EXCEPT ALL
# File lib/sequel/adapters/shared/oracle.rb 367 def except(dataset, opts=OPTS) 368 raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all] 369 compound_clone(:minus, dataset, opts) 370 end
Oracle
requires recursive CTEs to have column aliases.
# File lib/sequel/adapters/shared/oracle.rb 442 def recursive_cte_requires_column_aliases? 443 true 444 end
# File lib/sequel/adapters/shared/oracle.rb 425 def select_limit_sql(sql) 426 return unless supports_fetch_next_rows? 427 428 if offset = @opts[:offset] 429 sql << " OFFSET " 430 literal_append(sql, offset) 431 sql << " ROWS" 432 end 433 434 if limit = @opts[:limit] 435 sql << " FETCH NEXT " 436 literal_append(sql, limit) 437 sql << " ROWS ONLY" 438 end 439 end
Handle LIMIT by using a unlimited subselect filtered with ROWNUM, unless Oracle
12 is used.
# File lib/sequel/adapters/shared/oracle.rb 392 def select_sql 393 return super if @opts[:sql] 394 return super if supports_fetch_next_rows? 395 396 o = @opts[:offset] 397 if o && o != 0 398 columns = clone(:append_sql=>String.new, :placeholder_literal_null=>true).columns 399 dsa1 = dataset_alias(1) 400 rn = row_number_column 401 limit = @opts[:limit] 402 ds = unlimited. 403 from_self(:alias=>dsa1). 404 select_append(ROW_NUMBER_EXPRESSION.as(rn)). 405 from_self(:alias=>dsa1). 406 select(*columns). 407 where(SQL::Identifier.new(rn) > o) 408 ds = ds.where(SQL::Identifier.new(rn) <= Sequel.+(o, limit)) if limit 409 sql = @opts[:append_sql] || String.new 410 subselect_sql_append(sql, ds) 411 sql 412 elsif limit = @opts[:limit] 413 ds = unlimited 414 # Lock doesn't work in subselects, so don't use a subselect when locking. 415 # Don't use a subselect if custom SQL is used, as it breaks somethings. 416 ds = ds.from_self unless @opts[:lock] 417 sql = @opts[:append_sql] || String.new 418 subselect_sql_append(sql, ds.where(SQL::ComplexExpression.new(:<=, ROW_NUMBER_EXPRESSION, limit))) 419 sql 420 else 421 super 422 end 423 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 386 def sequence(s) 387 clone(:sequence=>s) 388 end
The version of the database server
# File lib/sequel/adapters/shared/oracle.rb 527 def server_version 528 db.server_version(@opts[:server]) 529 end
# File lib/sequel/adapters/shared/oracle.rb 446 def supports_cte?(type=:select) 447 type == :select 448 end
Oracle
does not support derived column lists
# File lib/sequel/adapters/shared/oracle.rb 451 def supports_derived_column_lists? 452 false 453 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 457 def supports_fetch_next_rows? 458 server_version >= 12000000 && !(@opts[:lock] || @opts[:skip_locked]) 459 end
Oracle
supports GROUP BY CUBE
# File lib/sequel/adapters/shared/oracle.rb 462 def supports_group_cube? 463 true 464 end
Oracle
supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/oracle.rb 467 def supports_group_rollup? 468 true 469 end
Oracle
supports GROUPING SETS
# File lib/sequel/adapters/shared/oracle.rb 472 def supports_grouping_sets? 473 true 474 end
Oracle
does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/oracle.rb 477 def supports_intersect_except_all? 478 false 479 end
Oracle
does not support IS TRUE.
# File lib/sequel/adapters/shared/oracle.rb 482 def supports_is_true? 483 false 484 end
Oracle
supports NOWAIT.
# File lib/sequel/adapters/shared/oracle.rb 492 def supports_nowait? 493 true 494 end
Oracle
10+ supports pattern matching via regular expressions
# File lib/sequel/adapters/shared/oracle.rb 532 def supports_regexp? 533 server_version >= 10010002 534 end
Oracle
does not support SELECT *, column
# File lib/sequel/adapters/shared/oracle.rb 502 def supports_select_all_and_column? 503 false 504 end
Oracle
supports SKIP LOCKED.
# File lib/sequel/adapters/shared/oracle.rb 507 def supports_skip_locked? 508 true 509 end
Oracle
supports timezones in literal timestamps.
# File lib/sequel/adapters/shared/oracle.rb 512 def supports_timestamp_timezones? 513 true 514 end
Oracle
does not support WHERE 'Y' for WHERE TRUE.
# File lib/sequel/adapters/shared/oracle.rb 517 def supports_where_true? 518 false 519 end
Oracle
supports window functions
# File lib/sequel/adapters/shared/oracle.rb 522 def supports_window_functions? 523 true 524 end
Private Instance Methods
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 540 def allow_preparing_prepared_statements? 541 true 542 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 547 def as_sql_append(sql, aliaz, column_aliases=nil) 548 raise Error, "oracle does not support derived column lists" if column_aliases 549 sql << ' ' 550 quote_identifier_append(sql, aliaz) 551 end
The strftime format to use when literalizing the time.
# File lib/sequel/adapters/shared/oracle.rb 554 def default_timestamp_format 555 "TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'" 556 end
# File lib/sequel/adapters/shared/oracle.rb 558 def empty_from_sql 559 ' FROM DUAL' 560 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 566 def emulate_function?(name) 567 name == :char_length 568 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 575 def emulate_function_sql_append(sql, f) 576 if f.name == :char_length 577 literal_append(sql, Sequel::SQL::Function.new(:length, Sequel.join([f.args.first, 'x'])) - 1) 578 end 579 end
If this dataset is associated with a sequence, return the most recently inserted sequence value.
# File lib/sequel/adapters/shared/oracle.rb 583 def execute_insert(sql, opts=OPTS) 584 opts = Hash[opts] 585 if f = @opts[:from] 586 opts[:table] = f.first 587 end 588 opts[:sequence] = @opts[:sequence] 589 super 590 end
Use a colon for the timestamp offset, since Oracle
appears to require it.
# File lib/sequel/adapters/shared/oracle.rb 593 def format_timestamp_offset(hour, minute) 594 sprintf("%+03i:%02i", hour, minute) 595 end
Oracle
doesn't support empty values when inserting.
# File lib/sequel/adapters/shared/oracle.rb 598 def insert_supports_empty_values? 599 false 600 end
Use string in hex format for blob data.
# File lib/sequel/adapters/shared/oracle.rb 603 def literal_blob_append(sql, v) 604 sql << "'" << v.unpack("H*").first << "'" 605 end
Oracle
uses 'N' for false values.
# File lib/sequel/adapters/shared/oracle.rb 608 def literal_false 609 "'N'" 610 end
Oracle
uses 'Y' for true values.
# File lib/sequel/adapters/shared/oracle.rb 618 def literal_true 619 "'Y'" 620 end
Oracle
can insert multiple rows using a UNION
# File lib/sequel/adapters/shared/oracle.rb 623 def multi_insert_sql_strategy 624 :union 625 end
Use SKIP LOCKED if skipping locked rows.
# File lib/sequel/adapters/shared/oracle.rb 628 def select_lock_sql(sql) 629 super 630 631 if @opts[:lock] 632 if @opts[:skip_locked] 633 sql << " SKIP LOCKED" 634 elsif @opts[:nowait] 635 sql << " NOWAIT" 636 end 637 end 638 end
Oracle
supports quoted function names.
# File lib/sequel/adapters/shared/oracle.rb 641 def supports_quoted_function_names? 642 true 643 end