Module: Sequel::Oracle::DatasetMethods

Includes:
EmulateOffsetWithRowNumber
Included in:
JDBC::Oracle::Dataset, Dataset
Defined in:
lib/sequel/adapters/shared/oracle.rb

Constant Summary collapse

SELECT_CLAUSE_METHODS = 
Dataset.clause_methods(:select, %w'with select distinct columns from join where group having compounds order lock')
ROW_NUMBER_EXPRESSION = 
LiteralString.new('ROWNUM').freeze
SPACE = 
Dataset::SPACE
APOS = 
Dataset::APOS
APOS_RE = 
Dataset::APOS_RE
DOUBLE_APOS = 
Dataset::DOUBLE_APOS
FROM = 
Dataset::FROM
BITCOMP_OPEN = 
"((0 - ".freeze
BITCOMP_CLOSE = 
") - 1)".freeze
TIMESTAMP_FORMAT = 
"TIMESTAMP '%Y-%m-%d %H:%M:%S%N %z'".freeze
TIMESTAMP_OFFSET_FORMAT = 
"%+03i:%02i".freeze
BOOL_FALSE = 
"'N'".freeze
BOOL_TRUE = 
"'Y'".freeze
HSTAR = 
"H*".freeze
DUAL = 
['DUAL'.freeze].freeze

Instance Method Summary collapse

Instance Method Details

#complex_expression_sql_append(sql, op, args) ⇒ Object 



245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
 
# 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) do |a, b|
      s1 = ''
      complex_expression_sql_append(s1, :&, [a, b])
      "(#{literal(a)} - #{s1} + #{literal(b)})"
    end
  when :^
    sql << complex_expression_arg_pairs(args) do |*x|
      s1 = ''
      s2 = ''
      complex_expression_sql_append(s1, :|, x)
      complex_expression_sql_append(s2, :&, x)
      "(#{s1} - #{s2})"
    end
  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

#constant_sql_append(sql, c) ⇒ Object

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.



281
282
283
284
285
286
287
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 281

def constant_sql_append(sql, c)
  if c == :CURRENT_TIME
    super(sql, :CURRENT_TIMESTAMP)
  else
    super
  end
end

#empty? ⇒ Boolean

Use a custom expression with EXISTS to determine whether a dataset is empty.

Returns:

  • (Boolean) 


311
312
313
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 311

def empty?
  db[:dual].where(@opts[:offset] ? exists : unordered.exists).get(1) == nil
end

#emulated_function_sql_append(sql, f) ⇒ Object

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.



294
295
296
297
298
299
300
301
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 294

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

#except(dataset, opts = OPTS) ⇒ Object

Oracle uses MINUS instead of EXCEPT, and doesn’t support EXCEPT ALL

Raises:



304
305
306
307
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 304

def except(dataset, opts=OPTS)
  raise(Sequel::Error, "EXCEPT ALL not supported") if opts[:all]
  compound_clone(:minus, dataset, opts)
end

#recursive_cte_requires_column_aliases? ⇒ Boolean

Oracle requires recursive CTEs to have column aliases.

Returns:

  • (Boolean) 


343
344
345
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 343

def recursive_cte_requires_column_aliases?
  true
end

#requires_sql_standard_datetimes? ⇒ Boolean

Oracle requires SQL standard datetimes

Returns:

  • (Boolean) 


316
317
318
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 316

def requires_sql_standard_datetimes?
  true
end

#select_sql ⇒ Object

Handle LIMIT by using a unlimited subselect filtered with ROWNUM.



328
329
330
331
332
333
334
335
336
337
338
339
340
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 328

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

#sequence(s) ⇒ Object

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.



323
324
325
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 323

def sequence(s)
  clone(:sequence=>s)
end

#supports_group_cube? ⇒ Boolean

Oracle supports GROUP BY CUBE

Returns:

  • (Boolean) 


348
349
350
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 348

def supports_group_cube?
  true
end

#supports_group_rollup? ⇒ Boolean

Oracle supports GROUP BY ROLLUP

Returns:

  • (Boolean) 


353
354
355
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 353

def supports_group_rollup?
  true
end

#supports_intersect_except_all? ⇒ Boolean

Oracle does not support INTERSECT ALL or EXCEPT ALL

Returns:

  • (Boolean) 


358
359
360
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 358

def supports_intersect_except_all?
  false
end

#supports_is_true? ⇒ Boolean

Oracle does not support IS TRUE.

Returns:

  • (Boolean) 


363
364
365
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 363

def supports_is_true?
  false
end

#supports_select_all_and_column? ⇒ Boolean

Oracle does not support SELECT *, column

Returns:

  • (Boolean) 


368
369
370
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 368

def supports_select_all_and_column?
  false
end

#supports_timestamp_timezones? ⇒ Boolean

Oracle supports timezones in literal timestamps.

Returns:

  • (Boolean) 


373
374
375
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 373

def supports_timestamp_timezones?
  true
end

#supports_where_true? ⇒ Boolean

Oracle does not support WHERE ‘Y’ for WHERE TRUE.

Returns:

  • (Boolean) 


378
379
380
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 378

def supports_where_true?
  false
end

#supports_window_functions? ⇒ Boolean

Oracle supports window functions

Returns:

  • (Boolean) 


383
384
385
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 383

def supports_window_functions?
  true
end