Module: Sequel::Oracle::DatasetMethods

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 



266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 266

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.



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

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) 


332
333
334
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 332

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.



315
316
317
318
319
320
321
322
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 315

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:



325
326
327
328
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 325

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) 


380
381
382
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 380

def recursive_cte_requires_column_aliases?
  true
end

#requires_sql_standard_datetimes? ⇒ Boolean

Oracle requires SQL standard datetimes

Returns:

  • (Boolean) 


337
338
339
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 337

def requires_sql_standard_datetimes?
  true
end

#select_sql ⇒ Object

Handle LIMIT by using a unlimited subselect filtered with ROWNUM.



349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 349

def select_sql
  return super if @opts[:sql]
  if o = @opts[:offset]
    columns = clone(:append_sql=>'').columns
    dsa1 = dataset_alias(1)
    rn = row_number_column
    limit = @opts[:limit]
    ds = unlimited.
      from_self(:alias=>dsa1).
      select_append(ROW_NUMBER_EXPRESSION.as(rn)).
      from_self(:alias=>dsa1).
      select(*columns).
      where(SQL::Identifier.new(rn) > o)
    ds = ds.where(SQL::Identifier.new(rn) <= Sequel.+(o, limit)) if limit
    sql = @opts[:append_sql] || ''
    subselect_sql_append(sql, ds)
    sql
  elsif limit = @opts[:limit]
    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.



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

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

#supports_group_cube? ⇒ Boolean

Oracle supports GROUP BY CUBE

Returns:

  • (Boolean) 


385
386
387
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 385

def supports_group_cube?
  true
end

#supports_group_rollup? ⇒ Boolean

Oracle supports GROUP BY ROLLUP

Returns:

  • (Boolean) 


390
391
392
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 390

def supports_group_rollup?
  true
end

#supports_intersect_except_all? ⇒ Boolean

Oracle does not support INTERSECT ALL or EXCEPT ALL

Returns:

  • (Boolean) 


395
396
397
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 395

def supports_intersect_except_all?
  false
end

#supports_is_true? ⇒ Boolean

Oracle does not support IS TRUE.

Returns:

  • (Boolean) 


400
401
402
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 400

def supports_is_true?
  false
end

#supports_select_all_and_column? ⇒ Boolean

Oracle does not support SELECT *, column

Returns:

  • (Boolean) 


405
406
407
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 405

def supports_select_all_and_column?
  false
end

#supports_timestamp_timezones? ⇒ Boolean

Oracle supports timezones in literal timestamps.

Returns:

  • (Boolean) 


410
411
412
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 410

def supports_timestamp_timezones?
  true
end

#supports_where_true? ⇒ Boolean

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

Returns:

  • (Boolean) 


415
416
417
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 415

def supports_where_true?
  false
end

#supports_window_functions? ⇒ Boolean

Oracle supports window functions

Returns:

  • (Boolean) 


420
421
422
 
# File 'lib/sequel/adapters/shared/oracle.rb', line 420

def supports_window_functions?
  true
end