質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Oracle Database

Oracle Databaseは、米オラクルが開発・販売を行うリレーショナルデータベース管理システムです。

Oracle Database 12c

Oracle Database 12cは、RDBMS製品です。世界初の商用RDBMSであるOracle Databaseのバージョンで、2013年にリリース。クラウド対応を考慮して設計されました。主な機能には、マルチテナント対応やインメモリデータベースの実装などがあります。

PL/SQL

PL/SQL (Procedural Language/Structured Query Language) はOracle CorporationによるSQL(非手続き型言語)を手続き型言語に拡張させるために開発されたプログラミング言語です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

Q&A

解決済

1回答

761閲覧

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

net-ohkubo

総合スコア7

Oracle Database

Oracle Databaseは、米オラクルが開発・販売を行うリレーショナルデータベース管理システムです。

Oracle Database 12c

Oracle Database 12cは、RDBMS製品です。世界初の商用RDBMSであるOracle Databaseのバージョンで、2013年にリリース。クラウド対応を考慮して設計されました。主な機能には、マルチテナント対応やインメモリデータベースの実装などがあります。

PL/SQL

PL/SQL (Procedural Language/Structured Query Language) はOracle CorporationによるSQL(非手続き型言語)を手続き型言語に拡張させるために開発されたプログラミング言語です。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

0グッド

0クリップ

投稿2020/11/12 01:27

編集2020/11/30 11:11

あるデータベースユーザ(ユーザA)が
ストアド・パッケージ2つ保持しており(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をネスト構造にせず1パッケージにまとめる
等を検討しております。

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

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

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

PLSQL

1/* 親パッケージ */ 2create or replace package pkg_oya as 3 type VARARRAY is table of varchar2(2000) index by binary_integer; 4 i binary_integer; 5 procedure spr_oya ( i_cd in number, 6 sts out number); 7End pkg_oya; 8/ 9create or replace package body pkg_oya as 10 procedure spr_oya ( i_cd in number, 11 sts out number) is 12 begin 13 /* 子パッケージ呼出し 子からsts=88を受け取る */ 14 pkg_ko.spr_ko( i_cd, sts ); 15 16 if sts = 88 then 17 sts := 88; 18 else 19 sts := 0; 20 end if; 21 exception 22 when others then 23 sts := 99; 24 end spr_oya; 25End pkg_oya; 26/

PLSQL

1/* 子パッケージ */ 2create or replace package pkg_ko as 3 i BINARY_INTEGER; 4 5 Procedure spr_ko 6 (i_cd in number, 7 sts in out number); 8End pkg_ko; 9/ 10create or replace package body pkg_ko is 11 Procedure spr_ko 12 (i_cd in number, 13 sts in out number) is 14 Begin 15 sts := 88; 16 goto end_proc; 17 18 << abort_proc >> 19 20 << end_proc >> 21 null; 22 End spr_ko; 23End; 24/

確認手順
1.ユーザAで親パッケージ(pkg_oya)、子パッケージ(pkg_ko)を定義
2.ユーザB向けにGRANT EXECUTE(pkg_oyaのみ付与。)
3.ユーザ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;

4.その後、ユーザAで子パッケージ(pkg_ko)をリコンパイル
5.ユーザB側でpkg_oyaを実行(子パッケージ部でExceptionになりコード99が返却される)
6.ユーザA側でpkg_oyaを実行(子パッケージのコード88が返却される)
7.ユーザAで親パッケージ(pkg_oya)をリコンパイルし、再度ユーザBへ権限設定
8.ユーザB側でpkg_oyaを実行(子パッケージのコード88が返却される)(元に戻る)

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

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

投稿2020/11/12 02:01

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

net-ohkubo

2020/11/12 02:14

Orlofskyさま 早速のご回答ありがとうございます。 ご質問の件、実はINVALIDになる詳しい原因は分かっておりません。 様々な処理で該当のパッケージをコールしているため(またシステム規模も大きいため) 一律で、INVALIDオブジェクトのリコンパイルを実施している状況です。 なお、ご指摘の強制コンパイルにつきましては 週1度のDB再起動運用時に合わせて行っています。 この強制再起動においても、 例えばpkg1→pkg2の順でコンパイルを行えば 同様の事象(別ユーザでpkg1をコールしてもpkg2が稼働しない)が発生します。
Orlofsky

2020/11/12 02:43

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

2020/11/27 08:26

INVALIDになる原因が判明しました。 ストアドパッケージ内で利用している、とあるテーブルについて import処理が行われた後に発生しておりました。 該当処理は見直しを掛けており、ひとまずリコンパイルは起こらないようになりました。 ただ、親子のパッケージ共にVALIDにの状態であっても、 その後子のパッケージだけリコンパイルした後では 別ユーザBからのpkg1実行にてpkg2が空振りして終了する事象が 発生するため、引き続き回答の募集をいたします。
Orlofsky

2020/11/27 10:11

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

2020/11/30 02:32

回答の募集箇所が不明慮でしたね、失礼しました。 恐れ入りますが、解決方法が知りたいわけでは無く、 PL/SQLの記法も分かっております(上述の通り解決しました) 後知りたいこととしては、以下のOracleでの動作のところになります。  ネストしたパッケージについては、  子のパッケージ(pkg2)をリコンパイルした場合には  ストアド・パッケージpkg1で内包されていると思われるpkg2に対する  executeオブジェクト権限が外れてしまう等の動作になるのでしょうか。
Orlofsky

2020/11/30 06:40

質問に現象を再現できるだけの必要最小限の情報を提示していませんから、怪しいところにあちこち 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

2020/11/30 11:02

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

2020/11/30 11:24

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問