Oracleを使用した.NETアプリケーション開発の習得

Oracleでの.NETストアド・プロシージャの使用
Mark A. Williams著

Oracle Database 10g Release 2で.NETストアド・プロシージャの開発、配置、デバッグをおこなうための手順と解説

関連するダウンロード・リンク
 サンプル・コード
 Oracle Developer Tools for Visual Studio .NET 10.2以降
 Oracle Database 10g 10.2以降

2005年11月公開

.NET開発者にとってもっとも興味深いOracle Database 10g Release 2 for Windowsの機能は、自分が選択した.NET言語を使用してストアド・プロシージャを実装できる機能です。この機能は、 Oracle Database Extensions for NETによって実現されます。

Oracleを使用した.NETアプリケーション開発の習得シリーズのこの記事では、アプリケーションで.NETストアド・プロシージャを利用する方法を段階的に解説します。 具体的には、.NETストアド・プロシージャのサポートの仕組み、.NETストアド・プロシージャのサポートが有効になるようにOracle Databaseをインストールおよび設定する方法、.NETストアド・プロシージャを開発および配置する方法、.NETストアド・プロシージャをデバッグする方法について説明します。

サポートの仕組み

PL/SQLストアド・プロシージャおよびストアド・ファンクションは、Oracle Databaseと同じプロセス内で実行され、Oracle内に格納されます。 一方、.NETストアド・プロシージャは外部プロセスとして実行され、.NETのコードはコンパイルされ、".NETアセンブリ"が生成されます。.NETアセンブリは動的リンク・ライブラリ(DLL)として(通常はデータベースと同じコンピュータ上の)ファイル・システムに格納されます。 .NETアセンブリはロードされ、"CLRホスト"外部プロセスであるextproc.exe内で実行されます。extproc.exe外部プロセスはWindowsサービス <OracleHomeName>ClrAgntによって生成されます。 .NETストアド・プロシージャが呼び出されると、Oracleは、引数を渡したり、結果を取得したりして、この外部プロセスとやり取りします。 このやり取りは、Oracleのマルチスレッド・エージェント・アーキテクチャによって処理されます。 エンドユーザーにとって、.NETストアド・プロシージャ・コールは、ほかのタイプのストアド・プロシージャ・コールとまったく同じように見えます。 実際、.NETストアド・プロシージャは、PL/SQLやJavaのストアド・プロシージャを呼び出せる環境であれば、どの環境からでも呼び出すことができます。

インストールと設定

Oracle Database 10g Express Editionを使用する場合は、.NETストアド・プロシージャが自動的にインストールおよび設定されるため、別途設定作業をおこなう必要はありません。 ただし、Oracle Database 10g Standard EditionおよびEnterprise Editionでは、.NETストアド・プロシージャは、デフォルトではインストールおよび設定されません。 以下に、上記2つのエディションで.NETストアド・プロシージャを使用するための基本的な手順を示します。

  1. Windowsプラットフォーム用のOracle Database 10.2以降を ダウンロードします。 (注: .NETストアド・プロシージャはWindows以外のプラットフォームではサポートされていません)
  2. インストーラ(setup.exe)を起動します。
  3. Advanced Installation」→「 Custom」を選択します。
  4. 使用するほかのインストール・オプションに加えて、「 Oracle Database Extensions for .NET」が選択されていることを確認します。
  5. インストールが完了したら、Database Configuration Assistantを起動します(「 スタート」メニュー→「 Oracle」→「 Configuration and Migration Tools」を選択)。
  6. この支援ツールで、Oracle Database Extensions for .NETデータベース・オプションを設定するように選択します。
  7. インストールが完了したら、 <OracleHome>ClrAgentサービスが起動されていることを確認します。
  8. Oracle Developer Tools for Visual Studio .NET 10.2以降を ダウンロードおよびインストールします。 .NETストアド・プロシージャを配置するには、このバージョンが必要です。

.NETストアド・プロシージャの開発と配置

ここでは、国別コードに基づいて国名を取得する、シンプルですが完全に動作する.NETストアド・プロシージャを開発および配置します。 Oracle Database 10g Release 2に含まれているHRサンプル・スキーマに格納されているcountries表を使用します。 countries表の構造は次のとおりです。

SQL> desc countries
 Name           Null?    Type
 -------------- -------- ------------
 COUNTRY_ID     NOT NULL CHAR(2)
 COUNTRY_NAME            VARCHAR2(40)
 REGION_ID               NUMBER
ストアド・プロシージャを作成するために、Visual Studio .NET 2003の新しいプロジェクト・タイプ"Oracleプロジェクト"を使用します。

図1

新規のOracleプロジェクト"MyStoredProcedure"を作成すると、プロジェクト・ウィザードによって、Oracle Data Provider for .NETアセンブリへの参照が追加され、ODP.NETネームスペースがクラス・ファイルに追加されます。 Visual Studio .NET 2003の開発環境は次のようになります。

図2

プロジェクト・ウィザードによって生成されたStoredProcedure1プロシージャを、countries表から国名を取得する自前のコードで置き換えます。 この新しいプロシージャは整数パラメータとして国別IDを受け取り、文字列値として国名を返します。 自分でプロシージャを開発するときに注意すべき重要な点は、.NETストアド・プロシージャはスタンドアロンのプログラムで使用するコードと事実上何ら変わりはないということです。 実際、スタンドアロンのプログラムとの唯一の違いは、データベースへの接続を確立するときに使用する接続文字列だけです。 以下に、新しいプロシージャのコード全体を掲載します。

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace MyStoredProcedure
{
  /// <summary>
  /// Summary description for Class1.
  /// </summary>
  public class Class1
  {
    public static string GetCountryName(string CountryID)
    {
      // used to return the country name
      string CountryName = "";

      // Get a connection to the db
      // context connection is used in a stored procedure
      OracleConnection con = new OracleConnection();
      con.ConnectionString = "context connection=true";
      con.Open();

      // Create command and parameter objects
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "select country_name from countries where country_id = :1";
      cmd.Parameters.Add(":1", OracleDbType.Varchar2, CountryID, ParameterDirection.Input);
      
      // get a data reader
      OracleDataReader rdr = cmd.ExecuteReader();

      // get the country name from the data reader
      if(rdr.Read())
      {
        CountryName = rdr.GetString(0);
      }
      
      // clean up objects
      rdr.Close();
      cmd.Dispose();

      // Return the country name
      return CountryName;
    }
  }
}
繰り返しますが、このコードは、特殊な接続文字列を除き、クライアント・アプリケーション内に記述するコードと同じです。 connection=trueコンテキストは、コードの呼出し元のプロセスの接続をそのコードで使用することを示すものです。ストアド・プロシージャ内では、 connection=trueコンテキストしか指定できません。 OracleConnectionには新しいプロパティ IsAvailableが追加されています。このプロパティは、コードがストアド・プロシージャのコンテキストで実行されているかどうかを判定するときに使用します。 コードがストアド・プロシージャのコンテキストで実行されている場合、 IsAvailableプロパティは trueを返します。 そうでない場合は、 falseを返します。 このプロパティを使用すれば、コードがストアド・プロシージャ内で実行されているのか、スタンドアロン・アプリケーション内で実行されているのかに基づいて接続文字列を作成できます。 これにより、ほとんど手を加えることなく、コードを再利用できます。

ストアド・プロシージャを配置するには、まず、プロジェクトを作成する必要があります。 プロシージャは配置したあとにデバッグする必要があるため、プロジェクトは必ずデバッグ用にビルドします。 プロジェクトをビルドしたら、配置ウィザードを使用してデータベースにプロジェクトを配置します。 このとき、ストアド・プロシージャを配置するために、SYSDBA接続でデータベースに接続する必要があります。 Oracle Developer Tools for Visual Studio .NETのData ConnectionノードにSYSDBA接続が存在しない場合は、ウィザード内で作成できます。

ストアド・プロシージャを配置するには、Visual Studio .NET 2003のメニュー・バーで、「 Build」→「 Deploy Solution」を選択します。 これにより、配置ウィザードが開始されます。

図3

ウィザードの最初の手順で各種情報を確認したら、「 Next」をクリックします。 使用するデータベース接続を選択するように要求されます。 データベース接続をまだ定義していない場合は、「 New Connection」ボタンをクリックします。 定義してある場合は、SYSDBA権限で確立された接続を選択します。

図4

使用するデータベース接続を選択したら、「 Next」ボタンをクリックします。 この手順では、使用する配置オプションを選択します。 このプロシージャはまだ配置されていないため、デフォルトのオプションを受け入れて、アセンブリを配置し、データベース内にストアド・プロシージャ・ラッパーを作成します。

図5

Next」ボタンをクリックして、次に進みます。 プロジェクトを配置するときに使用するDLLの名前を指定します。

図6

ウィザードによって提示されたデフォルト値を受け入れ、「 Next」をクリックします。プロジェクトの配置先ディレクトリを指定します。

図7

Next」をクリックしてデフォルト値を受け入れます。 これにより、プロジェクトが%ORACLE_HOME%\bin\clrディレクトリに配置されます。 次の手順では、配置するメソッド、そのメソッドの所属先スキーマ、データベース内でのメソッドの名前、セキュリティ・レベル、型のマッピングを指定します。

図8

プロジェクトを正しく配置するには、 GetCountryNameメソッドを選択し、スキーマとしてHRを選択し、提示されたデータベース・メソッド名およびセキュリティ・レベルを受け入れます。

次の3つのセキュリティ・レベルがあります。

  • Safe: データベース・リソースに対してのみアクセスを許可します。 ローカル・ファイル・システムやネットワーク接続などのリソースへのアクセスは許可しません。
  • External: ファイル・システムやネットワーク接続などのリソースに対するアクセスを許可します。
  • Unsafe: すべてのアクセスを許可します。
データベースと.NETデータ型との入出力パラメータ・マッピングを指定するには、「 Parameter Type Mapping」ボタンをクリックします。

図9

コード内の.NET型に基づいた値が選択されます。 これらの値を適切な値として受け入れます。

配置ウィザードの最後の手順では、選択したオプションのサマリーが表示され、実行される一連のアクションを確認できます。 実際に配置を実行するには、「 Finish」ボタンをクリックします。 配置が完了したら、配置先のディレクトリを調べて、ファイルが作成されていることを確認します。

C:\>dir c:\oracle\10.2\database\bin\clr
 Volume in drive C is Local Disk
 Volume Serial Number is 94FF-538C

 Directory of c:\oracle\10.2\database\bin\clr

09/10/2005  07:18 PM    <DIR>          .
09/10/2005  07:18 PM    <DIR>          ..
09/10/2005  07:18 PM            16,384 MyStoredProcedure.dll
               1 File(s)         16,384 bytes
               2 Dir(s)  26,689,114,112 bytes free
プロシージャが正しく配置されており、正しく機能することを確認するには、SQL*Plusを使用します。
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Sep 10 19:21:47 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect hr
Enter password:
Connected.
SQL> select GetCountryName('FR') from dual;

GETCOUNTRYNAME('FR')
---------------------------------------------------------------------
France

1 row selected.
Oracle Developer Tools for Visual Studio .NETを使用してプロシージャを実行することもできます。 それには、「 HR」接続を展開し、「 Functions」ノードを展開して、「 GETCOUNTRYNAME」ファンクションを選択および右クリックして、「 Run」を選択します。

図10

Run Functionダイアログが生成されます。

図11

FRと入力し、「 OK」ボタンをクリックします。 Visual Studio .NET 2003 IDE内に、次の結果ウィンドウが表示されます。

図12

.NETストアド・プロシージャをデバッグする

配置したストアド・プロシージャをVisual Studio .NET 2003 IDE内からデバッグするには、DLLを配置したディレクトリにProgram Debug Databaseファイルをコピーする必要があります。 「 MyStoredProcedure.pdb」ファイルを%ORACLE_HOME%\bin\clrディレクトリにコピーします。

C:\>dir c:\oracle\10.2\database\bin\clr
 Volume in drive C is Local Disk
 Volume Serial Number is 94FF-538C

 Directory of c:\oracle\10.2\database\bin\clr

09/10/2005  07:32 PM    <DIR>          .
09/10/2005  07:32 PM    <DIR>          ..
09/10/2005  07:18 PM            16,384 MyStoredProcedure.dll
09/10/2005  06:44 PM            11,776 MyStoredProcedure.pdb
               2 File(s)         28,160 bytes
               2 Dir(s)  26,681,720,832 bytes free
これで、%ORACLE_HOME%\bin\clrディレクトリに、DLLと.pdbファイルが格納されました。

DLLファイルはextproc.exeプロセスによってロードされるため、DLLのデバッグをおこなうには、Visual Studio .NET 2003内で同プロセスにアタッチする必要があります。 前の手順でストアド・プロシージャを起動したため、extproc.exeプロセスはすでに実行されているはずです。 しかし、ストアド・プロシージャをまだ起動していない場合は、extproc.exeプロセスも実行されていません。 このため、コードのデバッグをおこなう前に、SQL*PlusまたはOracle Developer Tools for Visual Studio .NETを使用して、ストアド・プロシージャを起動する必要があります。

ストアド・プロシージャをデバッグするには、以下に示すように、ソース・コード内にブレーク・ポイントを設定します。

図13

次に、Visual Studio .NET 2003メニュー・バーで「 Tools」→「 Debug Processes」を選択します。 これにより、Processダイアログが生成されます。 使用可能なプロセスのリストを下方向にスクロールして、「 extproc.exe」を選択します。

図14

Attach」ボタンをクリックして、extproc.exeプロセスにアタッチします。 Attach to Processダイアログが表示されます。

図15

開発したプロシージャは.NETプロシージャなので、 Common Language Runtimeチェック・ボックスがオンになっており、そのほかのチェック・ボックスがすべてオフになっていることを確認してください。 「 OK」ボタンをクリックすると、"Attach to Process"ダイアログが閉じて、"Processes"ダイアログに戻ります。 "Process"ダイアログで「 Close」ボタンをクリックすると、デバッグ・プロセスが開始されます。

プロセスを初期化してブレーク・ポイントを作動させるには、プロシージャを呼び出す必要があります。 Visual StudioのOracle問合せウィンドウ内で、またはSQL*Plusセッションから、次のようにしてプロシージャを呼び出します。

SQL> select GetCountryName('FR') from dual;
注: .NETプロシージャのブレーク・ポイントがVisual Studioの同一インスタンス内に設定されている場合、Oracle Explorerの"Run"メニュー項目を使用して.NETプロシージャを起動しないでください。 デッドロックが発生してVisual Studioがハングします。

これによって、プロシージャが呼び出され、SQL*Plusは一見ハングしたような状態になります。 しかし、これはハングしているのではなく、Visual Studio .NET 2003内に設定したブレーク・ポイントでプロシージャが停止しているのです。

図16

ここからは、スタンドアロンのコードと同じ要領でプロシージャをデバッグします。

ただし、アタッチされたプロセスのデバッグとスタンドアロンのコードのデバッグには重要な違いが1つあります。 extproc.exeプロセスは開発環境の外に存在するため、プロセスが終了するか実行を中止しても、そのことがデバッグ環境に検出されないことがあります。 その場合は、次のようなエラーが表示されます。

SQL> select GetCountryName('FR') from dual
       *
ERROR at line 1:
ORA-28576: lost RPC connection to external procedure agent
ORA-06512: at SYS.DBMS_CLR, line 234
ORA-06512: at HR.GETCOUNTRYNAME, line 7
このエラーが発生した場合は、通常、ストアド・プロシージャを再度呼び出すことで、extproc.exeプロセスが再起動されます。 そのほかにも、呼出し元の接続を閉じて再接続する、あるいはextproc.exeの実行を中止して、CLRサービスを再起動する(extproc.exeプロセスを強制的に再生成する)といったトラブルシューティングがあります。

結論

Oracle Database 10g Release 2 for Windowsにおける.NETストアド・プロシージャのサポートに関するこの入門記事では、.NETストアド・プロシージャのサポートの仕組み、Oracleネットワーキング・コンポーネントの設定方法、.NETストアド・プロシージャの開発および配置方法、配置したプロシージャのデバッグ方法について学習しました。 これで、各開発者の環境でいつでも、.NETストアド・プロシージャの開発と配置を始めることができるはずです。


Mark A. Williams [ mawilliams@cheshamdbs.com]は、 Oracle MagazineでODP.NET関連のコラムの執筆を担当しており、Oracle ACE、Oracle Certified Professional DBAの資格をもっています。著書に、『 Pro .NET Oracle Programming』(Apress、2004年)があります。 現在は、Windows上でのOracleソリューションに重点を置いて活動しており、Oracle Technology NetworkのOracle Data Provider for .NETフォーラムにも寄稿しています。