Class: Oci8Simple::Client

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

Overview

Run SQL and PL/SQL statements against an Oracle schema.

Logging

All logging is done to ~/.oci8_simple/oci8_simple.log

Examples

  • Initialize a client against the development schema

    client = Oci8Simple::Client.new

  • Run a simple select query against development schema

    client.run('select id, name from foos') => [[2, "lol"], [3, "hey"], ...]

  • Run a simple select query against stage schema

    Oci8Simple:Client.new("stage").run('select id, name from foos') => [[2, "lol"], [3, "hey"], ...]

  • Update something

    client.run <<-SQL
  UPDATE foos SET bar='baz' WHERE id=1233
SQL

  • Run some DDL

    client.run <<-SQL
  CREATE TABLE foos (
     ID NUMBER(38) NOT NULL
  )
SQL

  • Run some PL/SQL

    client.run <<-SQL
  DECLARE
    a NUMBER;
    b NUMBER;
  BEGIN
    SELECT e,f INTO a,b FROM T1 WHERE e>1;
    INSERT INTO T1 VALUES(b,a);
  END;
SQL

Constant Summary collapse

USER_DIR = 
File.join(ENV["HOME"], ".oci8_simple")
LOG_FILE_PATH = 
File.join(USER_DIR, "oci8_simple.log")

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(env = nil) ⇒ Client

  • env is the environment heading in your database.yml file



48
49
50
 
# File 'lib/oci8_simple/client.rb', line 48

def initialize(env=nil)
  self.env = env || "development"
end

Instance Attribute Details

#env ⇒ Object

Returns the value of attribute env.



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

def env
  @env
end

#log_file ⇒ Object

Returns the value of attribute log_file.



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

def log_file
  @log_file
end

#log_file_path ⇒ Object

Returns the value of attribute log_file_path.



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

def log_file_path
  @log_file_path
end

Instance Method Details

#config ⇒ Object 



103
104
105
 
# File 'lib/oci8_simple/client.rb', line 103

def config
  @config ||= Config.new[env]
end

#conn ⇒ Object

Create and return raw Oci8 connection



108
109
110
 
# File 'lib/oci8_simple/client.rb', line 108

def conn
  @conn ||= new_connection
end

#fetch_arrays(sql, &block) ⇒ Object 



86
87
88
89
90
91
 
# File 'lib/oci8_simple/client.rb', line 86

def fetch_arrays(sql, &block)
  conn.exec(sql) do |r|
    result = r.respond_to?(:map) ? r.map{|col| record_to_string(col)} : r
    yield result
  end
end

#fetch_hashes(sql, &block) ⇒ Object 



77
78
79
80
81
82
83
84
 
# File 'lib/oci8_simple/client.rb', line 77

def fetch_hashes(sql, &block)
  cursor = conn.exec(sql)
  col_names = cursor.get_col_names.map{|s| s.downcase.to_sym }
  while(r = cursor.fetch) do
    yield Hash[*col_names.zip(r.map {|col| record_to_string(col)}).flatten]
  end
  cursor.close
end

#record_to_string(record) ⇒ Object 



93
94
95
96
97
98
99
100
101
 
# File 'lib/oci8_simple/client.rb', line 93

def record_to_string(record)
  if record.class == BigDecimal
    record.to_f
  elsif record.class == OCI8::CLOB
    record.read
  else
    record.to_s
  end
end

#run(sql, options = {}) ⇒ Object

sql - a query options: 

:hash => true|false - default is false - return an array of hashes (with column names) 
         instead of an array or arrays



62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/oci8_simple/client.rb', line 62

def run(sql, options={})
  log(sql)
  result = []
  if(options[:hash])
    fetch_hashes(sql) do |r|
      result << r
    end
  else
    fetch_arrays(sql) do |r|
      result << r
    end
  end
  result
end