module Sequel::Oracle::DatasetMethods
Constants
- APOS
- APOS_RE
- BITCOMP_CLOSE
- BITCOMP_OPEN
- BOOL_FALSE
- BOOL_TRUE
- DOUBLE_APOS
- DUAL
- FROM
- HSTAR
- ROW_NUMBER_EXPRESSION
- SELECT_CLAUSE_METHODS
- SPACE
- TIMESTAMP_FORMAT
- TIMESTAMP_OFFSET_FORMAT
Public Instance Methods
# File lib/sequel/adapters/shared/oracle.rb, line 245 def complex_expression_sql_append(sql, op, args) case op when :& sql << complex_expression_arg_pairs(args){|a, b| "CAST(BITAND(#{literal(a)}, #{literal(b)}) AS INTEGER)"} when :| sql << complex_expression_arg_pairs(args){|a, b| "(#{literal(a)} - #{complex_expression_sql(:&, [a, b])} + #{literal(b)})"} when :^ sql << complex_expression_arg_pairs(args){|*x| "(#{complex_expression_sql(:|, x)} - #{complex_expression_sql(:&, x)})"} when :'B~' sql << BITCOMP_OPEN literal_append(sql, args.at(0)) sql << BITCOMP_CLOSE when :<< sql << complex_expression_arg_pairs(args){|a, b| "(#{literal(a)} * power(2, #{literal b}))"} when :>> sql << complex_expression_arg_pairs(args){|a, b| "(#{literal(a)} / power(2, #{literal b}))"} when :% sql << complex_expression_arg_pairs(args){|a, b| "MOD(#{literal(a)}, #{literal(b)})"} else super end 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, line 271 def constant_sql_append(sql, c) if c == :CURRENT_TIME super(sql, :CURRENT_TIMESTAMP) else super end end
Use a custom expression with EXISTS to determine whether a dataset is empty.
# File lib/sequel/adapters/shared/oracle.rb, line 302 def empty? db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil 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, line 284 def emulated_function_sql_append(sql, f) case f.f when :char_length literal_append(sql, Sequel::SQL::Function.new(:length, Sequel.join([f.args.first, 'x'])) - 1) else super end end
Oracle uses MINUS instead of EXCEPT, and doesn't support EXCEPT ALL
# File lib/sequel/adapters/shared/oracle.rb, line 294 def except(dataset, opts={}) opts = {:all=>opts} unless opts.is_a?(Hash) raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all] compound_clone(:minus, dataset, opts) end
Oracle requires recursive CTEs to have column aliases.
# File lib/sequel/adapters/shared/oracle.rb, line 334 def recursive_cte_requires_column_aliases? true end
Handle LIMIT by using a unlimited subselect filtered with ROWNUM.
# File lib/sequel/adapters/shared/oracle.rb, line 319 def select_sql if (limit = @opts[:limit]) && !@opts[:sql] ds = clone(:limit=>nil) # Lock doesn't work in subselects, so don't use a subselect when locking. # Don't use a subselect if custom SQL is used, as it breaks somethings. ds = ds.from_self unless @opts[:lock] sql = @opts[:append_sql] || '' subselect_sql_append(sql, ds.where(SQL::ComplexExpression.new(:<=, ROW_NUMBER_EXPRESSION, limit))) sql else super end 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, line 314 def sequence(s) clone(:sequence=>s) end
Oracle supports GROUP BY CUBE
# File lib/sequel/adapters/shared/oracle.rb, line 339 def supports_group_cube? true end
Oracle supports GROUP BY ROLLUP
# File lib/sequel/adapters/shared/oracle.rb, line 344 def supports_group_rollup? true end
Oracle does not support INTERSECT ALL or EXCEPT ALL
# File lib/sequel/adapters/shared/oracle.rb, line 349 def supports_intersect_except_all? false end
Oracle does not support IS TRUE.
# File lib/sequel/adapters/shared/oracle.rb, line 354 def supports_is_true? false end
Oracle does not support SELECT *, column
# File lib/sequel/adapters/shared/oracle.rb, line 359 def supports_select_all_and_column? false end
Oracle supports timezones in literal timestamps.
# File lib/sequel/adapters/shared/oracle.rb, line 364 def supports_timestamp_timezones? true end
Oracle does not support WHERE 'Y' for WHERE TRUE.
# File lib/sequel/adapters/shared/oracle.rb, line 369 def supports_where_true? false end
Oracle supports window functions
# File lib/sequel/adapters/shared/oracle.rb, line 374 def supports_window_functions? true end
Private Instance Methods
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.
# File lib/sequel/adapters/shared/oracle.rb, line 382 def as_sql_append(sql, aliaz) sql << SPACE quote_identifier_append(sql, aliaz) end
The strftime format to use when literalizing the time.
# File lib/sequel/adapters/shared/oracle.rb, line 388 def default_timestamp_format TIMESTAMP_FORMAT end
If this dataset is associated with a sequence, return the most recently inserted sequence value.
# File lib/sequel/adapters/shared/oracle.rb, line 394 def execute_insert(sql, opts={}) f = @opts[:from] super(sql, {:table=>(f.first if f), :sequence=>@opts[:sequence]}.merge(opts)) end
Use a colon for the timestamp offset, since Oracle appears to require it.
# File lib/sequel/adapters/shared/oracle.rb, line 400 def format_timestamp_offset(hour, minute) sprintf(TIMESTAMP_OFFSET_FORMAT, hour, minute) end
Oracle doesn't support empty values when inserting.
# File lib/sequel/adapters/shared/oracle.rb, line 405 def insert_supports_empty_values? false end
Use string in hex format for blob data.
# File lib/sequel/adapters/shared/oracle.rb, line 410 def literal_blob_append(sql, v) sql << APOS << v.unpack(HSTAR).first << APOS end
Oracle uses 'N' for false values.
# File lib/sequel/adapters/shared/oracle.rb, line 415 def literal_false BOOL_FALSE end
Oracle uses 'Y' for true values.
# File lib/sequel/adapters/shared/oracle.rb, line 425 def literal_true BOOL_TRUE end
Use the Oracle-specific SQL clauses (no limit, since it is emulated).
# File lib/sequel/adapters/shared/oracle.rb, line 430 def select_clause_methods SELECT_CLAUSE_METHODS end
Modify the SQL to add the list of tables to select FROM Oracle doesn't support select without FROM clause so add the dummy DUAL table if the dataset doesn't select from a table.
# File lib/sequel/adapters/shared/oracle.rb, line 438 def select_from_sql(sql) sql << FROM source_list_append(sql, @opts[:from] || DUAL) end