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

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

プロジェクト・ウィザードによって生成された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;
    }
  }
}

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

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

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

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

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

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

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

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

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

• Safe： データベース・リソースに対してのみアクセスを許可します。 ローカル・ファイル・システムやネットワーク接続などのリソースへのアクセスは許可しません。
• External： ファイル・システムやネットワーク接続などのリソースに対するアクセスを許可します。
• Unsafe： すべてのアクセスを許可します。

コード内の.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: 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.

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

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

.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

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

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

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

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

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

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

SQL> select GetCountryName('FR') from dual;

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

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

ただし、アタッチされたプロセスのデバッグとスタンドアロンのコードのデバッグには重要な違いが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

結論

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