ストアドプロシージャの実行方法について

969 views
Skip to first unread message

masatakashibuya

unread,
Aug 27, 2018, 9:53:53 PM8/27/18
to OpenTouryoProject
西野様

お世話になっております。渋谷です。

Touryo.Infrastructure.Framework
Touryo.Infrastructure.Framework.RichClient
Touryo.Infrastructure.Public
のバージョンは2.2.0です。

にver 02-30から汎用Daoを使用したストアド実行が可能になります。
とありましたが

以下の修正を行えばDaoでも実行できるのかなと
CmnDaoとMyBaseDaoに修正をしました。

以下のようなOracleプロシージャを作成し
CREATE OR REPLACE PROCEDURE TEST(param IN NUMBER, ret OUT NUMBER) IS
BEGIN
ret := param;
END TEST;

以下のように実行しました。
            CmnDao dao = new CmnDao(this.GetDam(), CommandType.StoredProcedure);
            dao.SQLText = "TEST";

            dao.SetParameter("param", 7, SqlDbType.Int, 0, ParameterDirection.Input);
            dao.SetParameter("ret", null, SqlDbType.Int, 0, ParameterDirection.Output);

            IDataReader idr = dao.ExecSelect_DR();

            int result = (int)dao.GetParameter("ret");

するとExecSelect_DR時のSetParameterあたりで以下のようなエラーが発生します。
System.Web.Services.Protocols.SoapException: 'System.Web.Services.Protocols.SoapException: サーバーは要求を処理できませんでした。 ---> System.ArgumentOutOfRangeException: 指定された引数は、有効な値の範囲内にありません。
   場所 Oracle.ManagedDataAccess.Client.OracleParameter.set_OracleDbType(OracleDbType value)
   場所 Touryo.Infrastructure.Public.Db.DamManagedOdp.SetParameter(String parameterName, Object obj, Object dbTypeInfo, Int32 size, ParameterDirection paramDirection)
   場所 Touryo.Infrastructure.Framework.Dao.BaseDao.SetParameter(String parameterName, Object obj, Object dbTypeInfo, Int32 size, ParameterDirection paramDirection)
   場所 Touryo.Infrastructure.Business.Dao.CmnDao.SetParameters() 場所 c:\root\programs\C#\Frameworks\Infrastructure\Business\Dao\CmnDao.cs:行 482
   場所 Touryo.Infrastructure.Business.Dao.CmnDao.ExecSelect_DR() 場所 c:\root\programs\C#\Frameworks\Infrastructure\Business\Dao\CmnDao.cs:行 343

Inputパラメータのみだとエラーなく実行できるので
outパラメータ設定時だとおもいます。

CmnDaoとMyBaseDaoに修正のみでDaoからストアドプロシージャを実行可能なのでしょうか
よろしくお願いいたします。

daisukenishino

unread,
Aug 27, 2018, 11:10:58 PM8/27/18
to OpenTouryoProject
渋谷さん

すいません、少々情報に誤りがあって、
を見ると、汎用Daoも、ストアド実行の実績は、かなり昔からあったようです。

＃今回は、ConstructorでCommandType.StoredProcedureを明示できるようにしています。

しかし、DBMS（と、そのデータプロバイダ）に依存するトコロはある気がします。

Oracle（ODP.NET）の場合、どういう指定が必要になるか？が、チョット解っていませんが、

　String parameterName, Object obj, Object dbTypeInfo, Int32 size, ParameterDirection paramDirection

の部分の指定の組み合わせに問題があるのではないか？と思います。


を見ると、特に問題なく、できると思いますが、
もしかすると、サイズ指定に問題があるのかもしれません。

ただ、以下を見ると、
Sizeはバイト数または文字数とありますが、
固定長のデータ型: 無視とあるので問題ない気もします。

西野



2018年8月28日火曜日 10時53分53秒 UTC+9 masatakashibuya:

daisukenishino

unread,
Aug 27, 2018, 11:13:15 PM8/27/18
to OpenTouryoProject
渋谷さん

すいません、これ、スタックトレースを見ると、実行以前の
Oracle.ManagedDataAccess.Client.OracleParameter.set_OracleDbType(OracleDbType value)
でエラーになっています。

そこで呼び出し元を確認しましたが、

dao.SetParameter("param", 7, SqlDbType.Int, 0, ParameterDirection.Input);

と、SqlDbTypeとなっていますが、OracleDbTypeの誤りでは？と思ったりしました。
＃ここがクロスDBでObject型なので間違い易いですが。

西野

masatakashibuya

unread,
Aug 28, 2018, 12:36:56 AM8/28/18
to OpenTouryoProject
西野様

Oracle.ManagedDataAccessのOracleDbTypeを指定することで実行することができました！

引数にOracleDbTypeを指定し
Varchar2の場合は、sizeを指定する必要があるようです。
GetParameterでは、OracleDecimalまたはOracleStringにキャストして
ToInt32やToStringなどで取得できました。

            dao.SetParameter("param", 7, OracleDbType.Int32, 0, ParameterDirection.Input);
            dao.SetParameter("ret", null, OracleDbType.Int32, 0, ParameterDirection.Output);
            dao.SetParameter("msg", null, OracleDbType.Varchar2, 100, ParameterDirection.Output);

            IDataReader idr = dao.ExecSelect_DR();

            OracleDecimal ret = (OracleDecimal)dao.GetParameter("ret");
            OracleString msg = (OracleString)dao.GetParameter("msg");

            int i = ret.ToInt32();
            string m = msg.ToString();


2018年8月28日火曜日 12時13分15秒 UTC+9 daisukenishino:

daisukenishino

unread,
Aug 28, 2018, 1:59:59 AM8/28/18
to OpenTouryoProject
渋谷さん

動いてよかったです。

>Varchar2の場合は、sizeを指定する必要があるようです。

上記って、nullじゃダメなんですかね。
# ただ、現時点のI/Fだと指定できませんが。

結果次第でsize指定無しのオーバーロード
or nullable対応が必要かどうか考えたいと思います。

西野


2018年8月28日火曜日 13時36分56秒 UTC+9 masatakashibuya:

masatakashibuya

unread,
Aug 30, 2018, 10:33:49 PM8/30/18
to OpenTouryoProject
西野様

CmnDaoに

        public void SetParameter(string parameterName, object obj,
            object dbTypeInfo, ParameterDirection paramDirection)
        {
            // ユーザ パラメタをディクショナリに設定
            this.DicParameter[parameterName] = obj;
            this.DicParameterType[parameterName] = dbTypeInfo;
            this.DicParameterDirection[parameterName] = paramDirection;
        }

size引数無しのSetParameterを追加してみました。
sizeにはデフォルトで-1が入るでいいでしょうか。

そうした場合、
Varchar2のパラメータで以下のエラーが発生します。

System.Web.Services.Protocols.SoapException: 'System.Web.Services.Protocols.SoapException: サーバーは要求を処理できませんでした。 ---> Oracle.ManagedDataAccess.Client.OracleException: ORA-06502: PL/SQL: 数値または値のエラー: 文字列バッファが小さすぎます。が発生しました
ORA-06512: "AIKK.KEBT001001", 行24
ORA-06502: PL/SQL: 数値または値のエラー: 文字列バッファが小さすぎます。が発生しました
ORA-06512: 行1
   場所 OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
   場所 OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
   場所 Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   場所 Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()
   場所 Touryo.Infrastructure.Public.Db.DamManagedOdp.ExecSelect_DR()
   場所 Touryo.Infrastructure.Framework.Dao.BaseDao.ExecSelect_DR()
   場所 Touryo.Infrastructure.Business.Dao.CmnDao.ExecSelect_DR() 場所 c:\root\programs\C#\Frameworks\Infrastructure\Business\Dao\CmnDao.cs:行 364

よろしくお願いします。

2018年8月28日火曜日 14時59分59秒 UTC+9 daisukenishino:

daisukenishino

unread,
Sep 1, 2018, 12:18:35 AM9/1/18
to OpenTouryoProject
渋谷さん

この対応は、ここのオーバーロードを増やす必要がありそうです。
sizeが無くて、paramDirectionがあるバージョンです。

  public abstract void SetParameter(string parameterName, object obj, object dbTypeInfo, ParameterDirection paramDirection);

issueに追加しておきます。

西野

2018年8月31日金曜日 11時33分49秒 UTC+9 masatakashibuya:

daisukenishino

unread,
Sep 4, 2018, 7:59:14 AM9/4/18
to OpenTouryoProject

渋谷さん

調査の結果、-1を指定すると、
null指定と同じ結果になりそうです（以下実装を参照）。


なので、見解としては、defaultのバッファ・サイズが小さすぎるのかもしれません。
と言う事で、サイズ指定を明示した方がイイと言う事になると思います。

西野

2018年9月1日土曜日 13時18分35秒 UTC+9 daisukenishino:

masatakashibuya

unread,
Sep 25, 2018, 12:16:41 AM9/25/18
to OpenTouryoProject
西野様

ありがとうございます。
サイズを明示することとしました。

ここまでの話なのですが
Damは、DamManagedOdpを使用していました。

OracleClientを使用する方（DamOraOdp）も試してみたところ
Dam内のParameterでOutパラメータがnullとなっており取得できていないようです。

なにかわかりますでしょうか。
よろしくお願いいたします。

2018年9月4日火曜日 20時59分14秒 UTC+9 daisukenishino:

masatakashibuya

unread,
Sep 25, 2018, 2:35:21 AM9/25/18
to OpenTouryoProject
nullとなっているのは
dao.ExecSelect_DRしたあと
dao.GetParameterで取得しようとした時です。


2018年9月25日火曜日 13時16分41秒 UTC+9 masatakashibuya:

daisukenishino

unread,
Sep 27, 2018, 7:17:54 AM9/27/18
to OpenTouryoProject
渋谷さん

ODP.NETでOutパラメータが取得できないと言う事ですよね。
もし可能なら、ストアドの定義と、dao呼出し作法を書き込んでください。

コチラでも確認してみます。

西野

2018年9月25日火曜日 15時35分21秒 UTC+9 masatakashibuya:

masatakashibuya

unread,
Sep 27, 2018, 9:25:36 PM9/27/18
to OpenTouryoProject
西野さん

はいODP.NETで取得できないです。

★ストアドプロシージャは以下です

CREATE OR REPLACE PROCEDURE PROC_EXAMPLE(ymd IN NUMBER, ret OUT NUMBER, msgid OUT VARCHAR2, msg OUT VARCHAR2) IS

BEGIN

　　-- 処理

ret := 1;
msgid := '';
msg := '正常終了';
--------------------------------------------------
-- 例外処理
--------------------------------------------------
EXCEPTION
WHEN OTHERS THEN
ret := 0;
msgid := SQLCODE;
msg := '[' || msg || ']' || SQLERRM;
END PROC_EXAMPLE;

★dao呼び出し作法は以下です。

            var dao = new CmnDao(this.GetDam(), CommandType.StoredProcedure);
            dao.CommandTimeout = 0;
            dao.SQLText = "PROC_EXAMPLE" ;

            dao.SetParameter("ymd", this.ymd, OracleDbType.Int32, 0, ParameterDirection.Input);
            dao.SetParameter("ret", null, OracleDbType.Int32, 0, ParameterDirection.Output);
            dao.SetParameter("msgid", null, OracleDbType.Varchar2, 4000, ParameterDirection.Output);
            dao.SetParameter("msg", null, OracleDbType.Varchar2, 4000, ParameterDirection.Output);

            IDataReader idr = dao.ExecSelect_DR();

            var ret = (OracleDecimal)dao.GetParameter("ret");
            var msgid = (OracleString)dao.GetParameter("msgid");
            var msg = (OracleString)dao.GetParameter("msg");

            int result = ret.ToInt32();
            string message = msg.ToString();

よろしくお願いします。

2018年9月27日木曜日 20時17分54秒 UTC+9 daisukenishino:

nishi.74322014

unread,
Oct 1, 2018, 7:19:49 PM10/1/18
to OpenTouryoProject
渋谷さん

期末/期初で対応できていませんでしたが本日着手します。

西野

masatakashibuya

unread,
Oct 1, 2018, 8:28:35 PM10/1/18
to OpenTouryoProject
西野さん

お忙しいところ恐れ入ります。

よろしくお願いいたします。


2018年10月2日火曜日 8時19分49秒 UTC+9 nishi.74322014:
渋谷さん

期末/期初で対応できていませんでしたが本日着手します。

西野

daisukenishino

unread,
Oct 1, 2018, 8:43:25 PM10/1/18
to OpenTouryoProject

渋谷さん


確認したところ、どうも、Oracleは

空文字列をNullにする仕様があるので、

それに起因している気がします。


ストアドの処理部分を


-- 処理

ret := 1;

msgid := 'aaa';

msg := '正常終了';


と修正したら以下のように文字が戻ってきました。


無題.png


なお使用しているODP.NETは、DB同梱の

\oraclexe\app\oracle\product\11.2.0\server\odp.net\bin\4\Oracle.DataAccess.dll"

を使用しています。


西野

daisukenishino

unread,
Oct 1, 2018, 8:45:51 PM10/1/18
to OpenTouryoProject
画像小さすぎますね。URLを張っておきます。

masatakashibuya

unread,
Oct 1, 2018, 10:46:35 PM10/1/18
to OpenTouryoProject
西野さん

こちらの状況では、Number型の値もnullとなっていました。

西野さんの方では取得できていたということで気付いたのですが
OracleDbTypeとOracleDecimalやOracleStringをOracle.ManagedDataAccess名前空間のものを使用していたためでした。
Oracle.DataAccess名前空間のものを使用することで取得することができました。

またご指摘の空文字の場合nullになるという事象はこちらでもありました。

今回、ManagedDataAccessとDataAccessを切替えれる仕様のため
CmnDaoを継承したクラスで、thisがDamOraOdpなのかDamManagedOdpかによって
引数の型を変更するよう実装すればいいでしょうか。


2018年10月2日火曜日 9時45分51秒 UTC+9 daisukenishino:

daisukenishino

unread,
Oct 2, 2018, 1:58:04 AM10/2/18
to OpenTouryoProject
渋谷さん

> 引数の型を変更するよう実装すればいいでしょうか。

基本的にusing切り替えや、名前空間のエイリアスで対応できると思います。

西野

2018年10月2日火曜日 11時46分35秒 UTC+9 masatakashibuya:

masatakashibuya

unread,
Oct 3, 2018, 9:55:06 PM10/3/18
to OpenTouryoProject
西野さん

参考にさせて頂きました。
ありがとうございました。

2018年10月2日火曜日 14時58分04秒 UTC+9 daisukenishino:
Reply all
Reply to author
Forward
0 new messages