質問: ### 前提・実現したいこと お世話になっております。 Oracleのtriggerを使用して、同じスキーマ内のfunctionの呼び出しに難儀しております。 triggerから同じスキーマ内のOUT引数を持つfunctionの呼び出しで、実行時エラーが発生しております。 DB:oracle12c ### 発生している問題・エラーメッセージ ``` SQL Developer のエラーメッセージ EXCEPTION_ORA_6572 【エラー内容】 ORA-06572: ファンクションstringに出力引数が指定されています 原因: SQL文が、引数リストにOUTパラメータが含まれているパッケージ化されたPL/SQLファンクションまたはスタンドアロンPL/SQLファンクションのどちらかを参照しています。SQL文によって参照されるPL/SQLファンクションには、OUTパラメータが含まれていてはいけません。 処置: 引数リストでOUTパラメータを使用せずに、PL/SQLファンクションを再作成してください。 ``` ### 該当のソースコード ``` --トリガー作成 CREATE OR REPLACE TRIGGER TEST_TRIGGER AFTER INSERT OR DELETE OR UPDATE ON TEST_TABLE_A REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE vRET_SQLERR_CD NUMBER; vRET_SQLERR_MSG VARCHAR2(500); BEGIN IF INSERTING THEN INSERT INTO TEST_TABLE_B VALUES (FNC_GET_NEXTVAL('KANRI_ID',vRET_SQLERR_CD,vRET_SQLERR_MSG), :NEW.FIELD_A, :NEW.FIELD_B); END IF; END; / -- 更新元テーブル CREATE TABLE TEST_TABLE_A( KANRI_NO NUMBER, FIELD_A VARCHAR2(10), FIELD_B VARCHAR2(10) ); -- 更新先テーブル CREATE TABLE TEST_TABLE_B( KANRI_NO NUMBER, FIELD_A VARCHAR2(10), FIELD_B VARCHAR2(10) ); -- ファンクション作成 CREATE OR REPLACE FUNCTION FNC_GET_NEXTVAL ( ------------------------------------------------------------ -- 次管理番号取得用 -- IN : KANRI_ID 管理番号ID -- OUT : RET_SQLERR_CD SQLエラーコード -- RET_SQLERR_MSG SQLエラーメッセージ -- RETURN -- 次管理番号 -- ※エラー発生の場合、-1を返す ------------------------------------------------------------ KANRI_ID IN VARCHAR2, RET_SQLERR_CD OUT NUMBER, RET_SQLERR_MSG OUT VARCHAR2 ) RETURN NUMBER IS v_nextval NUMBER := -1; v_SQL VARCHAR2(200); v_KANRI_ID VARCHAR2(30); BEGIN -- 次管理番号取得処理を実行 RETURN v_nextval; EXCEPTION -- エラー発生 RET_SQLERR_CD := SQLCODE; RET_SQLERR_MSG := SQLERRM; RETURN -1; END; / ``` ### 試したこと OUT引数に nullや0 を設定しましたが、結果は変わりませんでした。 ### 補足情報(FW/ツールのバージョンなど) ここにより詳細な情報を記載してください。
回答: EXCEPTIONも含めて常にRETURNが -1 で良いのか?とか、 INSERT文では PKG_UTL.FNC_GET_NEXTVAL になっているのに、 CREATE OR REPLACE FUNCTION FNC_GET_NEXTVAL とPACKAGEではなくFUNCTIONになっているのはエラーになるとか、突っ込みどころ満載です。 ``` -- ファンクション作成 CREATE OR REPLACE FUNCTION FNC_GET_NEXTVAL ( -- 次管理番号取得用 I_KANRI_ID IN VARCHAR2, -- 管理番号ID -- 使ってないけど O_SQLERR_CD OUT NUMBER, -- SQLエラーコード O_SQLERR_MSG OUT VARCHAR2 -- SQLエラーメッセージ ) RETURN NUMBER -- 次管理番号 ※エラー発生の場合、-1を返す (常にRETURNは -1) IS v_nextval NUMBER := -1; BEGIN O_SQLERR_CD := 0; O_SQLERR_MSG := NULL; -- 次管理番号取得処理を実行 RETURN v_nextval; EXCEPTION -- エラー発生 O_SQLERR_CD := SQLCODE; -- ↓ エラーメッセージにはSQLERRM以外にもFUNCTION名やSELECTするであろうテーブルのKRIMARY KEY の内容も追加しないと運用で困るのでは? O_SQLERR_MSG := SQLERRM; RETURN -1; END; / ``` ``` --トリガー作成 CREATE OR REPLACE TRIGGER TEST_TRIGGER AFTER INSERT OR DELETE OR UPDATE ON TEST_TABLE_A REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE v_KANRI_NO TEST_TABLE_B.KANRI_NO%TYPE ; v_SQLERR_CD NUMBER; v_SQLERR_MSG VARCHAR2(500); BEGIN IF INSERTING THEN -- TRIGGER は PL/SQLだからOUT引数が使えるが、TRIGGERの中のINSERTはSQLだからOUT引数は使えないので、以下のような記述になる v_KANRI_NO := FNC_GET_NEXTVAL('KANRI_ID', v_SQLERR_CD, v_SQLERR_MSG) ; -- ここにエラーの時のエラー表示と以下のINSERTはしない処理が欲しい INSERT INTO TEST_TABLE_B VALUES (v_KANRI_NO, :NEW.FIELD_A, :NEW.FIELD_B); END IF; END; / ``` 連番を取得したいだけなら欠番を許容できるなら SEQUENCEを使います。 Oracle12.1以降なら CREATE TABLE文で連番を設定する こともできます。