Oracle12c ネストしたストアドパッケージの実行権限について

Question

あるデータベースユーザ（ユーザA）が
ストアド・パッケージ２つ保持しており（pkg1とpkg2）、
pkg1の中でpkg2を呼び出すネスト構造になっております。
別のデータベースユーザ（ユーザB）でこのストアド・パッケージを
利用する要件があり、
ユーザAのpkg1に対してexecute オブジェクト権限をユーザBに付与し、
ユーザBでシノニム参照してpkg1を呼び出す仕組みで運用しておりました。
pkg2についてはpkg1のexecuteオブジェクト権限のみで実行されます。

データベースユーザ（ユーザA）のデイリー処理にて
オブジェクトがINVALIDとなっているものについて
リコンパイルしてVALIDに戻すバッチ処理が稼働しているのですが、
ある日、pkg2のみリコンパイル対象となった場合に
ユーザBからのpkg1実行にてpkg2が空振りして終了する事象が発生しました。
ユーザAからのpkg1実行は問題なくpkg2も稼働して正常終了します。
なおその後pkg1をリコンパイルすると、上記事象は解消され
ユーザBからのpkg1実行も正常終了します。

ネストしたパッケージについては、
子のパッケージ（pkg2）をリコンパイルした場合には
ストアド・パッケージpkg1で内包されていると思われるpkg2に対する
executeオブジェクト権限が外れてしまう等の動作になるのでしょうか。
所有者となっているユーザAではその事象が確認されないので
恐らくそうではないかとは考えてはおります。ご教授いただけると幸いです。

対策としては、
・pkg2がINVALIDになった場合pkg2だけでなくpkg1もリコンパイルする
・pkg1とpkg2をネスト構造にせず１パッケージにまとめる
等を検討しております。

(11/30追記)
その後、INVALIDになる原因と対処については実施済となり
pkg2だけがリコンパイルされる事例は排除したのですが、

上述の、pkg1とpkg2について
pkg1：VALID
pkg2：VALID　（こちらを後からコンパイルした）
と両方VALIDになっているにもかかわらず、
別ユーザBからのpkg1実行がpkg2を呼び出せずに終了してしまう、
という動作の要因が良く理解できていないままとなっておりました。

そこで以下のようなサンプルを用いてテストをしていたところ、
（結論はコードが悪いに尽きるのですが…）
子パッケージのほうに未使用の変数(i)がありそれが悪さをしていたようです。

/* 親パッケージ */
create or replace package pkg_oya as 
	type VARARRAY  is  table of varchar2(2000)  index by binary_integer;
	i binary_integer;
	procedure spr_oya (	i_cd	in	number,
				sts			out	number);
End pkg_oya;
/
create or replace package body pkg_oya as
	procedure spr_oya (	i_cd	in	number,
				sts			out	number) is
	begin
		/* 子パッケージ呼出し 子からsts=88を受け取る */
		pkg_ko.spr_ko( i_cd, sts );

		if sts = 88 then
			sts := 88;
		else
			sts := 0;
		end if;
	exception
		when others   then
			sts := 99;
	end spr_oya;
End pkg_oya;
/

/* 子パッケージ */
create or replace package pkg_ko as
    i BINARY_INTEGER;

    Procedure  spr_ko
            (i_cd            in          number,
            sts                     in out      number);
End pkg_ko;
/
create or replace package body pkg_ko is
    Procedure  spr_ko
            (i_cd            in          number,
            sts                     in out      number) is
    Begin
        sts := 88;
        goto    end_proc;

        << abort_proc >>

        << end_proc >>
            null;
    End spr_ko;
End;
/

確認手順
１．ユーザAで親パッケージ(pkg_oya)、子パッケージ(pkg_ko)を定義
２．ユーザB向けにGRANT EXECUTE（pkg_oyaのみ付与。）
３．ユーザB側でpkg_oyaを実行（例えば以下で実行すれば子パッケージのコード88が返却される）

DECLARE
  i_cd NUMBER := 1234;
  o_data NUMBER;
BEGIN
  pkg_oya.spr_oya(i_cd,o_data);
   DBMS_OUTPUT.PUT_LINE(o_data);
END;

４．その後、ユーザAで子パッケージ(pkg_ko)をリコンパイル
５．ユーザB側でpkg_oyaを実行（子パッケージ部でExceptionになりコード99が返却される）
６．ユーザA側でpkg_oyaを実行（子パッケージのコード88が返却される）
７．ユーザAで親パッケージ(pkg_oya)をリコンパイルし、再度ユーザBへ権限設定
８．ユーザB側でpkg_oyaを実行（子パッケージのコード88が返却される）（元に戻る）

なお、該当の変数iを子パッケージから除去すると、
上述の子パッケージをコンパイルしても、上述の５．
の実行結果は子パッケージ呼出し時にExceptionにならず、88が返却されました。

Answer 1

OBJECTがINVALIDになる原因はわかっていますか？
定期的にOracle Objectsの強制コンパイル を実行するのもシステムを安全に運用する手段のひとつです。

Comments

[net-ohkubo]

Orlofskyさま

早速のご回答ありがとうございます。
ご質問の件、実はINVALIDになる詳しい原因は分かっておりません。
様々な処理で該当のパッケージをコールしているため（またシステム規模も大きいため）
一律で、INVALIDオブジェクトのリコンパイルを実施している状況です。
なお、ご指摘の強制コンパイルにつきましては
週１度のDB再起動運用時に合わせて行っています。

この強制再起動においても、
例えばpkg1→pkg2の順でコンパイルを行えば
同様の事象（別ユーザでpkg1をコールしてもpkg2が稼働しない）が発生します。

[Orlofsky]

上記[強制コンパイル]は深く依存しているオブジェクトを徹底してコンパイルする為に作られたものです。リンク先のDB Serverがなくなったのに残っている DB LINK でもない限りほとんど処理時間はかかりません。日時処理に組み込んでは？
USER_DEPENDENCIESビューなどで依存性を確認して、INVALID になる原因は早めに追跡しておいた方が良いです。

[net-ohkubo]

INVALIDになる原因が判明しました。
ストアドパッケージ内で利用している、とあるテーブルについて
import処理が行われた後に発生しておりました。
該当処理は見直しを掛けており、ひとまずリコンパイルは起こらないようになりました。

ただ、親子のパッケージ共にVALIDにの状態であっても、
その後子のパッケージだけリコンパイルした後では
別ユーザBからのpkg1実行にてpkg2が空振りして終了する事象が
発生するため、引き続き回答の募集をいたします。

[Orlofsky]

だ、か、ら、
Oracle Objectsの強制コンパイル
を実行しなさい、って回答に書いたんです。
PL/SQLを読めなかったら勉強してください。

[net-ohkubo]

回答の募集箇所が不明慮でしたね、失礼しました。

恐れ入りますが、解決方法が知りたいわけでは無く、
PL/SQLの記法も分かっております（上述の通り解決しました）
後知りたいこととしては、以下のOracleでの動作のところになります。

　ネストしたパッケージについては、
　子のパッケージ（pkg2）をリコンパイルした場合には
　ストアド・パッケージpkg1で内包されていると思われるpkg2に対する
　executeオブジェクト権限が外れてしまう等の動作になるのでしょうか。

[Orlofsky]

質問に現象を再現できるだけの必要最小限の情報を提示していませんから、怪しいところにあちこち
SELECT
, OB.OBJECT_TYPE
, OB.OBJECT_NAME
FROM USER_OBJECTS OB
WHERE
OB.STATUS = 'INVALID'
AND OB.OBJECT_NAME NOT LIKE 'BIN$%' -- except trash box, since Oracle10.1.0
ORDER BY
OB.OBJECT_NAME
, OB.OBJECT_TYPE ;
に相当する内容を実行してみては？

[net-ohkubo]

色々とお付き合いいただきありがとうございます。
状況変わりまして、(11/30追記)として上記へ追記いたしましたので
悩んでいた箇所含めて御覧いただければと思います。
些細なコーディングミスが起因ではありましたが、、
具体的なコードも掲載しました。

[Orlofsky]

pkg_ko は無条件に sts := 88; を返しているだけです。
ひとつのPACKAGEにまとめた方が良さそうに思えますが、わざわざ別のPACKAGEに分けているのもわかりません。
それぞれのPACKAGE内のPROCEDUREでEXCEPTIONを取得してわかりやすいエラーメッセージを表示するきちんとした例外処理をしては？

有料の技術支援を受けた方が安上がりですよ。