Class: Oracle

Inherits:
Object
  • Object
show all
Defined in:
lib/oracle_query.rb

Instance Method Summary collapse

Constructor Details

#initialize(username, password, db_connection) ⇒ Oracle

Returns a new instance of Oracle.



4
5
6
 
# File 'lib/oracle_query.rb', line 4

def initialize (username, password, db_connection)
  @oci = OCI8.new(username, password, db_connection)
end

Instance Method Details

#append(table, values) ⇒ Object

Set table name and values [“name = ‘John’, employee_id = 39994”]



46
47
48
49
 
# File 'lib/oracle_query.rb', line 46

def append (table, values)
  @oci.exec("INSERT /*+ append */ INTO #{table} VALUES(#{values[0]})")
  @oci.commit
end

#commit ⇒ Object 



37
38
39
 
# File 'lib/oracle_query.rb', line 37

def commit()
  @oci.commit
end

#create(table, values) ⇒ Object

Set table = TABLE_NAME; Set Values as an array [[‘column_name1’, ‘data_type’], [‘column_name2’, ‘data_type’], [‘column_name3’, ‘data_type’]]



97
98
99
100
101
102
103
104
105
106
 
# File 'lib/oracle_query.rb', line 97

def create(table, values)
  data_set = []
  values.each{ |data| 
    data = data.join(" ") 
    data_set.push(data)
  }
  data_set = data_set.join(", ")
  @oci.exec("CREATE TABLE #{table} (#{data_set})")
  @oci.commit
end

#delete(table, values = '', type = '') ⇒ Object

Set table to name of table you wish to delete Set values for where column = values Set type to all if you want to remove all otherewise pass nil



72
73
74
75
76
77
78
79
80
 
# File 'lib/oracle_query.rb', line 72

def delete (table, values = '', type = '')
  if type == "all"
    @oci.exec("DELETE FROM #{table}")
    @oci.commit
  else
    @oci.exec("DELETE FROM #{table} WHERE #{values}")
    @oci.commit
  end
end

#drop(object, type) ⇒ Object

Set Object to either a table or database Set the value based on if its a table your droping or database



84
85
86
87
88
89
90
91
92
93
 
# File 'lib/oracle_query.rb', line 84

def drop (object, type)
  if type == 'table'
    @oci.exec("DROP #{object}")
    @oci.commit
  end
  if type == 'database'
    @oci.exec("DROP DATABASE #{object}")
    @oci.commit
  end
end

#insert(table, values, rollback) ⇒ Object

Set table name and values [“name = ‘John’, employee_id = 39994”]



22
23
24
25
26
27
28
29
 
# File 'lib/oracle_query.rb', line 22

def insert (table, values, rollback)
  if rollback == false
    @oci.exec("INSERT INTO #{table} VALUES(#{values[0]})")
    @oci.commit
  else
    @oci.exec("INSERT INTO #{table} VALUES(#{values[0]})")
  end
end

#merge(table, table_temp, columns, values, insert_col, value_col) ⇒ Object

Set table name, set merge from table, columns to link on, set values when match, set the insert_cols and value_col when no match found



32
33
34
35
 
# File 'lib/oracle_query.rb', line 32

def merge (table, table_temp, columns, values, insert_col, value_col)
  @oci.exec("MERGE INTO #{table} USING #{table_temp} ON (#{columns[0]}) WHEN MATCHED THEN UPDATE SET #{values[0]} WHEN NOT MATCHED THEN INSERT (#{insert_col[0]}) VALUES (#{value_col[0]})")
  @oci.commit
end

#procedure(procedure) ⇒ Object 



8
9
10
 
# File 'lib/oracle_query.rb', line 8

def procedure(procedure)
  @oci.exec("BEGIN #{procedure}; END;")
end

#rollback ⇒ Object 



41
42
43
 
# File 'lib/oracle_query.rb', line 41

def rollback()
  @oci.rollback
end

#select(query, var) ⇒ Object 



12
13
14
15
16
17
18
19
 
# File 'lib/oracle_query.rb', line 12

def select (query, var)
  variable = var
  @variable = []
  @oci.exec(query) do |result|
    @variable.push(result)
  end
  eval("$#{variable} = @variable")
end

#truncate(table) ⇒ Object

set Table name to truncate



52
53
54
55
 
# File 'lib/oracle_query.rb', line 52

def truncate (table)
  @oci.exec("TRUNCATE TABLE #{table}")
  @oci.commit
end

#update(table, values, where, omitt) ⇒ Object

set Table name, Values to update [“name = ‘John’, id = 30”], where columns = values omitt set to false omitts where clause



59
60
61
62
63
64
65
66
67
 
# File 'lib/oracle_query.rb', line 59

def update (table, values, where, omitt)
  if omitt == false
    @oci.exec("UPDATE #{table} SET #{values[0]} WHERE #{where}")
    @oci.commit
  else
    @oci.exec("UPDATE #{table} SET #{values[0]}")
    @oci.commit
  end
end