Oracleのストアド・プロシージャをVB／C#で記述する方法

bgx5810

はじめに　Oracle Database 10g Release2では、データベースのストアドプロシージャ／ファンクション（以下、ストアドプロシージャ）を.NET準拠言語であるVB.NETやC#で作成できます。これらの.NET準拠言語で作成されたストアドプロシージャを「.NETストアドプロシージャ」と呼びます。そして、これを実現するのがOracleDatabase Extensions for .NET（以下、ODE.NET）です。
　通常、OracleのストアドプロシージャはPL/SQLという言語で記述する必要がありますが、ODE.NETを利用すると、PL/SQLを利用したことのない開発者でも、VBやC#といった言語でOracleのストアドプロシージャを作成できます。今回は、ODE.NETを利用して.NETストアドプロシージャを記述してみましょう。
対象読者

• VB、もしくはC#でOracleのストアプロシージャを記述したい人。

必要な環境　Visual Studio 2005 Standard Edition以上が必要です。また、ODE.NETはOracleDatabase 10g Release2（10.2.0）から実装された機能で、Windows上で動作するOracleに限定されます。OracleのEditionはStandardEdition ONE以上から利用可能です。その他、.NETストアドプロシージャの開発、デプロイを行うために、Oracle Developer Tools for Visual Studio .NET 10.2（以下、ODT 10.2）が必要になります。
ODE.NETのインストール（1/2）　ODE.NETをインストールしてみましょう。ODE.NETはOracleデータベース側の機能なので、Oracleをインストールする時にODE.NETも一緒にインストールします。注意点として、ODE.NETはデフォルトではインストールされないため、データベースをインストールする際のインストール・タイプで［カスタム］を選択します。


既にOracleをインストール済みの場合
　既にOracleをインストール済みの環境では、再度インストーラを起動することで追加インストールが可能です。この場合は、Oracle Home（Oracleがインストールされているディレクトリ）をインストール先として指定してください。

　次に、インストールするコンポーネントとして［Oracle Database Extensions for .NET 10.2.0.1.0］にチェックを入れます。


　データベースのインストール後、データベース・コンフィギュレーション・アシスタント（DBCA）でデータベースを作成する際に、データベース・テンプレートとして［カスタム・データベース］を選択し、データベース・コンポーネントに［.NETのOracle DatabaseExtensions］を選択する必要があります。


　以上で、ODE.NETがインストールされました。ただし上記の作業でインストールされたODE.NETのバージョンは10.2.0.1.0で、.NET Framework 1.xに対応したODE.NETになります。.NET Framework 2.0に対応したODE.NETは、Oracle Technology Network（以下、OTN）からダウンロードします。


　上記サイトから、［Oracle Data Provider for .NET 2.010.2.0.2.20］をクリックし、［Oracle Developer Tools for Visual Studio .NET withODAC10.2.0.2.20］をクリックします（ここからはOTNへのログインが必要になり、ログインしていない場合は、ログインを行う画面が表示されます）。
　ライセンス契約書が表示されるので、すべての内容を確認して必要なチェックボックスをオンにし［同意する］ボタンをクリックすると、ファイルのダウンロードページが表示されます。［ODTwithODAC10202.exe］をクリックすると、ファイルのダウンロードが開始します。
　ダウンロードした「ODTwithODAC10202.exe」は自己解凍形式の圧縮ファイルになっていますので、エクスプローラからダブルクリックして解凍すると、「stage」と「install」という2つのフォルダが作成されます。「install」フォルダから「setup.exe」を実行し、Oracle UniversalInstaller（以下、OUI）起動させ「ようこそ」の画面で、［次へ］ボタンをクリックします。


　「インストールする製品の選択」の画面で、［Oracle Database Extensions for .NET 10.2.0.1.0］を選択して［次へ］をクリックします。


　「ホームの詳細の指定」画面で、インストール先を指定します。ここで重要な点として、先ほどインストールした「Oracle Database Extensions for .NET 10.2.0.1.0」と同一のインストール先を指定してください。


　「インストールするコンポーネントの選択」の画面で、表示されてる2つのコンポーネントをチェックして、［次へ］をクリックします。


　次のような画面が表示された場合は、


　「サービス」一覧から 「OracleOraDb10g_home（xx）ClrAgent」のサービスを停止します。


　OUIに戻り［次へ］をクリックすると、次のような画面が表示されます。


　コマンドプロンプトを開き、表示されているディレクトリに移動してから、SQL*PLUSで次のように「DBMSClr.plb」を実行します。
DBMSClr.plbの実行例
D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN>sqlplus sys/[パスワード]
as sysdba

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
に接続されました。

SQL> @DBMSClr.plb

～ 途中省略 ～

PL/SQLプロシージャが正常に完了しました。

エラーはありません。

SQL>

　上記のスクリプトを実行したら［次へ］をクリックし、サマリー画面でインストールされるコンポーネントを確認後、［インストール］をクリックしてインストールを実行します。インストールが完了したら、.NET Framework 2.0に対応したODE.NETのインストールが完了です。
　次に、ODE.NETを利用するための設定を行います。ODE.NETはリスナーを経由して.NETストアドプロシージャを実行します。Oracle Netの設定に問題がある場合、「ORA-28575: 外部プロシージャ・エージェントへのRPC接続をオープンできません。」というエラーが発生する可能性があります。そのため、以下の2つのファイルについて確認を行います。

• listener.ora
• tnsnames.ora

　これらのファイルは、Oracleデータベースをインストールしたフォルダ（Oracleホーム）以下の「NETWORK\ADMIN」フォルダにあります。これらのファイル内に以下の記述があるかを確認しましょう。
.NETストアドプロシージャを有効にするための設定
SID_LIST_LISTENER =
(SID_LIST =
  (SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
   (PROGRAM = extproc)
  )
  (SID_DESC =
   (SID_NAME = CLRExtProc)
   (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
   (PROGRAM = extproc)
  )
)

.NETストアドプロシージャを有効にするための設定
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
  )
  (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
  )
.NETストアドプロシージャの開発とデプロイ

　ODE.NETを使用するための準備が終わったら、Visual
Studioで.NETストアドプロシージャを作成しましょう。今回はデータベースへのアクセスのない、簡単な.NETストアドプロシージャを作成するこ
とで、開発からデータベースへのデプロイまでの手順を説明します。ODTがインストールされた環境で、Visual
Studioを起動すると、新しいプロジェクトとして、［Oracle Project］を選択することができます。






　［Oracle Project］を選択して.NETストアドプロシージャの作成を開始します。次のような.NETストアドプロシージャを記述するためのコードエディタが表示されます。






　では、実際に.NETストアドプロシージャを記述してみましょう。.NETストアドプロシージャとしてデプロイするプロシージャやファンクション
は、VB.NETの場合は「Public Shared」、C#の場合は「public
static」で宣言されている必要があります。この点に注意して、「SCOTT」ユーザーの「EMP」表にアクセスする簡単なコードを記述してみます。


「SCOTT」ユーザーの「EMP」表にアクセスするサンプル

Public Shared Function GetEmpName(ByVal empno As Integer) As String
  'コネクションの確立
  Dim conn As New OracleConnection("context connection=true")
  conn.Open()

  ''SQLの発行と値の取得
  Dim cmd As OracleCommand = conn.CreateCommand
  cmd.CommandText = "SELECT ename FROM emp WHERE empno=:1"
  cmd.Parameters.Add(":1", OracleDbType.Int32, empno, _
    ParameterDirection.Input)
  Dim rdr As OracleDataReader = cmd.ExecuteReader

  Dim strRet As String
  If rdr.Read Then
    strRet = rdr.Item("ename")
  Else
    strRet = ""
  End If
  rdr.Close()
  conn.Close()

  Return strRet
End Function


「SCOTT」ユーザーの「EMP」表にアクセスするサンプル

public static string GetEmpName(int empno)
  {
    // コネクションの確立
    OracleConnection conn =
      new OracleConnection("context connection=true");
    conn.Open();

    // SQLの発行と値の取得
    OracleCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT ename FROM emp WHERE empno=:1";
    cmd.Parameters.Add(":1", OracleDbType.Int32,empno,
      ParameterDirection.Input);
    OracleDataReader rdr = cmd.ExecuteReader();

    String strRet = "";
    if(rdr.Read())
    {
      strRet = rdr.GetString(0);
    }
    else
    {
      strRet = "";
    }
    rdr.Close();
    conn.Close();

    return strRet;
  }
}



　上記コードのように、通常のデータアクセスのコードと大差なく.NETストアドプロシージャを記述できるのが理解できるかと思います。一つ相違点
として挙げられるのがコネクション確立時の記述方法が違う点です。通常ODP.NETからOracleデータベースへ接続する際の接続文字列は、


通常のODP.NETからOracleデータベースへ接続する際の接続文字列

"User Id=scott;Password=tiger;Data Source=orcl"


　上記のようになりますが、.NETストアドファンクションでは次のような記述になります。


通常のODP.NETからOracleデータベースへ接続する際の接続文字列

"context connection=true"


　.NETストアドプロシージャでは呼び出し元のコネクションを引き継ぐため、呼び出し元のユーザーIDやパスワード情報もそのまま引き継がれま
す。そのため、接続文字列中にDBユーザーの情報を記述する必要がありません。また、コネクション情報と共にトランザクションも引き継がれますので、.
NETストアドプロシージャ内で新規にトランザクションを開始する必要はありません。コードの記述が終わったら、次にOracleデータベース側に、作成
された.NETストアドプロシージャをデプロイします。Visual
Studioのメニューバーより［ビルド］-［ソリューションの配置］をクリックします。






　これにより.NETアセンブリのビルドが行われ、データベースへのデプロイを行うためのOracle Deployment Wizard for .NETが起動します。このウィザードを利用することで、次のような作業をウィザード形式で行うことが可能です。

• .NETアセンブリの作成
• .NETアセンブリのデプロイ
• .NETアセンブリを呼び出すためのPL/SQLラッパーの作成

　デプロイを行う際に.NETストアドプロシージャのセキュリティレベルの設定を行います。セキュリティレベルについては以下の表を参照してください。


表1　.NETストアドプロシージャ/ファンクションのセキュリティレベル

セキュリティレベル	説明
Safe（デフォルト）	データベース・リソースへのアクセスのみが許可される。ローカル・ファイルやネットワークなどの外部リソースへのアクセスは許可されない。
External	ローカル・ファイルの読取りまたは書込みと、ソケットやインターネット・ノードなどのネットワーク・リソースへのアクセスも許可される。
Unsafe	管理されていないコードの実行を含め、制限なしの実行が許可される。他のすべてのセキュリティ・レベルのスーパーセット。

.NETストアドプロシージャの実行

　デプロイが完了したらOracleエクスプローラより、.NETストアドプロシージャを実行してみましょう。今回はストアド・ファンクションにな
るので、.NETストアド・ファンクションを呼び出すための、PL/SQLファンクションが同じ名前で作成されています。右クリックして［実行］をクリッ
クしてみます。






　ファンクションを実行した後に、引数を入力するダイアログが表示されますので、引数を入力して［OK］ボタンをクリックすると結果が表示されま
す。以上のように［Oracleエクスプローラ］から呼び出すこともできますし、通常のSQL文から.NETストアドプロシージャを呼び出すこともできま
す。ストアドファンクションの場合は、次のようにSQLにPL/SQLラッパーを直接記述します。ストアドプロシージャの場合は、「EXECUTE
（EXEC）」文を発行します。


.NETストアドプロシージャの呼び出し例

C:\>SQLPLUS scott/tiger

SQL> SELECT getempname(7369) FROM dual;

GETEMPNAME(7369)
------------------------------------------

SMITH



　通常のPL/SQLで記述したストアドプロシージャと同様に.NETストアドプロシージャ／ファンクションを呼び出せることが確認できたと思います。


おわりに

　今回は、.NETストアドプロシージャを実現するODE.NETの概要と、ODE.NETのインストール、および設定方法、.NETストアドプロ
シージャの開発手順を簡単なサンプルを使って解説しました。次回は、ODE.NETで作成された.NETストアドプロシージャをデバッグする方法と、
PL/SQLではできない処理をODE.NETで実現するための記事を投稿していきます。ご期待ください！

)

　次に、ODE.NETはWindowsサービスとして稼動するので、「サービス」一覧から 「OracleOraDb10g_home（xx）ClrAgent」サービスが開始されていることを確認します。


　以上で、ODE.NETを使用する準備は完了です。