by Hippo2000(1998/12/28,2001/5/18)
「Perlを使ってOracleに接続したい」という場合には、Win32::ODBCを使うという方法もありますが、
もう少しSCOTT/TIGERが気に入りそうな方法として、PerlのモジュールであるDBI、DBD::Oracleを使う方法とオラクル社の
004Oを使ってみたいと思います。なおここでは、本当に簡単な例しか出していません。（書く人間の能力と根気に依存しているので...）
詳細は参考資料などをお読みください。(2001/5/18) 間違っている部分を見つけて修正しました。
まだ現在のDBIのお作法から見るとマズい部分もあります。詳しくはDBIのドキュメントをご覧ください。
＃一度、全部見直さんといかんですね。
ＤＢＩとはTim Bunce氏が開発した、Perlからデータベースへアクセスするための共通のインターフェースを提供するモジュールです。
そして、ＤＢＩから実際のデータベースにアクセスするモジュールがＤＢＤです。
ＤＢＤは現在、Ｏｒａｃｌｅ、ＯＤＢＣをはじめとして、数多くのものが提供されています。
なおここの説明では、データベース別名をORADB、ユーザをscott、パスワードがtigerで、
以下のようなテーブル、プロシージャ、ファンクションが定義されているものとします。
/* テーブル */ CREATE TABLE TESTLNG ( IDX NUMBER, TAG VARCHAR2(80), MEMO LONG); /* プロシージャ */ CREATE OR REPLACE PROCEDURE INSDATA(iIDX IN NUMBER, sTag IN VARCHAR2) IS BEGIN INSERT INTO TESTLNG VALUES ( iIDX, sTag, NULL); END; / /* ファンクション */ CREATE OR REPLACE FUNCTION GETMAXIDX RETURN NUMBER IS nMax NUMBER; BEGIN SELECT MAX(IDX) INTO nMax FROM TESTLNG; RETURN nMax; END; / /* プロシージャ（値を取得） */ CREATE OR REPLACE PROCEDURE GETDATA(nIDX IN NUMBER, sTAG OUT VARCHAR2) IS BEGIN SELECT TAG INTO sTAG FROM TESTLNG WHERE IDX=nIDX AND ROWNUM=1; END; / /* プロシージャ（エラーを発生） */ CREATE OR REPLACE PROCEDURE RAISE_ERROR(nERR IN NUMBER) IS BEGIN RAISE_APPLICATION_ERROR(nERR, 'テスト用エラー発生('|| nERR || ')'); END; /
DBI、DBD::Oracleを使ってOracleに接続するためには、以下のパッケージが必要になります。
| パッケージ名 | 説 明 |
|---|---|
| DBI | DBIモジュール |
| DBD-Oracle | DBD::Oracleモジュール |
この他に、Oracleクライアントのインストールおよび設定が必要です。
実際には、「Oracle Calling Interface」を使っているようなので、これが入っていることが必要です。
（もちろんSQL*Netや通信プロトコルの部分もですけれど）
通常のインストールをして、目標のデータベースにSQL*Plusで接続できれば大丈夫なはずです。
（この辺はちょっと自信がありません）
さらに環境変数を設定しておくと便利です。
特にORACLE_HOMEは設定しておかないと、ワーニングが表示されます。
SQLの実行はできますが、「data_sources」メソッドの値がとれなくなるなどの影響があります。
またORACLE_SID、TWO_TASKを設定すると接続するデータベースへのデフォルト値を与えることができます。
データベースへの接続はデータベース・ハンドルであつかいます。
そしてデータベース・ハンドルについて複数のSQL文を発行できます。（Prepareされた）SQL文はステートメント・ハンドルで扱います。
基本的には、以下のような順序になります。
(1)データベースへの接続(Connect)
(2)ＳＱＬ文の準備(Prepare)
(3)ＳＱＬ文の実行(Execute)
(4)（SELECTの場合のみ）データの取り出し(fetch*)
fetch終了後、finishすることによりリソースを解放できる。
(5)接続の解除 (disconnect)
use DBI; #接続(別名oradbにSCOTT/TIGERでログイン） my $hDb = DBI->connect("dbi:Oracle:oradb", "scott/tiger") or die "CONNECT ERROR $DBI::errstr"; #実行（SELECT以外） $hSt = $hDb->prepare("INSERT INTO TESTLNG VALUES (1, 'Prepare+Execute', NULL)"); $nRes = $hSt->execute; #上記と同様 $nRes = $hDb->do("INSERT INTO TESTLNG VALUES (1, 'DO', NULL)"); # SELECTの場合 #準備 $hSt = $hDb->prepare("SELECT * FROM TESTLNG"); #実行 $nRes = $hSt->execute; #データの取得 while($raRes = $hSt->fetchrow_arrayref) { print join("\t", @$raRes), "\n"; #タブ区切りで出力 } $hSt->finish; $hDb->disconnect;
※ 99/1/7に間違っていた部分を修正しました.(fethRow→fethrowなど）
LONG列を取得するさいには、LongReadLen、LongTruncOkという２つのプロパティが大きく影響します。
LongReadLenは取得するLONG列に対して用意するバッファのサイズ（バイト）です。０〜２Ｇまでの範囲で指定できます。
LongReadLenが0の場合、LONG列の項目は取得しません。
LongTruncOkはLongReadLenを越えるデータがあった場合に、切り詰めるかをしめします。（0なら切り詰めない、0以外は切り詰め）
LongTruncOkが0でLongReadLenを越えるようなデータがあった場合には、エラーが発生します。
なおこの２つのプロパティはPrepareされる前に設定されていないとうまくいきません。
実際にはデータベース・ハンドルのプロパティを変更してから対象のSQL文をPrepareします。
use strict; use DBI; #Oracleでコネクト my $hDb = DBI->connect("dbi:Oracle:oradb", "scott/tiger") or die "CONNECT ERROR $DBI::errstr"; my($hSt, $rv, $sMemo); #INSERT, UPDATE はそのままできます。(DELETEではWHERE句にLONG列は指定できないので関係なし） $hSt=$hDb->prepare("INSERT INTO TESTLNG VALUES ( 2, 'LONGVAL', '" . '0123456789' x 200 . "')"); $rv = $hSt->execute; # $hSt=$hDb->do("UPDATE TESTLNG SET MEMO = '" . 'ABCDEFGHI' x 100 . "' WHERE IDX=1"); #SELECTの場合 $hDb->{LongReadLen} = 1024; #LONG列の最大長を1024 $hDb->{LongTruncOk} = 1; #切り詰めする $hSt= $hDb->prepare("SELECT MEMO FROM TESTLNG"); $rv = $hSt->execute; while(($sMemo) = $hSt->fetchrow_array) { print "MEMO :(", length($sMemo),")", $sMemo ,"\n"; } $hSt->finish; $hDb->disconnect;
PS. 本日(1998/12/28)現在、ActivePerlのDBD::Oracleモジュールでは、Oracle8のLONG列の取り扱うと
LongReadLenを１つ上回る大きさで取得されます。
これはおそらく、DBD::Oracle 0.57の障害がそのままになっているものと思われます。
先日リリースされた0.58が対応されれば、おそらく修正されるものと期待しています。
ストアド・プロシージャは通常のSQL文と同じように使うことができます。
ただしPL/SQLの標準通りにBEGIN、ENDで囲む必要があります。
use strict; use DBI; #Oracleでコネクト local $ENV{ORACLE_HOME}='c:\orawin95'; #一時的に環境変数を設定してもOK my $hDb = DBI->connect("dbi:Oracle:oradb", "scott/tiger") or die "CONNECT ERROR $DBI::errstr"; #ストアドプロシージャの呼び出し(q//を使うと便利です） my $hSt = $hDb->prepare(q/ BEGIN INSDATA(1, 'ABC'); END; /); $hSt->execute; #接続の解除 $hDb->disconnect;
DBIではプレースホルダ（Oracleではバインド変数と呼ぶもの）を使用することができます。
DBIの標準である「?」を使うことができるほか、
DBD::Oracleを使っている場合にはOracleではなじみの深い「：バインド変数名」の形式も使用できます。
バインド変数により値を与える場合には、executeメソッドの引数で指定するか、bind_paramメソッドを使って
バインド変数と値とを結び付けます。
use strict; use DBI; #Oracleでコネクト local $ENV{ORACLE_HOME}='c:\orawin95'; #一時的に環境変数を設定してもOK my $hDb = DBI->connect("dbi:Oracle:oradb", "scott/tiger") or die "CONNECT ERROR $DBI::errstr"; $hDb->do("DELETE FROM TESTLNG"); #ストアドプロシージャの呼び出し my $hSt = $hDb->prepare(q/ BEGIN INSDATA(?, ?); END; /); $hSt->execute(1, 'Execute Type'); #bind_param $hSt->bind_param(1, 2); $hSt->bind_param(2, '?-Type'); $hSt->execute; #名前付き my $hSt = $hDb->prepare(q/ BEGIN INSDATA(:nIDX, :sTag); END; /); $hSt->bind_param(':nIDX', 3); $hSt->bind_param(':sTag', 'NAMING PARAM'); $hSt->execute; $hDb->disconnect;
バインド変数により値を取得する場合には、bind_param_inoutメソッドを使ってバインド変数と領域とを結び付けます。
use strict; use DBI; #Oracleでコネクト local $ENV{ORACLE_HOME}='c:\orawin95'; #一時的に環境変数を設定してもOK my $hDb = DBI->connect("dbi:Oracle:oradb", "scott/tiger") or die "CONNECT ERROR $DBI::errstr"; #?で my($hSt, $sTag); $hSt = $hDb->prepare(q/ BEGIN GETDATA(?, ?); END; /); $hSt->bind_param(1, 2); $hSt->bind_param_inout(2, \$sTag, 80); #最大長を80バイトとする $hSt->execute; print "?:", $sTag, "\n"; #名前で $hSt = $hDb->prepare(q/ BEGIN GETDATA(:nIDX, :sTag); END; /); $hSt->bind_param(':nIDX', 3); $hSt->bind_param_inout(':sTag', \$sTag, 80); $hSt->execute; print "NAME:", $sTag, "\n"; #接続を解除 $hDb->disconnect;
実行結果
PrintErrorプロパティにより、エラーメッセージを画面に表示するかしないかを指定することができます。
デフォルトは１（出力する）です。
またエラー値、エラーメッセージは、それぞれerr、errstrというプロパティにより取得することができます。
use strict; use DBI; #Oracleでコネクト local $ENV{ORACLE_HOME}='c:\orawin95'; #一時的に環境変数を設定してもOK my $hDb = DBI->connect("dbi:Oracle:oradb", "scott/tiger") || die "CONNECT errstr"; #名前で my($hSt, $sTag); $hSt = $hDb->prepare(q/ BEGIN GETDATA(:nIDX, :sTag); END; /); $hSt->bind_param(':nIDX', 3); $hSt->bind_param_inout(':sTag', \$sTag, 80); $hSt->execute; print "NAME:", $sTag, "\n"; #エラーをそのまま表示 print $hSt->err, "\n", $hSt->errstr, "\n"; $hSt->bind_param(':nIDX', 100); $hSt->execute; #エラーを内部で取得する $hSt->{PrintError}=0; #エラーメッセージの抑止 $hSt->bind_param(':nIDX', 100); $hSt->execute; print "ERROR:", $hSt->err, "\n"; print "================\n", $hSt->errstr, "\n=================\n"; #接続を解除 $hDb->disconnect;
RaiseErrorプロパティは、発生したエラーを例外処理とするかどうかを示します。
デフォルトでは0のために、データベース処理で発生したエラーでは例外処理になりません。
0以外の値にすると、エラーが発生するとcroakをおこなうようになり、例外処理として扱うことができます。
一時的にRaiseErrorをオフにするためには、ブロックとlocalを組み合わせることが推奨されています。
ただしPerl5.004_04以前のバージョンでは、この方法は使えないために、代わりにevelを使うことができます。
evelで実行した場合には、$@にもエラーメッセージが入ります。
use strict; use DBI; #Oracleでコネクト local $ENV{ORACLE_HOME}='c:\orawin95'; #一時的に環境変数を設定してもOK my $hDb = DBI->connect("dbi:Oracle:oradb", "scott/tiger") || die "CONNECT errstr"; my($hSt, $sTag); $hSt = $hDb->prepare(q/ BEGIN RAISE_ERROR(:nErr); END; /); $hSt->{PrintError}=0; #エラー表示の抑止 $hSt->{RaiseError}=1; #エラーとする $hSt->bind_param(':nErr', -20001); #$hSt->execute; #障害になって強制終了 # #------------------------------------------------------------------------ # 一時的にRaiseErrorをはずす（推奨の方法） #------------------------------------------------------------------------ $hSt->bind_param(':nErr', -20002); { local $hSt->{RaiseError} = 0 if ($hSt->{RaiseError}); $hSt->execute; print "推奨:", $hSt->err, "\n"; print "ブロック内 ===============\n", $hSt->errstr, "\n=================\n"; } print "ブロック外:", $hSt->err, "\n"; print "ブロック外 ===============\n", $hSt->errstr, "\n=================\n"; #------------------------------------------------------------------------ #5.004_41 以前のバージョンとの互換性のために #------------------------------------------------------------------------ $hSt->bind_param(':nErr', -20003); eval { $hSt->execute; }; print "\$\@ ===============\n", $@, "\n=================\n"; print "Previous:", $hSt->err, "\n"; print "================\n", $hSt->errstr, "\n=================\n"; # $hDb->disconnect;
実行結果
Oracle Object for OLE(OO4O)は、Oracle社により提供されているオブジェクトです。
Visual BasicなどのOLEオートメーションをサポートするプログラミング環境で使用します。
ActivePerlでもOLEオートメーションをサポートしているので、使用することができます。
上記「1. DBI+DBI::Oracle」で説明したことをOO4Oで、実現した場合にどのようになるかの以下に示します。
uuse strict; use Win32::OLE; use Win32::OLE::Enum; use Win32::OLE::Variant; #セッションオブジェクトの生成 my $oSes = Win32::OLE->new ('OracleInProcServer.XOraSession'); #-------------------------------------------------------------------------------- #データベースへの接続 #-------------------------------------------------------------------------------- my $oDb = $oSes->OpenDatabase("oradb", "scott/tiger", 0); #-------------------------------------------------------------------------------- #1. ＳＱＬの実行 #1.1 文字列を埋め込んだ形でのDELETE, INSERT,UPDATE #-------------------------------------------------------------------------------- my($nRes, $sWk); $nRes = $oDb->ExecuteSQL("DELETE FROM TESTLNG"); $nRes = $oDb->ExecuteSQL("INSERT INTO TESTLNG VALUES('1', 'EXECUTE', NULL)"); $nRes = $oDb->ExecuteSQL("UPDATE TESTLNG SET TAG='EXECUTE(UPD)' WHERE IDX=1"); #-------------------------------------------------------------------------------- #1.1.1 エラーはErrno, ErrStrで取得できる #-------------------------------------------------------------------------------- $nRes = $oDb->ExecuteSQL("BEGIN RAISE_ERROR(-20001); END;"); print "ERRNO:", $oDb->{LastServerErr}, "\n"; print "ERRSTR:", $oDb->{LastServerErrText}, "\n"; #-------------------------------------------------------------------------------- #1.2 バインド変数を使った場合 #-------------------------------------------------------------------------------- $oDb->Parameters->Add("nIDX", 2, 1); #値は２、入力用 $oDb->Parameters->Item("nIDX")->{ServerType} = 2; #ORATYPE_NUMBER $oDb->Parameters->Add("sTag", 'ExecuteSQLとバインド変数', 1); #値は２、入力用 $oDb->Parameters->Item("sTag")->{ServerType} = 1; #ORATYPE_VARCHAR2 $nRes = $oDb->ExecuteSQL("INSERT INTO TESTLNG VALUES(:nIDX, :sTag, NULL)"); #-------------------------------------------------------------------------------- #1.3 CreateSQLとバインド変数 #-------------------------------------------------------------------------------- $oDb->Parameters->Item("nIDX")->{Value}= 3; $oDb->Parameters->Item("sTag")->{Value}= "Createとバインド変数"; my $oStm = $oDb->CreateSQL("INSERT INTO TESTLNG VALUES(:nIDX, :sTag, NULL)", 0); #-------------------------------------------------------------------------------- # 1.3.1 連続実行 #-------------------------------------------------------------------------------- $oDb->Parameters->Item("nIDX")->{Value}= 4; $oDb->Parameters->Item("sTag")->{Value}= "追加"; $oStm->Refresh; #-------------------------------------------------------------------------------- #2. ストアドプロシージャ #2.1 文字列を埋め込んだ形での実行 #-------------------------------------------------------------------------------- $nRes = $oDb->ExecuteSQL("BEGIN INSDATA(5, 'INSDATA1'); END;"); #バインド変数を使用 $oDb->Parameters->Item("nIDX")->{Value}= 6; $oDb->Parameters->Item("sTag")->{Value}= "ストアドプロシージャ BIND"; $nRes = $oDb->ExecuteSQL("BEGIN INSDATA(:nIDX, :sTag); END;"); #-------------------------------------------------------------------------------- #2.2 バインド変数を使用(CreateSQLを使用） #-------------------------------------------------------------------------------- $oDb->Parameters->Item("nIDX")->{Value}= 7; $oDb->Parameters->Item("sTag")->{Value}= "ストアドプロシージャ Craeate 1"; $oStm = $oDb->CreateSQL("BEGIN INSDATA(:nIDX, :sTag); END;", 0); $oDb->Parameters->Item("nIDX")->{Value}= 8; $oDb->Parameters->Item("sTag")->{Value}= "ストアドプロシージャ Craeate 2"; $oStm->Refresh; #-------------------------------------------------------------------------------- #3. SELECTの実行（通常の場合） #3.1 Dynasetの作成 #-------------------------------------------------------------------------------- my $oDyna = $oDb->CreateDynaset("SELECT * FROM TESTLNG ORDER BY IDX", 0); my $oFlds = Win32::OLE::Enum->new($oDyna->{Fields}); my @lFlds = $oFlds->All(); #-------------------------------------------------------------------------------- #3.2 データの取り出し #-------------------------------------------------------------------------------- while(!($oDyna->{EOF})) { foreach $sWk (@lFlds) { print $sWk->{value}, "\t"; } print "\n"; $oDyna->MoveNext; } #-------------------------------------------------------------------------------- #4. LONG列の取り扱い #4.1 INSERT # 4.1.1 LONG列でも64KBまでは通常の形で使える #-------------------------------------------------------------------------------- my($sMemo); $sMemo = "0123456789" x 10; $oDb->ExecuteSQL("INSERT INTO TESTLNG VALUES(9, 'LONG (SHORT)', '$sMemo')"); #-------------------------------------------------------------------------------- # 4.1.2 AppendChunkを使った例 #-------------------------------------------------------------------------------- $oDyna = $oDb->CreateDynaset("SELECT * FROM TESTLNG", 0); $oDyna->AddNew; $oDyna->{Fields}->Item('IDX')->{Value} = 10; $oDyna->{Fields}->Item('TAG')->{Value} = 'LONG ADD'; #$oDyna->{Fields}->Item('MEMO')->{Value}= ''; my($i); for($i=0; $i<1000; $i++) { $oDyna->{Fields}->Item('MEMO')->AppendChunk("1234567890"); } $oDyna->Update; #-------------------------------------------------------------------------------- # 4.2 LONG列の取り出し # 4.2.1 LONG列でも64KBまでは通常の形で使える #-------------------------------------------------------------------------------- my $oDyna = $oDb->CreateDynaset("SELECT * FROM TESTLNG WHERE IDX=9", 0); my $oFlds = Win32::OLE::Enum->new($oDyna->{Fields}); my @lFlds = $oFlds->All(); while(!($oDyna->{EOF})) { foreach $sWk (@lFlds) { print $sWk->{value}, "\t"; } print "\n"; $oDyna->MoveNext; } #-------------------------------------------------------------------------------- # 4.2.2 GetChunkを使った例 #-------------------------------------------------------------------------------- my($nChunkSize, $nCurSize, $nBgn); $oDyna = $oDb->CreateDynaset("SELECT * FROM TESTLNG WHERE IDX=10", 0); while(!($oDyna->{EOF})) { print "IDX:", $oDyna->{Fields}->Item('IDX')->{Value}, "\n"; print "TAG:", $oDyna->{Fields}->Item('TAG')->{Value}, "\n"; $sMemo = ""; $nChunkSize=1024; $nBgn = 0; do { $sWk = $oDyna->{Fields}->Item('MEMO')->GetChunk($nBgn, $nChunkSize); $nCurSize = length($sWk); $sMemo .= $sWk; $nBgn += $nCurSize; } until ($nCurSize < $nChunkSize); print "MEMO:", $sMemo, "\n"; $oDyna->MoveNext; }
実行結果
DBD::OracleのREADMEを見ますと、
Install enough Oracle software to enable DBD::Oracle to build.
That usually includes Pro*C.
DBD-Oracle0.58/README
*** *BEFORE* BUILDING, TESTING AND INSTALLING this you will need to:より
と書いてあり、まるでPro*CがないとDBD::Oracleが使えないようにも見えます。
しかし実際には、Oracle Calling Interfaceライブラリがインストールされ、さらにコンパル環境がキチンと整備されていれば、
インストールできるようです。
事実、私の会社のUltra5(Solaris)でもPro*Cは入っていませんが、インストールできています。
これについては、あくまでも経験的に「動いている」というだけで、裏づけとなる証拠はありません。
でもこっちのほうが、LONGの扱いが正確なんですよね。今のところ。
この文書をつくるのにあたり、以下のサイトのお世話になりました.
ご意見、ご質問はこちらの掲示板で受け付けています。
またメールは河馬屋(Nifty)にお願いします。