OracleのFunction(PL/SQL)のsqlplusからの呼び出し方法の確認

Question

下記コマンドにてFunctionを呼び出して成功しています。
SQL> variable PO_ERR_MSG varchar2(10);
SQL> execute :ret := FUNC_TEST('20200701','0000','9999',:PO_ERR_MSG);
SQL> print ret PO_ERR_MSG;

下記の様にSELCT文でずっと試験していましたが、上手く行かずexecuteを使った次第です。
function側はPO_ERR_MSGはOUTで定義しております。
元々OUT定義した場合にはSELECTは使えないものでしょうか。
SQL> SELECT FUNC_TEST('20200701','0000','9999',:PO_ERR_MSG) FROM DUAL;
行1でエラーが発生しました。:
ORA-06572: ファンクションFUNC_TESTに出力引数が指定されています
ご存じの方居りましたら宜しくお願い致します。

エラー履歴下記。
SQL> SELECT FUNC_TEST('20200701','0000','9999',:PO_ERR_MSG) FROM DUAL;
SELECT FUNC_TEST('20200701','0000','9999',:PO_ERR_MSG) FROM DUAL
行1でエラーが発生しました。:
ORA-06572: ファンクションFUNC_TESTに出力引数が指定されています
'--------------------------------------------------------------
Creat Function下記

CREATE OR REPLACE FUNCTION ATOMBC.FUNC_TEST
(
--パラメタ
    PI_EXECYMD          IN  VARCHAR2,       --実行年月日（YYYYMMDD）:任意
    PI_TERMSCD_FROM     IN  VARCHAR2,       --FROM条件コード:任意
    PI_TERMSCD_TO       IN  VARCHAR2,       --TO条件コード:任意
    PO_ERR_MSG          OUT VARCHAR2        --エラーメッセージ：異常終了時のORACLEエラー内容
)
--戻り値：正常＝0/異常終了＝-1
RETURN NUMBER
IS
--***************************************************************************
--** 変数定義
--***************************************************************************
W_EXECYMD           VARCHAR2(8)     := TO_CHAR(SYSDATE,'YYYYMMDD'); --初期値：サーバー日付なYYYYMMDD
W_TERMSCD_FROM      VARCHAR2(5)     := '00000'; --初期値：FROM条件「00000」
W_TERMSCD_TO        VARCHAR2(5)     := '99999'; --初期値：TO条件「99999」
BEGIN
--************************************************************
--パラメタを変数セット
IF PI_EXECYMD IS NOT NULL THEN
    W_EXECYMD := PI_EXECYMD;
END IF;
IF PI_TERMSCD_FROM IS NOT NULL THEN
    W_TERMSCD_FROM := PI_TERMSCD_FROM;
END IF;
IF PI_TERMSCD_TO IS NOT NULL THEN
    W_TERMSCD_TO := PI_TERMSCD_TO;
END IF;

--処理
PO_ERR_MSG := 'Nomal';

--正常戻り値：0
RETURN 0;

EXCEPTION
    --予期せぬエラー
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE || ':' || SQLERRM(SQLCODE));
        PO_ERR_MSG := SQLCODE || ':' || SQLERRM(SQLCODE);
        --異常終了戻り値：-1
        RETURN -1;
END;
/

Comments

[Orlofsky]

エラーメッセージはキャプチャではなく、表示された文字をそのまま質問にコピペしてください。https://teratail.com/help/question-tips#questionTips3-4-2 キャプチャは削ってください。

[Orlofsky]

[PL/SQL]タグを追加してください。

[Orlofsky]

質問に現象を再現できる最小限のコードで CREATE FUNCTION も https://teratail.com/help/question-tips#questionTips3-7 の [コード] で追記してください。

[Orlofsky]

無駄ですから、キャプチャは削ってください。

Answer 1

SQLでは PL/SQLのOUT パラメータを使えません。
見易さを考えて字下げをするのは初歩中の初歩です。

SQL> CREATE OR REPLACE FUNCTION FUNC_TEST
  2      (
  3      -- パラメタ
  4      PI_EXECYMD          IN  VARCHAR2,       -- 実行年月日（YYYYMMDD）:任意
  5      PI_TERMSCD_FROM     IN  VARCHAR2,       -- FROM条件コード:任意
  6      PI_TERMSCD_TO       IN  VARCHAR2,       -- TO条件コード:任意
  7      PO_ERR_MSG          OUT VARCHAR2        -- エラーメッセージ：異常終了時のORACLEエラー内容
  8      )
  9      -- 戻り値：正常＝0/異常終了＝-1
 10      RETURN NUMBER
 11  IS
 12      -- 変数定義
 13      W_EXECYMD           VARCHAR2(8)     := TO_CHAR(SYSDATE,'YYYYMMDD'); -- 初期値：サーバー日付なYYYYMMDD
 14      W_TERMSCD_FROM      VARCHAR2(5)     := '00000'; --初期値：FROM条件「00000」
 15      W_TERMSCD_TO        VARCHAR2(5)     := '99999'; --初期値：TO条件「99999」
 16  BEGIN
 17      --パラメタを変数セット
 18      IF PI_EXECYMD IS NOT NULL THEN
 19          W_EXECYMD := PI_EXECYMD;
 20      END IF;
 21      IF PI_TERMSCD_FROM IS NOT NULL THEN
 22          W_TERMSCD_FROM := PI_TERMSCD_FROM;
 23      END IF;
 24      IF PI_TERMSCD_TO IS NOT NULL THEN
 25          W_TERMSCD_TO := PI_TERMSCD_TO;
 26      END IF;
 27
 28      PO_ERR_MSG := 'Nomal';  -- 処理
 29
 30      RETURN 0; -- 正常
 31
 32  EXCEPTION
 33      -- 予期せぬエラー
 34      WHEN OTHERS THEN
 35          DBMS_OUTPUT.PUT_LINE(SQLERRM);
 36          PO_ERR_MSG := SQLERRM(SQLCODE);
 37          RETURN -1;  -- 異常終了
 38  END FUNC_TEST;
 39  /

ファンクションが作成されました。

今回はPL/SQLの無名ブロックからFUNCTION を呼びました。

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      W_ERR_MSG  VARCHAR2(1024) ;
  3  BEGIN
  4      IF FUNC_TEST('20200701','0000','9999', W_ERR_MSG) = -1 THEN
  5          DBMS_OUTPUT.PUT_LINE('ERROR IN FUNC_TEST ' || W_ERR_MSG) ;
  6      ELSE
  7          DBMS_OUTPUT.PUT_LINE('FUNC_TEST OK') ;
  8      END IF ;
  9  END ;
 10  /
FUNC_TEST OK

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

SQL>

マニュアルを確認しておきましょう。

Comments

[ad.sys.soleil]

ご回答有難う御座います。

[Orlofsky]

ひょっとして NVL関数 を使えば済む案件ではないでしょうか？
https://docs.oracle.com/cd/E96517_01/sqlrf/NVL.html#GUID-3AB61E54-9201-4D6A-B48A-79F4C4A034B2

Answer 2

元々OUT定義した場合にはSELECTは使えないものでしょうか。

select 文では、OUTで受け取るものを定義できないから無理だと思います。
複数の結果を受け取りたいなら、テーブルファンクションでしょうね。

Comments

[ad.sys.soleil]

ご回答有難う御座います。