Oracle SQLDeveloperでMySQLを操作する

Oracle社は SQL Developer という Javaベースの GUI DBクライアントを無償で配布している。
普通に考えればそれは Oracle DB専用のツールなのだろうが、実はMySQLや Microsoft SQL Serverへ接続することも出来る。
（Oracle社の立場からすれば、このツールを使ってぜひ Oracle DBへ移行して下さいというのが表向きのところだろう。しかし、そこはそれ）

MySQLをGUIで操作する方法といえば、Microsoft Accessと ODBCドライバを組み合わせて使用するか、Navicatのような商用ソフトウェアか、phpMyAdminのようなWebベースのツールを使うのが一般的であり、よもや Oracle社の無償ツールが使えるなどということを知る人は少ないのではないだろうか。

下記は、SQL DeveloperでMySQLを操作する方法のメモ。

OTN(Oracle Technology Network)から SQL Developer(現在のバージョンは1.2.1)をダウンロード出来る。動作させるにはJRE1.5が必要。
Mac版は 日本のOTNには置いていないようなので、USのOTNからダウンロードすること（但しUIは日本語化されていない）。

SQL Developerには MySQLの JDBCドライバが標準では添付されていないため、展開した SQL Developerの jdbc/libフォルダに mysql-connector-java-*-bin.jarを自分でコピーする。
（Macの場合 SQLDeveloper.app/Contents/Resources/sqldeveloper/jdbc/lib）

SQL Developerを起動し、Tools->Preferences->Database->Third Party JDBC Drivers から先刻コピーした mysql-connector-java-*-bin.jarを追加してやれば、SQL Developerで MySQLへのコネクションを張ることが出来る。

なおUTF-8オンリーの環境でしか動作を確認していないので、他のエンコーディングが関係している場合は文字化けなどを起こすかもしれない。その手の問題に対する対処法についてはここでは取り扱わないので各自で解決されたし。というかUTF-8を使え。

なおMicrosoft SQL Serverへ接続したい場合も JDBCドライバの jarファイル(sqljdbc.jar)を同様にしてセットしてやれば良い。

Railsと Oracle

大抵 Rails入門の記事は MySQLが対象になっているので Oracle用の使い方をメモしておく。

まず Oracle Clientが必要。
Oracle (Instant) Clientを入れて SQL*Plusが動くような状態になったら、ruby-oci8 を入れる。Gentooなら emerge ruby-oci8 で良い。

ここでは personオブジェクトを格納するテーブル people を作り、それに対するCRUD操作を提供するWeb画面を作る例を示す。
（ちなみにクラス名は単数形、テーブル名は複数形。普段英語を使い慣れていない向きには「どっちが複数だっけ？」と悩むこともしばしばだが、Rails(ActiveRecord)の「規約」はそういうことになっている。bookクラスのオブジェクトを格納するテーブルは booksね）

なお Railsアプリケーションの雛形は railsコマンドで既に出来ており、そのディレクトリの中にいるものとする。

SQL*Plusでデータベースに接続し、テーブルを作る。

create table people (
  id number(10) primary key,
  name varchar(255)
);
create sequence people_seq;

「idと名の付く列はオートナンバーの適用されるキー列であり、順序から値が移入される。順序名はテーブル名に_seqを付けたもの」というActiveRecord(+Oracle)の「規約」のようである。この割り切りがCoCというやつか。
Oracleデータベースにオートナンバーの処理を行わせるにはトリガーの作成が必須だが、ActiveRecordは自分で順序からID列へ値を移入するため、この場合はデータベース側にトリガーを作るべきでない。

app/models/person.rbというファイルを下記の内容で作成。personは単数形、personは単数形・・・ぼそぼそ

class Person < ActiveRecord::Base
end

とりあえず、中身の記述無し。普通に Javaの考え方だと、エンティティクラスを作るときにはこのテーブルに一体どんな列が含まれているのかまで書いてやらなければならないのだが、ActiveRecordsでは実行時に勝手にDBへ問い合わせて内容を決定できるので、これでも動作する。これもDOAというのだろうか。

conf/database.yml というファイルにデータベースの設定を書き込む。この設定ファイルには「開発用DB(development)」「テスト用DB(test)」「本番用DB(production)」の設定をそれぞれ書くことが出来る。
繰り返しになるが世の中にある Railsの情報はほとんど MySQL向けに書かれているため、Oracleの場合は少し違うことを書かなければならない。

development:
  adapter: oci
  username: scott
  password: tiger
  host: mydbhost/XE

adapterが ociになること。database指定がない(OracleとMySQLの違いをわかっている人なら理由は自明だろう)こと。hostは TNS名もしくはホスト名/サービス名の形式を取ること。

上記の設定が済んだら、generate scaffold を実行し、personオブジェクト用のCRUD操作画面を自動生成する。

$ ruby script/generate scaffold person




これで peopleというコントローラが作成され（こっちは複数形）、Webからデータの一覧や詳細を見たり、作成したり、編集したり、削除したりできるようになった。一覧表示のURIは (railsアプリケーションのルート)/people/list になる。

あと、OracleのNLS_LANGが UTF8になっているなら、config/environment.rbに $KCODE = 'u' の1行を追加しておいたほうがいいと思う。

Gentooに Oracle Clientをインストールする

Oracleアプリケーションを Javaで開発する分には、大抵 Type4(thin)ドライバで何とかなるのでネイティブの Oracle Clientを入れる必要がないのだが、ネイティブアプリケーションや Railsから Oracleにアクセスしたい場合どうしても Oracle Clientは必要となる。

Gentooの Portageには Oracle Instant Client があるので、emergeですぐにインストール出来る・・・といいたいところだが、プロプライエタリなソフトウェアなのでバイナリを Oracleのサイトからダウンロードして /usr/portage/distfiles に配置してやる必要がある。

# emerge oracle-instantclient-sqlplus

それらのファイルを配置せず上記 emergeコマンドを実行すると、入手先やファイル名などを指示するメッセージが出力され emergeが一度停止するので、言われたとおりのファイルを入手・配置して再度 emergeをする。

ORACLE_HOMEの設定は自動的に行われるので、emergeが済んだらすぐに SQL*Plusを使ってテストが出来る。
前にも書いたことがあるが、最近の Oracle Clientは tnsnames.oraにTNS名を定義しなくても直接 DBサーバへ接続できるのでそのあたりの手間は随分省けるようになった。
下記は mydbhost 上で動作している Oracle Expressに対して SQL*Plusで接続を行う例。
（なお Oracle Expressのインスタンスはサービス名が常に xe となっている）

$ sqlplus scott/tiger@mydbhost/xe

ただ、NLS_LANGの設定は自動的には行われないので、必要なら /etc/env.d/50oracle-instantclient-basic ファイルに
NLS_LANG=japanese_japan.utf8
などの設定を追加してやること。

Oracle用の DataSourceをすぐに作る

あなたは、Oracleデータベースにアクセスする、とあるビジネスコンポーネントを Javaで製造することを命じられました。
コンポーネントには DataSourceオブジェクトがあらかじめインジェクトされることが前提になっているので、あなたは各ビジネスメソッドの中でその DataSourceからデータベース・コネクションを取得し、クエリを実行したのち、正しくコネクションを解放して制御を戻せば良いことになっています。
トランザクションの境界については、フレームワーク側で定義するため意識する必要がないとのことです。

さて、あなたは JDBCを使って目的のクエリを行うコードを書きました。簡単です。しかし、ユニットテストをしなければこのコードが要求仕様を満たしているという保証ができません。ではこのコンポーネントをユニットテストしましょう。
ユニットテストを行うためには、接続先のデータベースが必要です。幸い、開発用のOracleデータベースは用意されています。

しかしながら、結合時にはフレームワークにより自動的にインジェクトされることになっているこの DataSourceも、ユニットテストの時には自分で作成してテスト対象のオブジェクトにインジェクトしてやらなければいけません。
しょうがないのであなたは JavaDoc で、javax.sql.DataSource調べます。
「なんてことだ！こいつはインターフェイスだ！インスタンスを作りたくても new できないじゃないか！」

OracleのJDBCドライバアーカイヴには OracleDataSource という DataSourceの実装が含まれていますので、これを使いましょう。

import oracle.jdbc.pool.OracleDataSource;
:
OracleDataSource ds = new OracleDataSource( );
ds.setUser("scott");
ds.setPassword("tiger");
ds.setURL("jdbc:oracle:thin:@//dbHost/service_name");
// テスト対象オブジェクトに ds をインジェクトする

むろん、これは Oracle専用の DataSource実装です。データベース非依存の DataSource実装を使っておきたければ SpringのSingleConnectionDataSourceあたりを使うと良いでしょう。

cronとexpユーティリティを使ったOracleのかんたんバックアップ

最近の Linuxだと、/etc/cron.daily に置いてあるスクリプトが勝手に毎日実行されるようになっていると思う。
なので、これを使って Oracleのデイリーバックアップをしてみよう。

※この記事で扱っている内容を実際に運用した結果について責任は持てませんので、やるなら必ず自分で検証してからにしてください。

私の作成した /etc/cron.daily/exp.cron は下記。

#!/bin/sh
USER=hr # Oracleユーザー名
PASSWORD=secret # パスワード
FILE=$USER-`date +%a`.dmp
echo "exp $USER/$PASSWORD FILE=$FILE && gzip --force $FILE" | su - oracle

/etc/cron.dailyに配置されたスクリプトは cronデーモンにより root権限で実行されるが、私の場合 rootユーザにOracle用の環境設定をしておらず、かわりに oracle ユーザに対してORACLE_HOMEなどの設定をしてある。そのためこのスクリプトでは expコマンドを su - oracle で呼び出している。suコマンドに - オプションがついているので、expコマンドはユーザ oracleのホームディレクトリで実行される。そのためこのスクリプトではバックアップファイルもそこに出力されることになる。

また、このスクリプトでは1ユーザのスキーマのみexpしている。もしデータベース全体のバックアップを取る必要がある場合は、expコマンドに与えるユーザーとしてDBA権限（Oracleにデフォルトで存在するのは sys, system など）を持ったものを選択し、FULL=y を指定する。

出力先ファイル名として、`date +%a` を用いている。このコマンドは曜日を返す。つまり、曜日毎に違うファイル名でバックアップファイルを出力するので、常に一週間分のバックアップが保持されることになる。

cronの実行中に出力された内容はrootにメールされるが、その中に expコマンドから出力された「EXP-00091: 不審な統計をエクスポートしています」という警告があるかもしれない。
不審という言葉もどうかと思うが、これは「統計情報をエクスポートしたものの、（なんらかの理由で）これをインポートしても使えないかもよ？」という意味である。この警告が出力される原因は色々あるようだが、統計情報はインポート後に再作成できるので、よほどテーブルが巨大で統計に時間がかかるというのでなければこの警告は無視して構わないと思う。

ついでにメモしておくと、統計情報の再作成をするには、プリセットのANALYZE_SCHEMAプロシージャをコールするのが早い。
（'HR' はユーザー名で置き換えること。大文字小文字の区別あり）

call DBMS_UTILITY.ANALYZE_SCHEMA('HR','compute');

統計情報ってなに？という人はOracleの CBO（コストベース・オプティマイザ）について調べると良い。

スコットさんはもういない

Oracle Express Edition(XE)をインストールするとデフォルトで HR というサンプルスキーマが作成される。HRが何の略かは知らない。HardRockか？
最初HRはアカウントロックされた状態なので、使うならロックを解除してやる必要がある。apexでやればすごく簡単。
apexでアカウントロックを解除する際、同時にパスワードも設定できるが、信頼できないネットワークからのアクセスを受けるノードなら少なくともTIGERやMANAGERやCHANGE_ON_INSTALLはやめておいたほうがいいと思う。

AccessとOracle、#Deletedの謎

Oracleで、Accessのオートナンバーと似たことをやろうとするとシーケンスとトリガを使ってこうなる。
"ID"がオートナンバーにしたい列ね。

CREATE TABLE "領収書"
( "ID" NUMBER(7,0),
"日付" DATE,
"発行元" NVARCHAR2(64),
"但し書き" NVARCHAR2(64),
"税込金額" NUMBER(8,0),
"領収書番号等" NVARCHAR2(64),
"主な品目" NVARCHAR2(64),
"決済方法" NVARCHAR2(64),
"記帳済み" NUMBER(2,0),
"仕分け候補" NVARCHAR2(64),
CONSTRAINT "領収書_PK" PRIMARY KEY ("ID") ENABLE
);

CREATE SEQUENCE "領収書_SEQ" NOMAXVALUE NOCACHE NOORDER NOCYCLE;

CREATE OR REPLACE TRIGGER "BI_領収書"
before insert on "領収書"
for each row
begin
select "領収書_SEQ".nextval into :NEW.ID from dual;
end;

ALTER TRIGGER "BI_領収書" ENABLE;


実はapex経由でテーブルを作成すると、主キー用のシーケンスとトリガ（上記みたいなの）を自動的に作ってくれるオプションが使えるのでいちいちやりかたを覚えなくて良い上記のようなことは覚えなくて良い。とはいえWeb上のフォームでポチポチやるよりもDDL文を手で書いて実行した方が早いので今後のためにメモ。

それはそうと、こうして作った「主キー列をDBMS側で自動生成する」テーブルを Access(2000とか2007とか)からリンクテーブルとして使おうとすると、行をINSERTするたびにいちいち"#Deleted"になってしまう。
なんでだろうと思って色々試してみたのだけれど、日本語のテーブル名やカラム名は問題ないみたいなのだが、どうやらVARCHAR2列の最大長256以上？だとそうなるみたいだ。ODBCの型にうまくマッピングできないんだろうか。
他にもID列の長さがint型の範囲を超えている（小数部があったり、桁数が10桁以上あったり）場合や、ODBCドライバが古い場合などにも#Deletedが出るとのこと。

Oracle10g Instant Client Windows ODBC

なんですかこの Google先生に教えを請うかのような題名は。

Oracle Express Edition(XE) のおかげで手軽に Oracleが使えるようになったので、オフィスワーク用に使うことにしてみた。
そんなの PostgreSQLや MySQLを使えばいいんじゃないの？というなかれ、Oracle向けのノウハウ（バッドノウハウがほとんどだが）やツール資産、国際化対応、ドライバ（特にODBC）の成熟というものに関してはオープンソース系のそれとは歴史の長さがまるで違うのだ。
だからオープンソースより良いって主張したいわけじゃないけどね。単なる物好きだ！悪いか！

しかしそんなOracleもユーザーインターフェイス無しにはただの入れ物、使いようがない。
実は XEについてくる apexなるWeb UIの出来が良いのでそれでもまあまあ良いんだけど、やはり Wordや Excelくらいしかしか使ったことのない事務系スタッフにでも使える UIを提供しようとすれば Accessから ODBC経由で使ってもらうのが一番簡単で高効率だ。というか、この分野(わーどやえくせるしかつかったことのないじむけいすたっふによろこんでつかってもらえるでーたべーすふろんとえんど)で Accessに匹敵するものは未だに存在しないと確信している。（そりゃそうだよね前提条件がWordやExcelの経験なんだから）

Oracleのフロントエンドとして Accessでアプリケーションを構築したことのある人はどんな悪夢をしっているかな？
そのひとつが、「各端末に余すことなく Oracle ClientをインストールしてTNSを構成しなきゃいけない」じゃないだろうか。
（「WinMeにインストールできない（Oracleの仕様）」っていうのもあるだろうけどそれは記憶の彼方に葬るとして）

ところが最近の Oracle Clientではインストール時に Instant Clientという方法が選択できて、これが「普通の利用に必要な最小構成」を割とうまくセットアップしてくれるのだ。

というわけで我がオフィスでも、この間インストールした XEで名刺管理をするために（なんという些細な仕事にOracleを使うことか）、Windowsマシンに Instant Clientを入れてみたのでこの記事はそのメモ。っていうか前振り長いよ。

Oracle 10g ClientのWindows用をダウンロードしたら setupを実行して Instant Clientを選択、「次へ」「次へ」・・・でインストール完了。あれ、ORACLE_HOMEは？構成の選択は？TNSは？

AccessからODBC経由でOracleを利用するため、コントロールパネル→管理ツール→ODBC で、DSN定義を行う。
「ほらみたことか、TNSサービス名を要求されるじゃないか！メモ帳でtnsnames.oraを書かなきゃ！ORACLE_HOMEは何処？」
落ち着きなさいあなた！試しにTNSサービス名のかわりに、ホスト名/サービス名を書いてごらん。
（ryoko.localはうちのOracleホスト名、xeは Express Editionのインストールで勝手につけられるサービス名）

あ、なに・・・これでつながっちゃうのね。tnsnames.oraもORACLE_HOMEも設定しなくていいんだ、便利になったなあ。

うんうん、わかってくれてうれしいよ。でも実はNLS_LANG環境変数だけは設定しないとだめでした。
（設定しないとODBCドライバの構成ダイアログが全部英語になるばかりか、オブジェクト名などで日本語が通らない）
うちの場合は、JAPANESE_JAPAN.JA16SJISTILDEに。これが最良なのかは知らない。

Oracle Express EditionをGentooに入れる

Oracle Express Edition(Oracle XE)は、4GBまでのデータを格納できる無料の Oracle。

Oracle XEは rpm又はdebで配布されている。ここではrpmを使う。
Oracleの動作にはlibaioとbcも必要なようなのでそれもemergeする。

# emerge libaio bc rpm

/etc/sysctl.confに下記を追記して、sysctl -pで反映しておく。

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

グループdbaとユーザーoracleを作成する。
本当はインストールスクリプトで自動作成するようになっているらしいんだけど、私のところではうまくいかなかったので手動で。

# groupadd dba
# useradd -m oracle -g dba -G wheel

rpmを使って Oracle XEのパッケージをインストール

# rpm -ivh oracle-xe-univ-ほげほげ.i386.rpm --nodeps

ファイル名に univ って入っている方のパッケージじゃないとデータベースの文字セットがWesternになってしまうので日本人は悲しい目にあうため注意。univって入っている方のパッケージだと内部文字セットにUNICODEが使われる。シフトJISやらEUCやらはXEではサポートされていないというもっぱらの噂。

rpmのインストールが済んだら、/etc/init.dに cdして下記のコマンドを入力（引用元：Gentoo Linux Wiki）。
なにやら Gentoo用に initスクリプトをちょっと直す必要があるみたい。

# sed -i -e "s/\$SU -s \/bin\/bash \{1,2\}\$ORACLE_OWNER -c/\$SU \$ORACLE_OWNER -l -c/g" oracle-xe


ここでハマりどころひとつ。（いや、普通の人はそんなところではまらないのだけど）
/etc/hostsに自分のホスト名が書かれてないと Oracleは正常動作しない。
初期化スクリプトが妙に早く完了したなと思ったら全然データベースが出来てない。
（通常のOSインストール手順を踏まずに仮想マシンとかで手早くセットアップした環境だとそういう問題が起こりうる）

下記コマンドで初期DBを作成する。Web管理画面やリスナーのポート番号、SYSなどの初期パスワードを聞かれる。

# /etc/init.d/oracle-xe configure

質問に答えたあとしばらく処理が行われているようだったら成功の可能性大。
時間をおかずにすぐプロンプトに帰ってくるようだと、おそらく初期DBの作成に失敗したか、あなたのマシンがアホみたいに速いかのどちらか。ちなみに私のところでは Athlon64X2 5200+とRAIDの環境で十数秒〜数十秒かかったと思う。

初期DBの作成が済んだら rc-update add oracle-xe default して自動起動にするとか、http://yourhost:8080/apex にアクセスして Web管理画面から色々やってみるとか、クラシカルに sqlplusでつないでみるとかすれば良い。

但しsqlplusを動かすためには当然伝統的な ORACLE_HOMEとかの設定が必要。

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export ORACLE_SID=XE
export NLS_LANG=japanese_japan.UTF8
export PATH=$ORACLE_HOME/bin:$PATH

インスタンスの名前は XE になる。TNS記述子もその名前で作られる。
変更できるのかは知らない（TNS記述子は変更できるだろうけど・・・）

注意：64bit環境の人は何とかして 32bit版の libaio.so.1を /usr/lib32 に入れてやらないと動かない。