有沒有辦法以編程方式從 Oracle 包中提取表引用？ (Is there a way to programmatically extract table references from an Oracle package?)

問題描述

有沒有辦法以編程方式從 Oracle 包中提取表引用？ (Is there a way to programmatically extract table references from an Oracle package?)

一年有幾次，我的團隊會出於某種原因編寫一個新的 Oracle 包，而且大部分時間這個包只引用包（我們的包運行的用戶/角色）已經獲得授權的表。

有時候，情況並非如此。包被簽入，編譯失敗，我們得到一個神秘的錯誤信息，在表名附近有一個行號。

麻煩的是，此時我們將等待幾個小時為了（政治）批准過程發揮作用，給予授權，我們簽入包......但它失敗了，並出現了一個新的表名。

有沒有辦法獲得所有的列表以編程方式引用？如果是這樣，我們可以自動檢查 dba_tab_privs 以查看是否需要事先授權，

參考解法

方法 1:

Assuming that all the references are static (i.e. you don't have bits of random dynamic SQL in your code), you can query `dba_dependencies'

select referenced_owner, referenced_name
  from dba_dependencies
 where owner = <<owner of package>>
   and name  = <<name of package>>
   and type  = 'PACKAGE BODY'
   and referenced_type = 'TABLE'

You could look for non‑table references as well or dependencies related to the package specification as well as the package body if you'd like. This will just show cases where the package body depends on a table.

方法 2:

Have a look at the ALL_SOURCE table. You can query against the TEXT column for what you are looking for.

select *
from all_source
where type = 'PACKAGE BODY'
and owner = 'XYZ'
and name = 'YOUR PACKAGE NAME'
order by line;

Edit: Justin Cave's answer is the correct one, but I'll leave this here for info anyway as every little bit of info helps.

(by John O、Justin Cave、Gary_W)

參考文件

1. Is there a way to programmatically extract table references from an Oracle package? (CC BY‑SA 2.5/3.0/4.0)