none
ユーザー関数の移行について(オラクルからSQLサーバ) RRS feed

  • 質問

  • オラクルで作成されていたユーザ関数をMS提供SQLサーバーの移行ツールで移行したところ、1つのユーザ関数がスカラ関数とプロセジャの2種類で作成されました。スカラ関数中でプロセジャをExecしております。データ型をSQL用に合わせ、Select文で実行したところ、復帰値を参照できず、調べると関数での制約事項もあることがわかってきました。スカラ関数のEXEC文内にキーワードで”xp_oracle2ms_exec2_ex”というキーワードがあり、変換ツールで生成されたことから、それを利用すればスカラ関数からプロセジャを呼べるのではと推測したのですが、新たに関数かプロセジャ一方に移植しなおさなければ使用できないのでしょうか?利用している関数はSQL文内で使用されているので、困っております。

    2019年6月10日 5:58

すべての返信

  • ご質問の内容を私が取り違えているかもしれませんが、以下のようなことでしょうか?

    Execute Stored Procedure from a Function
    https://stackoverflow.com/questions/6344880/execute-stored-procedure-from-a-function


    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    2019年6月10日 6:56
  • さっそくの返信ありがとうございました。翻訳機能を使ってみたのですが、内容的として変換後プロセジャ部分を拡張プロセジャとして作成し、返還後スカラ関数から呼ぶよう変更することで解決すると受け取ってよいのでしょうか?

    具体例があったほうがよろしいでしょうか?

    SQLのSELECT文内で関数が使用されているので変更の影響を抑えたいです。

    2019年6月10日 8:19
  • 復帰値が参照できないのが問題なのでしょうか?
    簡単で再現する具体例があれば、有益な回答が早く得られる可能性が高くなると思います。

    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    2019年6月10日 8:47
  • おはようございます。具体例をあげて質問させていただきます。お手数おかけしますがよろしくお願いします。

    下記に関数を呼んでいるSQL文と変換ツールにて作成されたスカラ関数およびプロセジャをあげました。

    元々オラクルで利用しておりましたが、SQLサーバへ移行となり今回の話になっております。

    やりたいことはSQL文内で関数を呼び、移行前のように値を取得したいと考えております。

    現状ではヌルがかえってきております。尚、都合上一部データ形式や名称を修正しております。

    試しに下記スカラ関数のmaster.dbo.xp_ora2ms_exec2_ex部分を

    作成されたプロセジャに変更し実行すると”関数または拡張プロセジャ~”とエラーメッセージが表示

    され使用できませんでした。

    /* 使用しているプログラム内SQL文 */

    SELECT  名称,f係数GET(201806,"G","01",10000)
    FORM aMaster
    WHERE Key = "12345"

    /* 移行ツールで作成されたスカラ関数 */
    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER        FUNCTION  [dbo].[f係数GET] 
    (
       @P年月         numeric(6),
       @P区分         varchar(1),
       @Pコード varchar(2),
       @PNO    numeric(5)
    )
       RETURNS       numeric(3,2)
    AS
       BEGIN
          DECLARE
             @active_spid   INT, 
             @login_time    DATETIME, 
             @db_name       NVARCHAR(128)

          SET @active_spid = 
             ssma_oracle.GET_ACTIVE_SPID()

          SET @login_time = 
             ssma_oracle.GET_ACTIVE_LOGIN_TIME()

          SET @db_name = DB_NAME()

          DECLARE
             @return_value  numeric(3,2)

          EXECUTE master.dbo.xp_ora2ms_exec2_ex 
             @active_spid, 
             @login_time, 
             @db_name, 
             N'dbo', 
             N'F係数GET$IMPL', 
             N'true', 
             @P年月, 
             @P区分, 
             @Pコード, 
             @PNO, 
             @return_value    OUTPUT

          RETURN @return_value
       END

    /* 移行ツールで作成されたストアドプロシジャ */
    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[f係数GET$IMPL]  

       @P年月 numeric(6),
       @P区分 varchar(1),
       @Pコード varchar(2),
       @PNO numeric(5)
       @return_value_argument numeric(3,2)  OUTPUT
    AS 
       BEGIN

          DECLARE
             @SEIDATE numeric(6, 0), 
             @DATEST numeric(8, 0), 
             @DATEED numeric(8, 0), 
             @率 numeric(14, 2)

          BEGIN TRY

             SET @SEIDATE = @P年月

             /*** 係数率の取得 ***/
             SELECT TOP (1) @率 = fxx.率
             FROM tbl_係数  fxx
             WHERE kbn = @P区分

             SET @return_value_argument = @率

             RETURN 

          END TRY

          BEGIN CATCH
             BEGIN

                SET @return_value_argument = 0

                RETURN 

             END
          END CATCH

       END

    2019年6月11日 0:47
  • masterデータベースに作成されているのが気になりますが、とりあえず置いておいて・・・

    さて、とりあえず、master.dbo.xp_ora2ms_exec2_exが正しい値を返すかをまず確認されてみてはいかがでしょうか?
    以下をSQL Server Management Studioのクエリウィンドウなどに打ち込んで試してみて下さい。当然ですが、パラメータ部分には適当な値を指定して下さい

    DECLARE
       @return_value  numeric(3,2)
      
    EXECUTE master.dbo.xp_ora2ms_exec2_ex
       @active_spid,
       @login_time,
       @db_name,
       N'dbo',
       N'F係数GET$IMPL',
       N'true',
       @P年月,
       @P区分,
       @Pコード,
       @PNO,
       @return_value    OUTPUT
      
    select @return_value

    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    2019年6月11日 1:41
  • 返信ありがとうございます。さっそくManagement Studioに下記のように打つこんで実行してみました。

    実行したところODBC~[sqlserver]ストアドプロシジャー”F係数GET$”が見つかりませんと表示されました。

    Management Studio画面の”プロシジャ”フォルダにはF係数GET$IMPLという名前があることは確認済みです。

    またストアドプロシジャを再クリエイト実施後実行してもやはり同じエラーがかえってきます。

    プロシジャが参照できていないようです 

          DECLARE
             @active_spid   INT, 
             @login_time    DATETIME, 
             @db_name       NVARCHAR(128)

          SET @active_spid = 
             ssma_oracle.GET_ACTIVE_SPID()

          SET @login_time = 
             ssma_oracle.GET_ACTIVE_LOGIN_TIME()

          SET @db_name = DB_NAME()

          DECLARE
             @return_value  numeric(3,2)

          EXECUTE master.dbo.xp_ora2ms_exec2_ex 
             @active_spid, 
             @login_time, 
             @db_name, 
             N'dbo', 
             N'F係数GET$IMPL', 
             N'true', 
             @P年月, 
             @P区分, 
             @Pコード, 
             @PNO, 
             @return_value    OUTPUT

    select @return_value
          

    2019年6月11日 4:46
  • ユーザーのデフォルトのスキーマ名や権限が関係しているような気がします。
    F係数GET$IMPLの前にスキーマ名を付けると動きますでしょうか?
    例えば、ssma_oracle.GET_ACTIVE_LOGIN_TIME()であれば、ssma_oracle.を付ける感じです。

    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    2019年6月11日 5:07
  • たびたびすいません。説明不足でした、先ほどのエラーメッセージに

    master.dbo.F係数GET$”が見つかりませんと表示されておりました。

    試しにEXECUTEの中のN'F係数GET$IMPL'の箇所をN'master.dbo.F係数GET$IMPL'と変更し、

    再度実行してみたのですが、結果は前回と同じでした。

    権限ですがプロシジャのプロパティを開き見たところ、「全般」画面の説明欄にある実行時の権限は”呼出元”となっており、

    「権限」画面にはユーザー名等なにも表示されておりません。

    2019年6月11日 5:27
  • master.dbo.F係数GET$IMPLを探しに行って見つからないと言っているわけですから、本当にその名前になっているのか、SQL Server Management Studioで当該の関数を右クリックして変更で表示してみて下さい。
    その結果、
    ALTER FUNCTION 
    dbo.F係数GET$IMPL
    となっていますでしょうか? また、
    dbo.F係数GET$IMPLはmasterデータベースに存在しているのでしょうか?

    とりあえずその関数が探せてないので権限ではないと思いますが、権限を与えるのであれば以下を実行して下さい。

    Grant Execute on 関数名 to ユーザー名



    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    2019年6月11日 5:59
  • たびたびありがとうございます。先ほどの操作を行ってみました。下記がその結果です。

    画面でみてもデータベースmasterのストアドプロシジャフォルダ内に名前は表示されています。

    見つかっても良さそうなのですが。。。

    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[F係数GET$IMPL]  
       @P年月 numeric(6),
       @P区分 varchar(1),
       @Pコード varchar(2),
       @PNO numeric(5),
       @PReturn numeric(3,2)    OUTPUT
    AS 



    権限の件ですが対象プロシジャのプロパティを開き、頁の選択欄の権限選んだ後
    検索ボタンを押して[guest]と[public]を追加して実行を行ったのですが、
    エラーメッセージは同じままでした。

    権限付与は教えていただいたGrant~を使ったほうがよいでしょうか?

    2019年6月11日 7:46
  • SQL Server Management Studioで見えていて、実行すると見つからないというのは謎ですね・・・
    確認ですが、SQL Server Management Studioのクエリ画面で実行してもダメということでしょうか?

    また、F係数GET$IMPLの中身をコピーして新しい関数を作成した場合、それを実行することはできるのでしょうか?
    または簡単な関数を作成してテストしてみても良いです。どこに問題があるのか切り分けていかなければなりません。

    権限付与は教えていただいたGrant~を使ったほうがよいでしょうか?

    権限が無い時は無いというメッセージが出ますので、権限以前の問題だと思います。


    ★良い回答には質問者は回答済みマークを、閲覧者は投票を!

    2019年6月11日 8:45
  • SSMA For Oracle を使ってみたことがあるのですが、テーブル名や項目名に日本語を使ってると移行途中で異常終了したことがあります。

    プロシージャの名前が日本語ってのが気になるんですが、適当なアルファベットに変えたら呼び出せたりしないでしょうか?
    2019年6月11日 15:05
  • おはようございます。先日の続きですが、F係数GET$IMPLをManagement Studioのクエリ画面で単体動作すると
    結果はうまくかえってきます。新し関数名に中身をコピーしても同じ結果で、確認用の関数を作成し、
    新しい関数名で読んでみる等試みましたが、ストアドプロシジャ名が見つからないと結果は同じでした。

    ほかの方の書き込みも参考に関数名を英語表記に変えても結果は変わらずです。

    master.dbo.xp_ora2ms_exec2_ex が何を行うものか不明ですが、どうもストアドに移行されてしまった内容を
    スカラ関数側に持ってこないと下記のようなSQL文内で書かれたところに値を取得することはできないようです。

    SELECT  名称,f係数GET(201806,"G","01",10000)
    FORM aMaster
    WHERE Key = "12345"
    2019年6月12日 2:36
  • 返信ありがとうございます。

    日本語表記を変えて行ってみましたが、結果は変わらずでした。

    移行ツールといってもあまりあてにはできない感じです

    2019年6月12日 2:38
  • xp_ora2ms_exec2_ex については

    SSMA Blog - SQL Server Migration
    「Deadlock occurs during execution of converted code.」
    http://ssmablog.blogspot.com/2009/08/deadlock-occurs-during-execution-of.html

    に、ちょっと説明があります。

    Oracle では Function の中で更新系の DML を実行できますが、SQL Server のユーザ定義関数(UDF) は実行できないので Procedure に分離して呼び出しているだけのようです。

    その際に、別のトランザクションを起動することがオプションで指定でき、Oracle の自律型トランザクション(AUTONOMOUS_TRANSACTION) のようなこともできるのかなと思います。

    また、SQL Server では Function や Procedure の中に、入れ子の Function や Procedure を作ることができないので、その場合にも分離されるのかなと思います。

    ※ 思いますーというのは、前述のとおりで使うのをあきらめてしまったからなので・・・

    この辺は、実際に SSMA を使って移行した方に話を伺いたいところですね。
    2019年6月12日 4:05