RubyでOracle Databaseにアクセスしてみる

RubyからOracle Databaseにアクセスしselect文を発行してみる。

今回はWindows7上のRubyで実行。
まず、RubyからOracleにアクセスするためのライブラリruby-oci8をgemでインストール。

>gem install ruby-oci8
Fetching: ruby-oci8-2.1.7-x86-mingw32.gem (100%)
Successfully installed ruby-oci8-2.1.7-x86-mingw32
1 gem installed
Installing ri documentation for ruby-oci8-2.1.7-x86-mingw32...
file 'ext/oci8' not found
Installing RDoc documentation for ruby-oci8-2.1.7-x86-mingw32...
file 'ext/oci8' not found

以下のようなRubyソース作成。

# coding: UTF-8
require 'oci8'

user='hr'
pass='hr'
tns='orcl'
sql="select * from EMPLOYEES where rownum <= 10"

begin
  conn = OCI8.new(user, pass, tns)
  cursor = conn.exec(sql)
  
  # テーブル定義表示
　puts cursor.get_col_names.join(',')
  puts cursor.column_metadata

  # テーブルデータ表示
  while row = cursor.fetch
    puts row.join(',')
  end

  # データ取得件数表示
  puts "#{cursor.row_count} rows fetched."

  cursor.close
  ensure
  conn.logoff if conn
end

cursor.column_metadataで検索対象テーブルのメタデータ(列定義)をとることができて、
あとはcursor.fetchで1行ずつデータがとれる。

以下、実行結果

EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID
"EMPLOYEE_ID" NUMBER(6) NOT NULL
"FIRST_NAME" VARCHAR2(20)
"LAST_NAME" VARCHAR2(25) NOT NULL
"EMAIL" VARCHAR2(25) NOT NULL
"PHONE_NUMBER" VARCHAR2(20)
"HIRE_DATE" DATE NOT NULL
"JOB_ID" VARCHAR2(10) NOT NULL
"SALARY" NUMBER(8,2)
"COMMISSION_PCT" NUMBER(2,2)
"MANAGER_ID" NUMBER(6)
"DEPARTMENT_ID" NUMBER(4)
198,Donald,OConnell,DOCONNEL,650.507.9833,2007-06-21 00:00:00 +0900,SH_CLERK,2600.0,,124,50
199,Douglas,Grant,DGRANT,650.507.9844,2008-01-13 00:00:00 +0900,SH_CLERK,2600.0,,124,50
200,Jennifer,Whalen,JWHALEN,515.123.4444,2003-09-17 00:00:00 +0900,AD_ASST,4400.0,,101,10
201,Michael,Hartstein,MHARTSTE,515.123.5555,2004-02-17 00:00:00 +0900,MK_MAN,13000.0,,100,20
202,Pat,Fay,PFAY,603.123.6666,2005-08-17 00:00:00 +0900,MK_REP,6000.0,,201,20
203,Susan,Mavris,SMAVRIS,515.123.7777,2002-06-07 00:00:00 +0900,HR_REP,6500.0,,101,40
204,Hermann,Baer,HBAER,515.123.8888,2002-06-07 00:00:00 +0900,PR_REP,10000.0,,101,70
205,Shelley,Higgins,SHIGGINS,515.123.8080,2002-06-07 00:00:00 +0900,AC_MGR,12008.0,,101,110
206,William,Gietz,WGIETZ,515.123.8181,2002-06-07 00:00:00 +0900,AC_ACCOUNT,8300.0,,205,110
100,Steven,King,SKING,515.123.4567,2003-06-17 00:00:00 +0900,AD_PRES,24000.0,,,90
10 rows fetched.