# File lib/sequel/adapters/shared/oracle.rb, line 11 def create_sequence(name, opts={}) self << create_sequence_sql(name, opts) end
# File lib/sequel/adapters/shared/oracle.rb, line 15 def create_trigger(*args) self << create_trigger_sql(*args) end
# File lib/sequel/adapters/shared/oracle.rb, line 19 def current_user @current_user ||= metadata_dataset.get{sys_context('USERENV', 'CURRENT_USER')} end
Oracle uses the :oracle database type
# File lib/sequel/adapters/shared/oracle.rb, line 28 def database_type :oracle end
# File lib/sequel/adapters/shared/oracle.rb, line 23 def drop_sequence(name) self << drop_sequence_sql(name) end
Oracle namespaces indexes per table.
# File lib/sequel/adapters/shared/oracle.rb, line 33 def global_index_namespace? false end
Oracle supports deferrable constraints.
# File lib/sequel/adapters/shared/oracle.rb, line 53 def supports_deferrable_constraints? true end
DB2 supports transaction isolation levels.
# File lib/sequel/adapters/shared/oracle.rb, line 58 def supports_transaction_isolation_levels? true end
# File lib/sequel/adapters/shared/oracle.rb, line 37 def tables(opts={}) m = output_identifier_meth metadata_dataset.from(:tab).server(opts[:server]).select(:tname).filter(:tabtype => 'TABLE').map{|r| m.call(r[:tname])} end
# File lib/sequel/adapters/shared/oracle.rb, line 47 def view_exists?(name) m = input_identifier_meth metadata_dataset.from(:tab).filter(:tname =>m.call(name), :tabtype => 'VIEW').count > 0 end
# File lib/sequel/adapters/shared/oracle.rb, line 42 def views(opts={}) m = output_identifier_meth metadata_dataset.from(:tab).server(opts[:server]).select(:tname).filter(:tabtype => 'VIEW').map{|r| m.call(r[:tname])} end
Handle Oracle specific ALTER TABLE SQL
# File lib/sequel/adapters/shared/oracle.rb, line 65 def alter_table_sql(table, op) case op[:op] when :add_column if op[:primary_key] sqls = [] sqls << alter_table_sql(table, op.merge(:primary_key=>nil)) if op[:auto_increment] seq_name = default_sequence_name(table, op[:name]) sqls << drop_sequence_sql(seq_name) sqls << create_sequence_sql(seq_name, op) sqls << "UPDATE #{quote_schema_table(table)} SET #{quote_identifier(op[:name])} = #{seq_name}.nextval" end sqls << "ALTER TABLE #{quote_schema_table(table)} ADD PRIMARY KEY (#{quote_identifier(op[:name])})" sqls else "ALTER TABLE #{quote_schema_table(table)} ADD #{column_definition_sql(op)}" end when :set_column_null "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{op[:null] ? 'NULL' : 'NOT NULL'}" when :set_column_type "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} #{type_literal(op)}" when :set_column_default "ALTER TABLE #{quote_schema_table(table)} MODIFY #{quote_identifier(op[:name])} DEFAULT #{literal(op[:default])}" else super(table, op) end end
# File lib/sequel/adapters/shared/oracle.rb, line 93 def auto_increment_sql AUTOINCREMENT end
# File lib/sequel/adapters/shared/oracle.rb, line 97 def create_sequence_sql(name, opts={}) "CREATE SEQUENCE #{quote_identifier(name)} start with #{opts [:start_with]||1} increment by #{opts[:increment_by]||1} nomaxvalue" end
# File lib/sequel/adapters/shared/oracle.rb, line 101 def create_table_from_generator(name, generator, options) drop_statement, create_statements = create_table_sql_list(name, generator, options) (execute_ddl(drop_statement) rescue nil) if drop_statement create_statements.each{|sql| execute_ddl(sql)} end
# File lib/sequel/adapters/shared/oracle.rb, line 107 def create_table_sql_list(name, generator, options={}) statements = [create_table_sql(name, generator, options)] drop_seq_statement = nil generator.columns.each do |c| if c[:auto_increment] c[:sequence_name] ||= default_sequence_name(name, c[:name]) unless c[:create_sequence] == false drop_seq_statement = drop_sequence_sql(c[:sequence_name]) statements << create_sequence_sql(c[:sequence_name], c) end unless c[:create_trigger] == false c[:trigger_name] ||= "BI_#{name}_#{c[:name]}" trigger_definition = <<-end_sql BEGIN IF :NEW.#{quote_identifier(c[:name])} IS NULL THEN SELECT #{c[:sequence_name]}.nextval INTO :NEW.#{quote_identifier(c[:name])} FROM dual; END IF; END; end_sql statements << create_trigger_sql(name, c[:trigger_name], trigger_definition, {:events => [:insert]}) end end end [drop_seq_statement, statements] end
# File lib/sequel/adapters/shared/oracle.rb, line 133 def create_trigger_sql(table, name, definition, opts={}) events = opts[:events] ? Array(opts[:events]) : [:insert, :update, :delete] sql = <<-end_sql CREATE#{' OR REPLACE' if opts[:replace]} TRIGGER #{quote_identifier(name)} #{opts[:after] ? 'AFTER' : 'BEFORE'} #{events.map{|e| e.to_s.upcase}.join(' OR ')} ON #{quote_schema_table(table)} REFERENCING NEW AS NEW FOR EACH ROW #{definition} end_sql sql end
# File lib/sequel/adapters/shared/oracle.rb, line 151 def database_error_regexps DATABASE_ERROR_REGEXPS end
# File lib/sequel/adapters/shared/oracle.rb, line 155 def default_sequence_name(table, column) "seq_#{table}_#{column}" end
# File lib/sequel/adapters/shared/oracle.rb, line 159 def drop_sequence_sql(name) "DROP SEQUENCE #{quote_identifier(name)}" end
# File lib/sequel/adapters/shared/oracle.rb, line 163 def remove_cached_schema(table) @primary_key_sequences.delete(table) super end
# File lib/sequel/adapters/shared/oracle.rb, line 178 def sequence_for_table(table) return nil unless autosequence @primary_key_sequences.fetch(table) do |key| pk = schema(table).select{|k, v| v[:primary_key]} @primary_key_sequences[table] = if pk.length == 1 seq = "seq_#{table}_#{pk.first.first}" seq.to_sym unless from(:user_sequences).filter(:sequence_name=>input_identifier_meth.call(seq)).empty? end end end
Oracle doesn't support READ UNCOMMITTED OR REPEATABLE READ transaction isolation levels, so upgrade to the next highest level in those cases.
# File lib/sequel/adapters/shared/oracle.rb, line 174 def set_transaction_isolation_sql(level) "SET TRANSACTION ISOLATION LEVEL #{TRANSACTION_ISOLATION_LEVELS[level]}" end
Oracle supports CREATE OR REPLACE VIEW.
# File lib/sequel/adapters/shared/oracle.rb, line 190 def supports_create_or_replace_view? true end
SQL fragment for showing a table is temporary
# File lib/sequel/adapters/shared/oracle.rb, line 214 def temporary_table_sql TEMPORARY end
Oracle's integer/:number type handles larger values than most other databases's bigint types, so it should be safe to use for Bignum.
# File lib/sequel/adapters/shared/oracle.rb, line 197 def type_literal_generic_bignum(column) :integer end
Oracle doesn't have a time type, so use timestamp for all time columns.
# File lib/sequel/adapters/shared/oracle.rb, line 203 def type_literal_generic_time(column) :timestamp end
Oracle doesn't have a boolean type or even a reasonable facsimile. Using a char(1) seems to be the recommended way.
# File lib/sequel/adapters/shared/oracle.rb, line 209 def type_literal_generic_trueclass(column) :'char(1)' end
Oracle uses clob for text types.
# File lib/sequel/adapters/shared/oracle.rb, line 219 def uses_clob_for_text? true end