class PLSQL::Schema

Constants

DBMS_OUTPUT_MAX_LINES

Maximum line numbers for DBMS_OUTPUT in one PL/SQL call (from DBMSOUTPUT_LINESARRAY type)

Attributes

connection[R]

Returns connection wrapper object (this is not raw OCI8 or JDBC connection!)

Public Instance Methods

activerecord_class=(ar_class) click to toggle source

Set connection to current ActiveRecord connection (use in initializer file):

plsql.activerecord_class = ActiveRecord::Base
# File lib/plsql/schema.rb, line 78
def activerecord_class=(ar_class)
  @connection = ar_class ? Connection.create(nil, ar_class) : nil
  reset_instance_variables
  ar_class
end
connect!(*args) click to toggle source

Create new OCI8 or JDBC connection using one of the following ways:

plsql.connect! username, password, database_tns_alias
plsql.connect! username, password, :host => host, :port => port, :database => database
plsql.connect! :username => username, :password => password, :database => database_tns_alias
plsql.connect! :username => username, :password => password, :host => host, :port => port, :database => database
# File lib/plsql/schema.rb, line 64
def connect!(*args)
  params = {}
  params[:username] = args.shift if args[0].is_a?(String)
  params[:password] = args.shift if args[0].is_a?(String)
  params[:database] = args.shift if args[0].is_a?(String)
  params.merge!(args.shift) if args[0].is_a?(Hash)
  raise ArgumentError, "Wrong number of arguments" unless args.empty?
  self.connection = Connection.create_new(params)
end
connection=(conn) click to toggle source

Set connection to OCI8 or JDBC connection:

plsql.connection = OCI8.new(database_user, database_password, database_name)

or

plsql.connection = java.sql.DriverManager.getConnection(
  "jdbc:oracle:thin:@#{database_host}:#{database_port}:#{database_name}",
  database_user, database_password)
# File lib/plsql/schema.rb, line 47
def connection=(conn)
  if conn.is_a?(Connection)
    @connection = conn
    reset_instance_variables
  else
    self.raw_connection = conn
  end
  conn
end
dbms_output_buffer_size() click to toggle source

DBMS_OUTPUT buffer size (default is 20_000)

# File lib/plsql/schema.rb, line 125
def dbms_output_buffer_size
  if @original_schema
    @original_schema.dbms_output_buffer_size
  else
    @dbms_output_buffer_size || 20_000
  end
end
dbms_output_buffer_size=(value) click to toggle source

Seet DBMS_OUTPUT buffer size (default is 20_000). Example:

plsql.dbms_output_buffer_size = 100_000
# File lib/plsql/schema.rb, line 137
def dbms_output_buffer_size=(value)
  @dbms_output_buffer_size = value
end
dbms_output_stream() click to toggle source

IO stream where to log DBMS_OUTPUT from PL/SQL procedures.

# File lib/plsql/schema.rb, line 156
def dbms_output_stream
  if @original_schema
    @original_schema.dbms_output_stream
  else
    @dbms_output_stream
  end
end
dbms_output_stream=(stream) click to toggle source

Specify IO stream where to log DBMS_OUTPUT from PL/SQL procedures. Example:

plsql.dbms_output_stream = STDOUT
# File lib/plsql/schema.rb, line 148
def dbms_output_stream=(stream)
  @dbms_output_stream = stream
  if @dbms_output_stream.nil? && @connection
    sys.dbms_output.disable
  end
end
default_timezone() click to toggle source

Default timezone to which database values will be converted - :utc or :local

# File lib/plsql/schema.rb, line 97
def default_timezone
  if @original_schema
    @original_schema.default_timezone
  else
    @default_timezone ||
      # Use ActiveRecord class default_timezone when ActiveRecord connection is used
      (@connection && (ar_class = @connection.activerecord_class) && ar_class.default_timezone) ||
      # default to local timezone
      :local
  end
end
default_timezone=(value) click to toggle source

Set default timezone to which database values will be converted - :utc or :local

# File lib/plsql/schema.rb, line 110
def default_timezone=(value)
  if [:local, :utc].include?(value)
    @default_timezone = value
  else
    raise ArgumentError, "default timezone should be :local or :utc"
  end
end
logoff() click to toggle source

Disconnect from Oracle

# File lib/plsql/schema.rb, line 85
def logoff
  @connection.logoff
  self.connection = nil
end
schema_name() click to toggle source

Current Oracle schema name

# File lib/plsql/schema.rb, line 91
def schema_name
  return nil unless connection
  @schema_name ||= select_first("SELECT SYS_CONTEXT('userenv','session_user') FROM dual")[0]
end

Private Instance Methods

_errors(object_schema_name, object_name, object_type) click to toggle source
# File lib/plsql/schema.rb, line 255
def _errors(object_schema_name, object_name, object_type)
  result = ""
  previous_line = 0
  select_all(
    "SELECT e.line, e.position, e.text error_text, s.text source_text
    FROM all_errors e, all_source s
    WHERE e.owner = :owner AND e.name = :name AND e.type = :type
      AND s.owner = e.owner AND s.name = e.name AND s.type = e.type AND s.line = e.line
    ORDER BY e.sequence",
    object_schema_name, object_name, object_type
  ).each do |line, position, error_text, source_text|
    result << "Error on line #{'%4d' % line}: #{source_text}" if line > previous_line
    result << "     position #{'%4d' % position}: #{error_text}\n"
    previous_line = line
  end
  result unless result.empty?
end
find_database_object(name, override_schema_name = nil) click to toggle source
# File lib/plsql/schema.rb, line 193
    def find_database_object(name, override_schema_name = nil)
      object_schema_name = override_schema_name || schema_name
      object_name = name.to_s.upcase
      if (row = select_first(<<-SQL, object_schema_name, object_name))
          SELECT o.object_type,
                 o.object_id,
                 o.status,
                (CASE
                 WHEN o.object_type = 'PACKAGE' THEN
                  (SELECT ob.status
                   FROM   all_objects ob
                   WHERE  ob.owner = o.owner
                   AND    ob.object_name = o.object_name
                   AND    ob.object_type = 'PACKAGE BODY')
                 ELSE NULL
                 END) body_status,
                (CASE
                 WHEN o.object_type = 'FUNCTION' THEN
                  (SELECT p.pipelined
                   FROM   all_procedures p
                   WHERE  p.owner = o.owner
                   AND    p.object_name = o.object_name
                   AND    p.object_type = 'FUNCTION')
                 ELSE NULL
                 END) pipelined
          FROM   all_objects o
          WHERE  owner = :owner
          AND    object_name = :object_name
          AND    object_type IN ('PROCEDURE','FUNCTION','PACKAGE','TABLE','VIEW','SEQUENCE','TYPE','SYNONYM')
        SQL

        object_type, object_id, status, body_status, pipelined = row
        raise ArgumentError, "Database object '#{object_schema_name}.#{object_name}' is not in valid status\n#{
          _errors(object_schema_name, object_name, object_type)}" if status == 'INVALID'
        raise ArgumentError, "Package '#{object_schema_name}.#{object_name}' body is not in valid status\n#{
          _errors(object_schema_name, object_name, 'PACKAGE BODY')}" if body_status == 'INVALID'
        case object_type
        when 'PROCEDURE'
          Procedure.new(self, name, nil, override_schema_name, object_id)
        when 'FUNCTION'
          if pipelined == 'NO'
            Procedure.new(self, name, nil, override_schema_name, object_id)
          else
            PipelinedFunction.new(self, name, nil, override_schema_name, object_id)
          end
        when 'PACKAGE'
          Package.new(self, name, override_schema_name)
        when 'TABLE'
          Table.new(self, name, override_schema_name)
        when 'VIEW'
          View.new(self, name, override_schema_name)
        when 'SEQUENCE'
          Sequence.new(self, name, override_schema_name)
        when 'TYPE'
          Type.new(self, name, override_schema_name)
        when 'SYNONYM'
          target_schema_name, target_object_name = @connection.describe_synonym(object_schema_name, object_name)
          find_database_object(target_object_name, target_schema_name)
        end
      end
    end
find_other_schema(name) click to toggle source
# File lib/plsql/schema.rb, line 273
def find_other_schema(name)
  return nil if @original_schema
  if select_first("SELECT username FROM all_users WHERE username = :username", name.to_s.upcase)
    Schema.new(connection, name, self)
  else
    nil
  end
end
find_public_synonym(name) click to toggle source
# File lib/plsql/schema.rb, line 287
def find_public_synonym(name)
  return nil if @original_schema
  target_schema_name, target_object_name = @connection.describe_synonym('PUBLIC', name)
  find_database_object(target_object_name, target_schema_name) if target_schema_name
end
find_standard_procedure(name) click to toggle source
# File lib/plsql/schema.rb, line 282
def find_standard_procedure(name)
  return nil if @original_schema
  Procedure.find(self, name, 'STANDARD', 'SYS')
end
method_missing(method, *args, &block) click to toggle source
# File lib/plsql/schema.rb, line 176
def method_missing(method, *args, &block)
  raise ArgumentError, "No database connection" unless connection
  # search in database if not in cache at first
  object = (@schema_objects[method] ||= find_database_object(method) || find_other_schema(method) ||
     find_public_synonym(method) || find_standard_procedure(method))

  raise ArgumentError, "No database object '#{method.to_s.upcase}' found" unless object

  if object.is_a?(Procedure)
    object.exec(*args, &block)
  elsif object.is_a?(Type) && !args.empty?
    object.new(*args, &block)
  else
    object
  end
end
reset_instance_variables() click to toggle source
# File lib/plsql/schema.rb, line 166
def reset_instance_variables
  if @connection
    @schema_objects = {}
  else
    @schema_objects = nil
  end
  @schema_name = nil
  @default_timezone = nil
end