Re: [SQL ] 請問一個Oracle語法
※ 引述《ling123 (@@)》之銘言：
: 請問一個Oracle語法
: 要如何知道該Function/Procedure/View
: References到哪個Table或Function阿?
: 在PL/SQL Developer這套工具可以看到References資訊
: 那要如何用SQL語法串出呢?
: 謝謝~
基本上是用 SYS.DBA_OBJECTS 和 PUBLIC_DEPENDENCY 這兩個串出來的
如底下 SQL, 如果你權限不足, 試著用 USER_OBJECTS 看看
Select a.object_type, a.object_name, b.owner AS related_owner,
b.object_type AS related_object_type,
b.object_name AS related_object_name, b.status
from sys.dba_objects a,
sys.dba_objects b,
(Select object_id, referenced_object_id
FROM public_dependency
start with object_id =
(Select object_id
from sys.dba_objects
WHERE owner = '' -- your schema name here
AND object_name = '' -- your procedure name here
AND object_type = 'PROCEDURE')
connect by prior referenced_object_id = object_id) c
where a.object_id = c.object_id
and b.object_id = c.referenced_object_id
and a.owner not in ('SYS', 'SYSTEM')
and b.owner not in ('SYS', 'SYSTEM')
and a.object_name <> 'DUAL'
and b.object_name <> 'DUAL';
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 118.160.109.245
推
04/03 13:00, , 1F
04/03 13:00, 1F
推
04/03 14:09, , 2F
04/03 14:09, 2F
討論串 (同標題文章)
本文引述了以下文章的的內容：
完整討論串 (本文為第 2 之 2 篇)：