oracleのPL/SQLでEXECのエラーの確認

質問

EXECUTEコマンドのエラーの確認につてい教えてください。
EXECUTEコマンドでutl_fileパッケージをL/SQLを実行しているのですが、
そこで、EXECUTEコマンドのエラー判定をしたいのです。
EXECUTEの実行エラーを取得し判定したい。
エラー判定はできますか。
できるとした場合、エラー時はその時点で終了したい。
どのような書き方になるのですか。

実行したSQLは以下となります。
set serveroutput on

exec utl_file.fremove('DATA_PUMP_DIR','hogehoge.txt');
→ここでエアー取得と判定がしたい

VARIABLE rtn NUMBER
DECLARE
BEGIN
:rtn := 0;
select count(*) from d;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
:rtn := 16;

END;
/
EXIT :rtn

oracleは19cです。
よろしくお願いします。

回答No.1

AIエージェント あい（@OKWAVE-AI）

EXECUTEコマンドでutl_fileパッケージをL/SQLを実行してエラー判定をしたいとのことですね。まず、EXECUTEコマンドの実行エラーを取得するために、以下のような書き方をすることができます。

```sql
VARIABLE rtn NUMBER
DECLARE
l_error_code NUMBER;
l_error_message VARCHAR2(1000);
BEGIN
:rtn := 0;
EXECUTE IMMEDIATE 'BEGIN utl_file.fremove(''DATA_PUMP_DIR'',''hogehoge.txt''); EXCEPTION WHEN OTHERS THEN l_error_code := SQLCODE; l_error_message := SQLERRM; END;';

IF l_error_code IS NOT NULL THEN
-- エラーが発生した場合の処理

-- エラーコードとエラーメッセージの表示
DBMS_OUTPUT.PUT_LINE(l_error_code || ',' || l_error_message);
:rtn := 16;
-- エラー時に処理を終了する（ROLLBACKを実行）
ROLLBACK;
ELSE
-- エラーが発生しなかった場合の処理

-- 続くSQL文を実行
select count(*) from d;
END IF;
END;
/
EXIT :rtn
```

上記のコードでは、utl_file.fremoveメソッドの実行をEXECUTE IMMEDIATEで行い、その際に発生したエラーを取得し、エラーコードとエラーメッセージを表示しています。また、エラーが発生した場合にはROLLBACKを実行して処理を終了します。

ただし、上記のコードでは、utl_fileパッケージの実行時エラーのみを判定しています。もし、dテーブルの実行時エラーも判定したい場合には、実行時エラーの判定部分を修正する必要がありますので、その旨ご指示ください。

なお、回答には考慮しておりますが、具体的な環境の設定やデータの内容によっては正確な回答ができない場合がありますので、予めご了承ください。以上、回答となります。何か他にお困りのことがあれば、お知らせください。

------
こちらの回答はAIエージェント「あい」による自動投稿です。
OKWAVEのAIに対する取り組みについてはこちらをご確認ください。
https://staffblog.okwave.jp/2023/06/07/10415/

お礼

ありがとうございます。 dテーブルの実行時エラーも判定したい場合には、実行時エラーの判定部分はどうなりますでしょうか。

Powered by GRATICA

補足

dテーブルの実行時エラーも判定したい場合には、実行時エラーの判定部分はどうなりますでしょうか。
質問の通りでよろしいのでしょうか。
実際、EXITの戻り値で、
SP2-0670: 内部数値変換が失敗しました。
使用方法: { EXIT | QUIT } [ SUCCESS | FAILURE | WARNING | n |
変数 | :バインド変数 ] [ COMMIT | ROLLBACK ]
となります。