Oracleストアドプロシージャのエラー確認＆一括リコンパイル

Oracleのストアドプロシージャは、データベース内で重要な処理やビジネスロジックを実行するためのプログラムです。しかし、ストアドプロシージャの開発や変更の過程でエラーが発生することがあります。この記事では、Oracleストアドプロシージャに関連するエラーの確認方法と一括リコンパイルの手法について解説します。

『Oracleストアドプロシージャのエラー確認方法は？』
『Oracleストアドプロシージャを一括でリコンパイルする方法は？』

こんな疑問に答えます。

SE歴25年のオッサンが解説するぞい！

ＳＥおっさん

Oracleストアドプロシージャのエラー確認する方法

ストアドプロシージャのエラー確認には、Oracleのエラーメッセージで確認できます。
エラーメッセージには詳細な情報が含まれており、問題の特定と解決に役立つのです。
ストアドプロシージャのエラーメッセージ確認方法は２通りです。

ストアドエラーの確認方法

データ・ディクショナリ・ビュー*_ERRORSをSELECT文で問合せする
[*補足]
DBA_ERRORS…データベース全体の情報
ALL_ERRORS…現行ユーザーがアクセスできる情報
USER_ERRORS…現行ユーザーのスキーマの情報
SQL*Plus環境でコマンドのSHOW ERRORSを実行
[*補足]
SQL*Plusと同等の環境でもOKです
（SQLDEVELOPPERやOBJECTBROWSERなど）

エラーメッセージを分析することで、ストアドプロシージャの実行時に発生するエラーの原因や頻度を把握することができるでしょう。

エラーメッセージから原因となる部分を特定し、該当するストアドプロシージャのコードを修正できるようになれば一人前です。

頑張ってくださいね！

Oracleストアドプロシージャを一括でリコンパイルする方法

早速ですが、一括コンパイルする無名プロシージャのサンプルを提示します。
色々とやり方はありますので、あくまで参考までにしてください。

４回位したら解消されるという推測をもとにサンプルを作成してます。
依存関係が深い場合はリコンパイルが必要な場合もあるのでご了承ください。
（ストアドプロシージャ以外のオブジェクトにも対応していますが、MATERIALIZED VIEWは対象外です）

set SERVEROUTPUT ON

// 依存関係を気にせずに４回コンパイルする
// コンパルエラーがあればエラーを表示して次のコンパイルを行う
DECLARE
  v_code  NUMBER;
  v_errm  VARCHAR2(64);
BEGIN

  FOR i IN 1..4 LOOP
    DBMS_OUTPUT.PUT_LINE(i || '回目');
    FOR cur IN (
          SELECT
               owner
              ,object_name
              ,decode(object_type, 'PACKAGE BODY', 'PACKAGE', object_type) as object_type2
          FROM dba_objects
          WHERE
              STATUS = 'INVALID'
          AND OBJECT_TYPE IN (
                      'FUNCTION'
                    , 'PACKAGE'
                    , 'PACKAGE BODY'
                    , 'PROCEDURE'
                    , 'TRIGGER'
                    , 'VIEW'
                    ) 
          ) LOOP

      DBMS_OUTPUT.PUT_LINE(cur.object_name);
      DBMS_OUTPUT.PUT_LINE('alter ' || cur.object_type2 || ' ' || cur.owner || '.'  || cur.object_name || ' compile');

      BEGIN
        EXECUTE IMMEDIATE 'alter ' || cur.object_type2 || ' ' || cur.owner || '.'  || cur.object_name || ' compile';
      EXCEPTION
        WHEN OTHERS THEN
          v_code := SQLCODE;
          v_errm := SUBSTR(SQLERRM, 1, 64);
          DBMS_OUTPUT.PUT_LINE ('Error code ' || v_code || ': ' || v_errm);            
      END;
    END LOOP;
  END LOOP;


EXCEPTION
  WHEN OTHERS THEN
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.PUT_LINE ('Error code ' || v_code || ': ' || v_errm);            
END;

ストアドプロシージャの一括リコンパイルについて考えてみましょう。
複数のストアドプロシージャが存在する場合、それぞれのプロシージャを個別にコンパイルするのは手間がかかりますよね。
そこで、一括リコンパイルを行うことで効率的に修正や更新を行うわけです。
ストアドプロシージャを本番環境にリリース時、依存オブジェクトがINVALID(無効）になるケースのリコンパイルでも一括リコンパイルは有効となります。

一括リコンパイルの際には、依存関係や順序に注意が必要です。
ストアドプロシージャは他のオブジェクトと関連していることがあり、それらのオブジェクトが正しくコンパイルされている必要があります。適切な順序で一括リコンパイルを行うことで、依存関係の問題を解消し、システム全体の安定性を確保することができます。

サンプルでは依存関係を気にせずに回数でカバーする端折ったサンプルです。
きちんと依存関係を調べようと思ったら、
データディクショナリの「DBA_DEPENDENCIES/ALL_DEPENDENCIES/USER_DEPENDENCIES」をなど参照する必要があるでしょう。

Oracleが用意している一括リコンパイルするパッケージもありますが、カスタマイズできないので今回はカスタマイズできるようなサンプルを作成してみました。
「このオブジェクトは絶対エラーでるけど、誰も触っちゃいけない」ってこともあるかもしれないので。

まとめ

この記事では、Oracleストアドプロシージャのエラー確認と一括リコンパイルの手法について詳しく解説してきました。
エラーの特定と修正、効率的な一括リコンパイルの方法をマスターすることで、Oracleデータベースのパフォーマンスと信頼性を向上させることができます。
是非、これらのテクニックを活用して、より効率的なストアドプロシージャの開発と管理を行ってください。

ＳＥおっさん

ここまで読んでくれてありがとう！

「記事を読んでもわからないトコがある」「内容が変だよ」
という時は、お気軽にコメントください♪

「もっとＳＥおっさんに詳しく聞きたい。何かお願いしたい！」
という時は、ココナラまで。メッセージもお気軽に♪

LINEでのお問合わせも受付中！
LINE公式アカウント

メッセージをお待ちしています！

Follow @koisyo_ok

取得資格