Class: Orasaurus::DB::Connection

Inherits:
OCI8
  • Object
show all
Defined in:
lib/orasaurus/db.rb

Overview

Extends oci8 for more info on oci8 read this: ruby-oci8.rubyforge.org/en/index.html

Instance Method Summary collapse

Instance Method Details

#determine_object_type(object_owner, object_name) ⇒ Object

returns either the object type or nilif the object can’t be found



16
17
18
19
20
21
 
# File 'lib/orasaurus/db.rb', line 16

def determine_object_type(object_owner,object_name)
  cursor = self.exec("SELECT object_type FROM ALL_OBJECTS WHERE UPPER(owner) = UPPER(:the_owner) AND UPPER(object_name) = UPPER(:the_object_name)", object_owner, object_name)
  return cursor.fetch().first
rescue 
  return nil
end

#get_dependencies(object_owner, object_name) ⇒ Object

gets a valid dependency list works for any object type for tables, it also searches for foreign key refernces as well as plsql references.



26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/orasaurus/db.rb', line 26

def get_dependencies(object_owner,object_name)
  tbl_dependency_sql = %q{ select UNIQUE b.table_name dependent_table
    from all_constraints a
    join all_constraints b
    on a.r_constraint_name = b.constraint_name
    where a.constraint_type = 'R'
    and a.owner = upper(:object_owner)
    and a.table_name = upper(:object_name)
    order by b.table_name    
  }
  
  plsql_dependency_sql = %q{ select 
                  referenced_name,
                  referenced_type
             from all_dependencies
            where owner = upper(:object_owner)
              and name = upper(:object_name)
              and substr(name,1,4) != 'BIN$'
              and substr(referenced_name,1,4) != 'BIN$'
              and referenced_type != 'NON-EXISTENT'
            order by referenced_name    
  }
  
  object_type = determine_object_type(object_owner,object_name)
          
  final_sql = object_type == 'TABLE' ? tbl_dependency_sql : plsql_dependency_sql
  
  dependency_list = []

  self.exec(tbl_dependency_sql,object_owner,object_name) do |row|
    dependency_list.push({ :object_name => row[0], :object_type => row[1]||='TABLE'})
  end
  
  return dependency_list
  
end