OracleCommand
オブジェクトは、Oracle Databaseで実行されるSQL文またはストアド・プロシージャを表します。
この項の内容は次のとおりです。
Oracle Databaseは、接続のコンテキストでのみトランザクションを開始します。トランザクションが開始すると、その接続で引き続き行われるコマンド実行はすべて、そのトランザクションのコンテキストで実行されます。トランザクションはOracleConnection
オブジェクトでのみ開始され、OracleConnection
オブジェクトのTransaction
読取り専用プロパティは、OracleConnection
オブジェクトによって暗黙的に設定されます。このため、アプリケーションはTransaction
プロパティを設定できません。また、設定する必要もありません。
注意: トランザクションは、.NETストアド・プロシージャではサポートされません。 |
OracleParameter
オブジェクトのDbType
プロパティが設定されている場合、OracleParameter
オブジェクトのOracleDbType
プロパティも変更されます。その逆も同じです。パラメータ・セットは最後に有効になります。アプリケーションはデータをバインドして、パラメータ値の.NETタイプから、ODP.NETでDbType
とOracleDbType
の両方のプロパティを推測します。ODP.NETを使用すると、アプリケーションでは、出力パラメータを.NETフレームワーク・タイプまたはODP.NETタイプのいずれかとして取得できます。アプリケーションでは、出力パラメータのDbType
プロパティ(.NETタイプ)、またはOracleParameter
オブジェクトのOracleDbType
プロパティ(ODP.NETタイプ)を設定することで、出力パラメータに対してどちらのタイプを戻すかを指定できます。たとえば、DbType
プロパティを設定することで出力パラメータがDbType.String
型として設定されている場合、出力データは.NET String型として戻されます。一方、OracleDbType
プロパティを設定することでパラメータがOracleDbType.Char
として設定されている場合、出力データはOracleString
型として戻されます。DbType
とOracleDbType
の両方のプロパティを設定してからコマンドを実行した場合は、最後の設定が有効になります。
ODP.NETは、次のOracleCommand
メソッドの実行により、OracleデータをInputOutput
、Output
およびReturnValue
パラメータに移入します。
ExecuteReader
ExecuteNonQuery
ExecuteScalar
アプリケーションは、出力パラメータの値をバインドできません。出力パラメータ値のバインドはODP.NETによって行われます。値オブジェクトが作成され、オブジェクトがOracleParameter
Value
プロパティに移入されます。
位置によってファンクションにバインドする場合(デフォルト)、ODP.NETでは、他のパラメータより前に戻り値がまずバインドされることが想定されています。
この項の内容は次のとおりです。
Oracle Database 10g以上のデータベースでは、BINARY_FLOAT
およびBINARY_DOUBLE
という2つの新しいネイティブ・データ型がサポートされています。
BINARY_FLOAT
およびBINARY_DOUBLE
データ型はそれぞれ、単精度浮動小数点値と倍精度小数点値を表します。
OracleParameter
バインドでは、アプリケーションは、BINARY_FLOAT
およびBINARY_DOUBLE
データ型には、OracleDbType.BinaryFloat
およびOracleDbType.BinaryDouble
の列挙を使用する必要があります。
OracleDbType
の列挙値は、OracleParameter
オブジェクトのOracleDbType
を明示的に指定する際に使用されます。
表3-3に、OracleDbType
の列挙値と、それぞれの列挙値の説明を示します。
表3-3 OracleDbType列挙値
メンバー名 | 説明 |
---|---|
|
Oracle |
|
Oracle |
|
Oracle |
|
Oracle |
|
|
|
Oracle |
|
Oracle |
|
Oracle |
|
Oracle |
|
8バイトの |
|
2バイトの |
|
4バイトの |
|
8バイトの |
|
Oracle |
|
Oracle |
|
Oracle |
|
Oracle |
|
Oracle |
|
Oracleオブジェクト型 |
|
Oracle |
|
Oracle |
|
Oracleネスト表型 |
|
Oracle |
|
Oracle |
|
Oracle |
|
4バイトの |
|
Oracle |
|
Oracle |
|
Oracle |
|
Oracle |
|
Oracle |
|
Oracle |
この項では、OracleParameter
クラス内のSystem.Data.DbType
、OracleDbType
およびValue
のプロパティからの判断を説明します。
OracleParameter
クラスでは、DbType
、OracleDbType
およびValue
プロパティはリンクされています。これらのプロパティのいずれかに値を指定すると、残りの1つ以上のプロパティの値を推測できます。
OracleParameter
クラスでは、OracleDbType
の値を指定すると、表3-4に示すように、DbType
の値が判断されます。
表3-4 OracleDbTypeからのSystem.Data.DbTypeの判断
OracleDbType | System.Data.DbType |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
OracleParameter
クラスでは、DbType
の値を指定すると、表3-5に示すように、OracleDbType
の値が判断されます。
表3-5 DbTypeからのOracleDbTypeの判断
System.Data.DbType | OracleDbType |
---|---|
|
|
|
サポート対象外 |
|
|
|
サポート対象外 |
|
|
|
|
|
|
|
|
|
サポート対象外 |
|
|
|
|
|
|
|
|
|
サポート対象外 |
|
|
|
|
|
|
|
|
|
サポート対象外 |
|
サポート対象外 |
|
サポート対象外 |
|
サポート対象外 |
OracleParameter
クラスでは、Value
は任意のNET Frameworkデータ型またはODP.NETタイプになるオブジェクト・タイプです。OracleParameter
クラスにOracleDbType
プロパティおよびDbType
プロパティが指定されていない場合、OracleDbType
プロパティはValue
プロパティのタイプから推測されます。
表3-6は、Value
のタイプが.NETフレームワーク・データ型の1つである場合の、Value
プロパティからのDbType
およびOracleDbType
プロパティの判断を示します。
表3-6 Value(.NETデータ型)からのDbTypeおよびOracleDbTypeの判断
Value(.NETデータ型) | System.Data.DbType | OracleDbType |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
注意: DbType またはOracleDbType を指定せずに、OracleParameter の値としてその他の.NETフレームワーク・データ型を使用すると、例外が発生します。これは、その他の.NETフレームワーク・データ型からのDbType プロパティおよびOracleDbType プロパティの判断がサポートされていないためです。 |
表3-7は、Value
のタイプがOracle.DataAccess.Types
型の1つである場合の、Value
プロパティから判断されたDbType
およびOracleDbType
プロパティを示します。
表3-7 Value(ODP.NETデータ型)からのDbTypeおよびOracleDbTypeの判断
Value(Oracle.DataAccess.Types) | System.Data.DbType | OracleDbType |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ODP.NETは、PL/SQL連想配列(従来のPL/SQL索引付き表)バインドをサポートしています。
アプリケーションは、OracleParameter
オブジェクトをPL/SQL連想配列としてPL/SQLストアド・プロシージャにバインドできます。次のOracleParameter
プロパティは、この機能に使用されます。
このプロパティは、PL/SQL連想配列にバインドするために、OracleCollectionType.PLSQLAssociativeArray
に設定する必要があります。
このプロパティは、固定長の要素タイプ(Int32
など)の場合は無視されます。
可変長の要素タイプ(Varchar2
など)の場合、ArrayBindSize
プロパティ内の各要素は、Value
プロパティ内の対応する要素のサイズを指定します。
Output
パラメータ、InputOutput
パラメータおよび戻り値の場合、このプロパティは、可変長の変数に対して指定する必要があります。
このプロパティは、OracleParameter.Value
プロパティ内の各要素の実行ステータスを指定します。
このプロパティは、PL/SQL連想配列にバインドされる要素の最大数を指定します。
このプロパティは、値の配列にNULL
またはDBNull.Value
を設定する必要があります。
PL/SQL連想配列の例
この例では、3つのOracleParameter
オブジェクトをPL/SQL連想配列としてバインドします。Param1
はIn
パラメータ、Param2
はInputOutput
パラメータ、Param3
はOutput
パラメータとしてバインドします。
PL/SQLパッケージ: MYPACK
/* Setup the tables and required PL/SQL: connect scott/tiger@oracle CREATE TABLE T1(COL1 number, COL2 varchar2(20)); CREATE or replace PACKAGE MYPACK AS TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER; PROCEDURE TestVarchar2( Param1 IN AssocArrayVarchar2_t, Param2 IN OUT AssocArrayVarchar2_t, Param3 OUT AssocArrayVarchar2_t); END MYPACK; / CREATE or REPLACE package body MYPACK as PROCEDURE TestVarchar2( Param1 IN AssocArrayVarchar2_t, Param2 IN OUT AssocArrayVarchar2_t, Param3 OUT AssocArrayVarchar2_t) IS i integer; BEGIN -- copy a few elements from Param2 to Param1\n Param3(1) := Param2(1); Param3(2) := NULL; Param3(3) := Param2(3); -- copy all elements from Param1 to Param2\n Param2(1) := Param1(1); Param2(2) := Param1(2); Param2(3) := Param1(3); -- insert some values to db\n FOR i IN 1..3 LOOP insert into T1 values(i,Param2(i)); END LOOP; END TestVarchar2; END MYPACK; / */ // C# using System; using System.Data; using Oracle.DataAccess.Client; class AssociativeArraySample { static void Main() { OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle"; con.Open(); Console.WriteLine("Connected to Oracle" + con.ServerVersion); OracleCommand cmd = new OracleCommand( "begin MyPack.TestVarchar2(:1, :2, :3); end;", con); OracleParameter Param1 = cmd.Parameters.Add("1", OracleDbType.Varchar2); OracleParameter Param2 = cmd.Parameters.Add("2", OracleDbType.Varchar2); OracleParameter Param3 = cmd.Parameters.Add("3", OracleDbType.Varchar2); Param1.Direction = ParameterDirection.Input; Param2.Direction = ParameterDirection.InputOutput; Param3.Direction = ParameterDirection.Output; // Specify that we are binding PL/SQL Associative Array Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray; Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray; Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray; // Setup the values for PL/SQL Associative Array Param1.Value = new string[3] { "First Element", "Second Element ", "Third Element " }; Param2.Value = new string[3] { "First Element", "Second Element ", "Third Element " }; Param3.Value = null; // Specify the maximum number of elements in the PL/SQL Associative Array Param1.Size = 3; Param2.Size = 3; Param3.Size = 3; // Setup the ArrayBindSize for Param1 Param1.ArrayBindSize = new int[3] { 13, 14, 13 }; // Setup the ArrayBindStatus for Param1 Param1.ArrayBindStatus = new OracleParameterStatus[3] { OracleParameterStatus.Success, OracleParameterStatus.Success, OracleParameterStatus.Success}; // Setup the ArrayBindSize for Param2 Param2.ArrayBindSize = new int[3] { 20, 20, 20 }; // Setup the ArrayBindSize for Param3 Param3.ArrayBindSize = new int[3] { 20, 20, 20 }; // execute the cmd cmd.ExecuteNonQuery(); //print out the parameter's values Console.WriteLine("parameter values after executing the PL/SQL block"); for (int i = 0; i < 3; i++) Console.WriteLine("Param2[{0}] = {1} ", i, (cmd.Parameters[1].Value as Array).GetValue(i)); for (int i = 0; i < 3; i++) Console.WriteLine("Param3[{0}] = {1} ", i, (cmd.Parameters[2].Value as Array).GetValue(i)); // Close and Dispose OracleConnection object con.Close(); con.Dispose(); Console.WriteLine("Disconnected"); } }
配列バインド機能を使用すると、アプリケーションでは、OracleParameter
クラスを使用してタイプの配列をバインドできます。配列バインド機能を使用すると、アプリケーションは、1回のデータベース・ラウンドトリップで複数の行を表に挿入できます。
次のコード例は、1回のデータベース・ラウンドトリップで3行をDept
表に挿入します。OracleCommand
ArrayBindCount
プロパティは、文の実行時に使用する配列の要素の数を定義します。
// C# using System; using System.Data; using Oracle.DataAccess.Client; class ArrayBindSample { static void Main() { OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;"; con.Open(); Console.WriteLine("Connected successfully"); int[] myArrayDeptNo = new int[3] { 10, 20, 30 }; OracleCommand cmd = new OracleCommand(); // Set the command text on an OracleCommand object cmd.CommandText = "insert into dept(deptno) values (:deptno)"; cmd.Connection = con; // Set the ArrayBindCount to indicate the number of values cmd.ArrayBindCount = 3; // Create a parameter for the array operations OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32); prm.Direction = ParameterDirection.Input; prm.Value = myArrayDeptNo; // Add the parameter to the parameter collection cmd.Parameters.Add(prm); // Execute the command cmd.ExecuteNonQuery(); Console.WriteLine("Insert Completed Successfully"); // Close and Dispose OracleConnection object con.Close(); con.Dispose(); } }
OracleParameter
クラスは、配列バインド機能を使用するときに、詳細な制御を行うために2つのプロパティを提供します。
ArrayBindSize
プロパティは、配列内の対応する各値の最大サイズを指定する整数の配列です。ArrayBindSize
プロパティは、OracleParameter
のSize
プロパティに類似していますが、ArrayBindSize
プロパティは配列内の各値のサイズを指定するという点が異なります。
アプリケーションは、実行前にArrayBindSize
プロパティを移入する必要があります。ODP.NETは、実行後にArrayBindSize
プロパティを移入します。
ArrayBindSize
プロパティは、Clob
、Blob
、Varchar2
など、可変長のパラメータ・タイプにのみ使用されます。Unicode文字列タイプのバイナリ・データ型および文字の場合、サイズはバイトで表されます。文字列タイプのカウントには、終了文字は含まれません。サイズが明示的に設定されていない場合、値の実際のサイズから推測されます。出力パラメータの場合、それぞれの値のサイズはODP.NETにより設定されます。固定長のデータ型の場合、ArrayBindSize
プロパティは無視されます。
ArrayBindStatus
プロパティは、パラメータの配列内の対応する各値のステータスを指定するOracleParameterStatus
値の配列です。このプロパティは、OracleParameter
のStatus
プロパティに類似していますが、ArrayBindStatus
プロパティは配列内の各値のステータスを指定するという点が異なります。
アプリケーションは、実行前にArrayBindStatus
プロパティを移入する必要があります。ODP.NETは、実行後に移入します。ArrayBindStatus
プロパティを使用するアプリケーションは、実行前に、パラメータの配列内の対応する要素にNULL
値を指定できます。ODP.NETは、実行後にArrayBindStatus
プロパティを移入して、配列内の対応する要素にNULL
値があるか、または値がフェッチされたときにデータの切捨てが発生したかどうかを示します。
配列バインドの実行中にエラーが発生した場合、Value
プロパティ内のどの要素がエラーの原因になったかを判断するのは困難です。ODP.NETは、エラーが発生した行を判断する方法を提供することで、エラーの原因になった行の要素を簡単に検索できるようにします。
配列バインドの実行中にOracleException
オブジェクトがスローされた場合、OracleErrorCollection
には1つ以上のOracleError
オブジェクトが含まれます。これらのOracleError
オブジェクトはそれぞれ、実行中に発生した個別のエラーを表し、プロバイダ固有のプロパティArrayBindIndex
を含みます。このプロパティは、エラーが発生した行番号を示します。
次に配列バインドのエラー処理の例を示します。
/* Database Setup connect scott/tiger@oracle drop table depttest; create table depttest(deptno number(2)); */ // C# using System; using System.Data; using Oracle.DataAccess.Client; class ArrayBindExceptionSample { static void Main() { OracleConnection con = new OracleConnection(); con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;"; con.Open(); OracleCommand cmd = new OracleCommand(); // Start a transaction OracleTransaction txn = con.BeginTransaction(IsolationLevel.ReadCommitted); try { int[] myArrayDeptNo = new int[3] { 10, 200000, 30 }; // int[] myArrayDeptNo = new int[3]{ 10,20,30}; // Set the command text on an OracleCommand object cmd.CommandText = "insert into depttest(deptno) values (:deptno)"; cmd.Connection = con; // Set the ArrayBindCount to indicate the number of values cmd.ArrayBindCount = 3; // Create a parameter for the array operations OracleParameter prm = new OracleParameter("deptno", OracleDbType.Int32); prm.Direction = ParameterDirection.Input; prm.Value = myArrayDeptNo; // Add the parameter to the parameter collection cmd.Parameters.Add(prm); // Execute the command cmd.ExecuteNonQuery(); } catch (OracleException e) { Console.WriteLine("OracleException {0} occured", e.Message); if (e.Number == 24381) for (int i = 0; i < e.Errors.Count; i++) Console.WriteLine("Array Bind Error {0} occured at Row Number {1}", e.Errors[i].Message, e.Errors[i].ArrayBindIndex); txn.Commit(); } cmd.Parameters.Clear(); cmd.CommandText = "select count(*) from depttest"; decimal rows = (decimal)cmd.ExecuteScalar(); Console.WriteLine("{0} row have been inserted", rows); con.Close(); con.Dispose(); } }
表3-8は、OracleParameterStatus
の列挙値のリストです。
文キャッシングにより、最初の実行で作成されたサーバー・カーソルのキャッシングによる実行の前に、各SQL文またはPL/SQL文を解析する必要がなくなります。同じ文を後で実行する場合、カーソルから解析された情報を再利用して、文を解析せずに実行することで、パフォーマンスが向上します。
文キャッシングによるパフォーマンスの向上を得るために、繰り返し実行される文のみをキャッシュすることをお薦めします。さらに、SQL文またはPL/SQL文では、リテラル値ではなくパラメータを使用してください。これにより、文キャッシングが有効になります。これは、後続の実行でパラメータ値が異なる場合でも、パラメータ化された文から解析された情報は再利用できるためです。ただし、文のリテラル値が異なる場合、解析された情報は、後続の文にも同じリテラル値がないかぎり、再使用できません。
次の接続文字列属性は、ODP.NET文キャッシング機能の動作を制御します。
Statement
Cache
Size
この属性は、ODP.NET文キャッシングを有効化または無効化します。デフォルトでは、この属性は10
に設定され、ODP.NET文キャッシングは有効です。0
に設定した場合、属性は無効になります。0
より大きい値に設定した場合、ODP.NET文キャッシングが有効になり、指定された値は、接続用にキャッシュできる文の最大数として使用されます。指定された最大キャッシュ・サイズまで接続がキャッシュされると、最低使用頻度のカーソルが解放され、新しく作成されたカーソルをキャッシュするためのスペースが確保されます。
Statement
Cache
Purge
この属性は、接続がクローズされた場合、または接続プールに戻された場合にキャッシュされるすべての文を消去する方法を提供します。デフォルトでは、この属性はfalse
に設定されています。つまり、接続がプールに戻されてもカーソルは解放されません。
アプリケーションを変更せずに、システムで実行しているすべてのODP.NETアプリケーションの文キャッシングをデフォルトで有効化するには、HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\
Assembly_Version
\StatementCacheSize
のレジストリ・キーを0
より大きい値に設定しますこの値は、サーバーでキャッシュされるカーソルの数を指定します。デフォルトでは、この値は10
に設定されています。
システムのデフォルト値は接続プール・レベルで無効にできます。Statement
Cache
Size
属性をレジストリ値と異なるサイズに設定するか、またはこの属性をオフにできます。
次のプロパティおよびメソッドは、文キャッシングが有効な場合のみ当てはまります。
OracleCommand.AddToStatementCache
プロパティ
文キャッシングが有効な場合、このプロパティをtrue
に設定すると、文の実行時にキャッシュに文が追加されます。文キャッシングが無効な場合、またはこのプロパティがfalse
に設定されている場合、実行された文はキャッシュされません。
OracleConnection.PurgeStatementCache
メソッド
このメソッドは、特定の接続に関連付けられたデータベースでオープンしているカーソルをすべてクローズすることで、キャッシュされた文をすべて消去します。このコールの後も、文キャッシングは有効です。