http://www.sqle.ru/index.php?action=history&feed=atom&title=Oracle_PL%2FSQL%2FSystem_Tables_Views%2Fuser_dependencies
Oracle PL/SQL/System Tables Views/user dependencies - История изменений
2026-05-23T22:15:33Z
История изменений этой страницы в вики
MediaWiki 1.30.0
http://www.sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/user_dependencies&diff=2208&oldid=prev
в 13:45, 26 мая 2010
2010-05-26T13:45:19Z
<p></p>
<table class="diff diff-contentalign-left" data-mw="interface">
<tr style="vertical-align: top;" lang="ru">
<td colspan="1" style="background-color: white; color:black; text-align: center;">← Предыдущая</td>
<td colspan="1" style="background-color: white; color:black; text-align: center;">Версия 13:45, 26 мая 2010</td>
</tr><tr><td colspan="2" style="text-align: center;" lang="ru"><div class="mw-diff-empty">(нет различий)</div>
</td></tr></table>
http://www.sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/user_dependencies&diff=2209&oldid=prev
Admin: 1 версия
2010-05-26T10:01:11Z
<p>1 версия</p>
<p><b>Новая страница</b></p><div>==Check dependencies for procedure==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL> CREATE TABLE EMP(<br />
2 EMPNO NUMBER(4) NOT NULL,<br />
3 ENAME VARCHAR2(10),<br />
4 JOB VARCHAR2(9),<br />
5 MGR NUMBER(4),<br />
6 HIREDATE DATE,<br />
7 SAL NUMBER(7, 2),<br />
8 COMM NUMBER(7, 2),<br />
9 DEPTNO NUMBER(2)<br />
10 );<br />
Table created.<br />
SQL> create or replace procedure P1 is<br />
2 v_cnt number;<br />
3 begin<br />
4 select count(*) into v_cnt from emp;<br />
5 end;<br />
6 /<br />
<br />
SQL> create or replace procedure P2 is<br />
2 begin<br />
3 P1;<br />
4 end;<br />
5 /<br />
<br />
SQL> create or replace procedure P3 is<br />
2 begin<br />
3 P2;<br />
4 end;<br />
5 /<br />
<br />
SQL> create or replace procedure P4 is<br />
2 begin<br />
3 P3;<br />
4 end;<br />
5 /<br />
<br />
SQL><br />
SQL> select name, type,referenced_name, referenced_type from user_Dependencies where rownum < 20<br />
2 /<br />
Enter...<br />
SQL><br />
<br />
<br />
<br />
<br />
</source><br />
<br />
<br />
<br />
<br />
==Check package reference==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL> create table t ( x int );<br />
Table created.<br />
SQL><br />
SQL> create or replace view v as select * from t;<br />
View created.<br />
SQL><br />
SQL> create or replace procedure p<br />
2 as<br />
3 begin<br />
4 for x in ( select * from t )<br />
5 loop<br />
6 null;<br />
7 end loop;<br />
8 end;<br />
9 /<br />
<br />
SQL><br />
SQL> create or replace function f return number<br />
2 as<br />
3 countValue number;<br />
4 begin<br />
5 select count(*) into countValue from t;<br />
6 return countValue;<br />
7 end;<br />
8 /<br />
Function created.<br />
SQL><br />
SQL><br />
SQL> create or replace procedure p2<br />
2 as<br />
3 begin<br />
4 p;<br />
5 end;<br />
6 /<br />
Warning: Procedure created with compilation errors.<br />
SQL><br />
SQL> create or replace package p1<br />
2 as<br />
3 procedure p;<br />
4 end;<br />
5 /<br />
<br />
SQL><br />
SQL> create or replace package body p1<br />
2 as<br />
3 procedure p<br />
4 as<br />
5 begin<br />
6 for x in ( select * from t )<br />
7 loop<br />
8 null;<br />
9 end loop;<br />
10 end;<br />
11 end p1;<br />
12 /<br />
Warning: Package Body created with compilation errors.<br />
SQL><br />
SQL> create or replace package p2<br />
2 as<br />
3 procedure p;<br />
4 end;<br />
5 /<br />
<br />
SQL><br />
SQL> create or replace package body p2<br />
2 as<br />
3 procedure p<br />
4 as<br />
5 begin<br />
6 p1.p;<br />
7 end;<br />
8 end p2;<br />
9 /<br />
Warning: Package Body created with compilation errors.<br />
SQL><br />
SQL> select name, type, referenced_name, referenced_type<br />
2 from user_dependencies<br />
3 where referenced_owner = user and rownum < 20<br />
4 order by name<br />
5 /<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
COMPILEERROR PROCEDURE<br />
DUAL<br />
NON-EXISTENT<br />
COMPILE_ERROR PROCEDURE<br />
DUAL<br />
NON-EXISTENT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
DEFINER_RIGHTS PACKAGE BODY<br />
DUAL<br />
NON-EXISTENT<br />
LOOPING_EXAMPLE PROCEDURE<br />
DBMS_OUTPUT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
NON-EXISTENT<br />
MONTH_GENERATOR FUNCTION<br />
SQLMONTH_TABLETYPE<br />
TYPE<br />
MONTH_GENERATOR_PIPED FUNCTION<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
SQLMONTH_TABLETYPE<br />
TYPE<br />
ORD_GENERATOR FUNCTION<br />
ORD_TYPE<br />
TYPE<br />
<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
ORD_GENERATOR FUNCTION<br />
ORD_TABLE<br />
TYPE<br />
ORD_GENERATOR_PIPED FUNCTION<br />
ORD_TABLE<br />
TYPE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
ORD_GENERATOR_PIPED FUNCTION<br />
ORD_TYPE<br />
TYPE<br />
ORD_TABLE TYPE<br />
ORD_TYPE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
TYPE<br />
PRODUCT_PACKAGE PACKAGE BODY<br />
PRODUCT_PACKAGE<br />
PACKAGE<br />
P_ADD_ORDERS PROCEDURE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
DUAL<br />
NON-EXISTENT<br />
P_ADD_ORDERS PROCEDURE<br />
ORDER_SEQ<br />
NON-EXISTENT<br />
<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
P_ADD_ORDERS PROCEDURE<br />
P_ADD_ITEMS<br />
PROCEDURE<br />
P_ADD_PROD PROCEDURE<br />
PRODUCT_SEQ<br />
NON-EXISTENT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
READCLOB PROCEDURE<br />
DBMS_OUTPUT<br />
NON-EXISTENT<br />
TEMPERATURE_CONVERSION PROCEDURE<br />
DBMS_OUTPUT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
NON-EXISTENT<br />
USE_G2 FUNCTION<br />
DUAL<br />
NON-EXISTENT<br />
<br />
19 rows selected.<br />
<br />
<br />
<br />
<br />
</source><br />
<br />
<br />
<br />
<br />
==Check user dependencies for a given table==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL> select name, type,<br />
2 referenced_name, referenced_type<br />
3 from user_Dependencies<br />
4 where name = "EMP_MAINT"<br />
5 /<br />
no rows selected<br />
SQL><br />
<br />
<br />
<br />
<br />
</source><br />
<br />
<br />
<br />
<br />
==Query user_dependencies for cross references==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL><br />
SQL> create table t ( x int );<br />
Table created.<br />
SQL><br />
SQL> create or replace view v as select * from t;<br />
View created.<br />
SQL><br />
SQL> create or replace procedure p<br />
2 as<br />
3 begin<br />
4 for x in ( select * from t )<br />
5 loop<br />
6 null;<br />
7 end loop;<br />
8 end;<br />
9 /<br />
<br />
SQL><br />
SQL> create or replace function f return number<br />
2 as<br />
3 countValue number;<br />
4 begin<br />
5 select count(*) into countValue from t;<br />
6 return countValue;<br />
7 end;<br />
8 /<br />
Function created.<br />
SQL><br />
SQL> select name, type, referenced_name, referenced_type<br />
2 from user_dependencies<br />
3 where referenced_owner = user and rownum < 20<br />
4 order by name<br />
5 /<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
COMPILEERROR PROCEDURE<br />
DUAL<br />
NON-EXISTENT<br />
COMPILE_ERROR PROCEDURE<br />
DUAL<br />
NON-EXISTENT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
DEFINER_RIGHTS PACKAGE BODY<br />
DUAL<br />
NON-EXISTENT<br />
LOOPING_EXAMPLE PROCEDURE<br />
DBMS_OUTPUT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
NON-EXISTENT<br />
MONTH_GENERATOR FUNCTION<br />
SQLMONTH_TABLETYPE<br />
TYPE<br />
MONTH_GENERATOR_PIPED FUNCTION<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
SQLMONTH_TABLETYPE<br />
TYPE<br />
ORD_GENERATOR FUNCTION<br />
ORD_TYPE<br />
TYPE<br />
<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
ORD_GENERATOR FUNCTION<br />
ORD_TABLE<br />
TYPE<br />
ORD_GENERATOR_PIPED FUNCTION<br />
ORD_TABLE<br />
TYPE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
ORD_GENERATOR_PIPED FUNCTION<br />
ORD_TYPE<br />
TYPE<br />
ORD_TABLE TYPE<br />
ORD_TYPE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
TYPE<br />
PRODUCT_PACKAGE PACKAGE BODY<br />
PRODUCT_PACKAGE<br />
PACKAGE<br />
P_ADD_ORDERS PROCEDURE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
DUAL<br />
NON-EXISTENT<br />
P_ADD_ORDERS PROCEDURE<br />
ORDER_SEQ<br />
NON-EXISTENT<br />
<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
P_ADD_ORDERS PROCEDURE<br />
P_ADD_ITEMS<br />
PROCEDURE<br />
P_ADD_PROD PROCEDURE<br />
PRODUCT_SEQ<br />
NON-EXISTENT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
READCLOB PROCEDURE<br />
DBMS_OUTPUT<br />
NON-EXISTENT<br />
TEMPERATURE_CONVERSION PROCEDURE<br />
DBMS_OUTPUT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
NON-EXISTENT<br />
USE_G2 FUNCTION<br />
DUAL<br />
NON-EXISTENT<br />
<br />
19 rows selected.<br />
SQL><br />
SQL> drop table t;<br />
Table dropped.<br />
<br />
<br />
<br />
</source><br />
<br />
<br />
<br />
<br />
==Who referenced current user"s objects==<br />
<br />
<br />
<br />
<br />
<!-- start source code --><br />
<br />
<source lang="sql"><br />
<br />
SQL><br />
SQL> create table t ( x int );<br />
Table created.<br />
SQL><br />
SQL> create or replace view v as select * from t;<br />
View created.<br />
SQL><br />
SQL> create or replace procedure p<br />
2 as<br />
3 begin<br />
4 for x in ( select * from t )<br />
5 loop<br />
6 null;<br />
7 end loop;<br />
8 end;<br />
9 /<br />
<br />
SQL><br />
SQL> create or replace function f return number<br />
2 as<br />
3 countValue number;<br />
4 begin<br />
5 select count(*) into countValue from t;<br />
6 return countValue;<br />
7 end;<br />
8 /<br />
Function created.<br />
SQL><br />
SQL><br />
SQL> create or replace procedure p2<br />
2 as<br />
3 begin<br />
4 p;<br />
5 end;<br />
6 /<br />
Warning: Procedure created with compilation errors.<br />
SQL><br />
SQL><br />
SQL> select name, type, referenced_name, referenced_type<br />
2 from user_dependencies<br />
3 where referenced_owner = user and rownum < 20<br />
4 order by name<br />
5 /<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
COMPILEERROR PROCEDURE<br />
DUAL<br />
NON-EXISTENT<br />
COMPILE_ERROR PROCEDURE<br />
DUAL<br />
NON-EXISTENT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
DEFINER_RIGHTS PACKAGE BODY<br />
DUAL<br />
NON-EXISTENT<br />
LOOPING_EXAMPLE PROCEDURE<br />
DBMS_OUTPUT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
NON-EXISTENT<br />
MONTH_GENERATOR FUNCTION<br />
SQLMONTH_TABLETYPE<br />
TYPE<br />
MONTH_GENERATOR_PIPED FUNCTION<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
SQLMONTH_TABLETYPE<br />
TYPE<br />
ORD_GENERATOR FUNCTION<br />
ORD_TYPE<br />
TYPE<br />
<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
ORD_GENERATOR FUNCTION<br />
ORD_TABLE<br />
TYPE<br />
ORD_GENERATOR_PIPED FUNCTION<br />
ORD_TABLE<br />
TYPE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
ORD_GENERATOR_PIPED FUNCTION<br />
ORD_TYPE<br />
TYPE<br />
ORD_TABLE TYPE<br />
ORD_TYPE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
TYPE<br />
PRODUCT_PACKAGE PACKAGE BODY<br />
PRODUCT_PACKAGE<br />
PACKAGE<br />
P_ADD_ORDERS PROCEDURE<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
DUAL<br />
NON-EXISTENT<br />
P_ADD_ORDERS PROCEDURE<br />
ORDER_SEQ<br />
NON-EXISTENT<br />
<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
P_ADD_ORDERS PROCEDURE<br />
P_ADD_ITEMS<br />
PROCEDURE<br />
P_ADD_PROD PROCEDURE<br />
PRODUCT_SEQ<br />
NON-EXISTENT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
READCLOB PROCEDURE<br />
DBMS_OUTPUT<br />
NON-EXISTENT<br />
TEMPERATURE_CONVERSION PROCEDURE<br />
DBMS_OUTPUT<br />
NAME TYPE<br />
------------------------------ -----------------<br />
REFERENCED_NAME<br />
----------------------------------------------------------------<br />
REFERENCED_TYPE<br />
-----------------<br />
NON-EXISTENT<br />
USE_G2 FUNCTION<br />
DUAL<br />
NON-EXISTENT<br />
<br />
19 rows selected.<br />
SQL><br />
SQL> drop table t;<br />
Table dropped.<br />
<br />
<br />
<br />
<br />
</source></div>
Admin