Oracle PL/SQL/System Tables Views/user dependencies

Содержание

1 Check dependencies for procedure
2 Check package reference
3 Check user dependencies for a given table
4 Query user_dependencies for cross references
5 Who referenced current user"s objects

Check dependencies for procedure

   <source lang="sql">

SQL> CREATE TABLE EMP(

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created. SQL> create or replace procedure P1 is

 2   v_cnt number;
 3   begin
 4       select count(*) into v_cnt from emp;
 5   end;
 6  /

SQL> create or replace procedure P2 is

 2   begin
 3       P1;
 4   end;
 5  /

SQL> create or replace procedure P3 is

 2   begin
 3       P2;
 4   end;
 5  /

SQL> create or replace procedure P4 is

 2   begin
 3       P3;
 4   end;
 5  /

SQL> SQL> select name, type,referenced_name, referenced_type from user_Dependencies where rownum < 20

 2  /

Enter... SQL>

 </source>

Check package reference

   <source lang="sql">

SQL> SQL> create table t ( x int ); Table created. SQL> SQL> create or replace view v as select * from t; View created. SQL> SQL> create or replace procedure p

 2  as
 3  begin
 4          for x in ( select * from t )
 5          loop
 6                  null;
 7          end loop;
 8  end;
 9  /

SQL> SQL> create or replace function f return number

 2  as
 3          countValue number;
 4  begin
 5          select count(*) into countValue from t;
 6          return countValue;
 7  end;
 8  /

Function created. SQL> SQL> SQL> create or replace procedure p2

 2  as
 3  begin
 4          p;
 5  end;
 6  /

Warning: Procedure created with compilation errors. SQL> SQL> create or replace package p1

 2  as
 3          procedure p;
 4  end;
 5  /

SQL> SQL> create or replace package body p1

 2  as
 3  procedure p
 4  as
 5  begin
 6          for x in ( select * from t )
 7          loop
 8                  null;
 9          end loop;
10  end;
11  end p1;
12  /

Warning: Package Body created with compilation errors. SQL> SQL> create or replace package p2

 2  as
 3          procedure p;
 4  end;
 5  /

SQL> SQL> create or replace package body p2

 2  as
 3  procedure p
 4  as
 5  begin
 6          p1.p;
 7  end;
 8  end p2;
 9  /

Warning: Package Body created with compilation errors. SQL> SQL> select name, type, referenced_name, referenced_type

 2    from user_dependencies
 3   where referenced_owner = user  and rownum < 20
 4   order by name
 5  /

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

COMPILEERROR PROCEDURE DUAL NON-EXISTENT COMPILE_ERROR PROCEDURE DUAL NON-EXISTENT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

DEFINER_RIGHTS PACKAGE BODY DUAL NON-EXISTENT LOOPING_EXAMPLE PROCEDURE DBMS_OUTPUT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

NON-EXISTENT MONTH_GENERATOR FUNCTION SQLMONTH_TABLETYPE TYPE MONTH_GENERATOR_PIPED FUNCTION NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

SQLMONTH_TABLETYPE TYPE ORD_GENERATOR FUNCTION ORD_TYPE TYPE

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

ORD_GENERATOR FUNCTION ORD_TABLE TYPE ORD_GENERATOR_PIPED FUNCTION ORD_TABLE TYPE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

ORD_GENERATOR_PIPED FUNCTION ORD_TYPE TYPE ORD_TABLE TYPE ORD_TYPE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

TYPE PRODUCT_PACKAGE PACKAGE BODY PRODUCT_PACKAGE PACKAGE P_ADD_ORDERS PROCEDURE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

DUAL NON-EXISTENT P_ADD_ORDERS PROCEDURE ORDER_SEQ NON-EXISTENT

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

P_ADD_ORDERS PROCEDURE P_ADD_ITEMS PROCEDURE P_ADD_PROD PROCEDURE PRODUCT_SEQ NON-EXISTENT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

READCLOB PROCEDURE DBMS_OUTPUT NON-EXISTENT TEMPERATURE_CONVERSION PROCEDURE DBMS_OUTPUT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

NON-EXISTENT USE_G2 FUNCTION DUAL NON-EXISTENT

19 rows selected.

 </source>

Check user dependencies for a given table

   <source lang="sql">

SQL> select name, type,

 2   referenced_name, referenced_type
 3   from user_Dependencies
 4   where name = "EMP_MAINT"
 5  /

no rows selected SQL>

 </source>

Query user_dependencies for cross references

   <source lang="sql">

SQL> SQL> SQL> create table t ( x int ); Table created. SQL> SQL> create or replace view v as select * from t; View created. SQL> SQL> create or replace procedure p

 2  as
 3  begin
 4          for x in ( select * from t )
 5          loop
 6                  null;
 7          end loop;
 8  end;
 9  /

SQL> SQL> create or replace function f return number

 2  as
 3          countValue number;
 4  begin
 5          select count(*) into countValue from t;
 6          return countValue;
 7  end;
 8  /

Function created. SQL> SQL> select name, type, referenced_name, referenced_type

 2    from user_dependencies
 3   where referenced_owner = user and rownum < 20
 4   order by name
 5  /

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

COMPILEERROR PROCEDURE DUAL NON-EXISTENT COMPILE_ERROR PROCEDURE DUAL NON-EXISTENT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

DEFINER_RIGHTS PACKAGE BODY DUAL NON-EXISTENT LOOPING_EXAMPLE PROCEDURE DBMS_OUTPUT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

NON-EXISTENT MONTH_GENERATOR FUNCTION SQLMONTH_TABLETYPE TYPE MONTH_GENERATOR_PIPED FUNCTION NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

SQLMONTH_TABLETYPE TYPE ORD_GENERATOR FUNCTION ORD_TYPE TYPE

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

ORD_GENERATOR FUNCTION ORD_TABLE TYPE ORD_GENERATOR_PIPED FUNCTION ORD_TABLE TYPE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

ORD_GENERATOR_PIPED FUNCTION ORD_TYPE TYPE ORD_TABLE TYPE ORD_TYPE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

TYPE PRODUCT_PACKAGE PACKAGE BODY PRODUCT_PACKAGE PACKAGE P_ADD_ORDERS PROCEDURE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

DUAL NON-EXISTENT P_ADD_ORDERS PROCEDURE ORDER_SEQ NON-EXISTENT

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

P_ADD_ORDERS PROCEDURE P_ADD_ITEMS PROCEDURE P_ADD_PROD PROCEDURE PRODUCT_SEQ NON-EXISTENT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

READCLOB PROCEDURE DBMS_OUTPUT NON-EXISTENT TEMPERATURE_CONVERSION PROCEDURE DBMS_OUTPUT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

NON-EXISTENT USE_G2 FUNCTION DUAL NON-EXISTENT

19 rows selected. SQL> SQL> drop table t; Table dropped.

 </source>

Who referenced current user"s objects

   <source lang="sql">

SQL> SQL> create table t ( x int ); Table created. SQL> SQL> create or replace view v as select * from t; View created. SQL> SQL> create or replace procedure p

 2  as
 3  begin
 4          for x in ( select * from t )
 5          loop
 6                  null;
 7          end loop;
 8  end;
 9  /

SQL> SQL> create or replace function f return number

 2  as
 3          countValue number;
 4  begin
 5          select count(*) into countValue from t;
 6          return countValue;
 7  end;
 8  /

Function created. SQL> SQL> SQL> create or replace procedure p2

 2  as
 3  begin
 4          p;
 5  end;
 6  /

Warning: Procedure created with compilation errors. SQL> SQL> SQL> select name, type, referenced_name, referenced_type

 2    from user_dependencies
 3   where referenced_owner = user  and rownum < 20
 4   order by name
 5  /

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

COMPILEERROR PROCEDURE DUAL NON-EXISTENT COMPILE_ERROR PROCEDURE DUAL NON-EXISTENT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

DEFINER_RIGHTS PACKAGE BODY DUAL NON-EXISTENT LOOPING_EXAMPLE PROCEDURE DBMS_OUTPUT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

NON-EXISTENT MONTH_GENERATOR FUNCTION SQLMONTH_TABLETYPE TYPE MONTH_GENERATOR_PIPED FUNCTION NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

SQLMONTH_TABLETYPE TYPE ORD_GENERATOR FUNCTION ORD_TYPE TYPE

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

ORD_GENERATOR FUNCTION ORD_TABLE TYPE ORD_GENERATOR_PIPED FUNCTION ORD_TABLE TYPE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

ORD_GENERATOR_PIPED FUNCTION ORD_TYPE TYPE ORD_TABLE TYPE ORD_TYPE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

TYPE PRODUCT_PACKAGE PACKAGE BODY PRODUCT_PACKAGE PACKAGE P_ADD_ORDERS PROCEDURE NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

DUAL NON-EXISTENT P_ADD_ORDERS PROCEDURE ORDER_SEQ NON-EXISTENT

NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

P_ADD_ORDERS PROCEDURE P_ADD_ITEMS PROCEDURE P_ADD_PROD PROCEDURE PRODUCT_SEQ NON-EXISTENT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

READCLOB PROCEDURE DBMS_OUTPUT NON-EXISTENT TEMPERATURE_CONVERSION PROCEDURE DBMS_OUTPUT NAME TYPE

-----------------

REFERENCED_NAME

REFERENCED_TYPE

NON-EXISTENT USE_G2 FUNCTION DUAL NON-EXISTENT

19 rows selected. SQL> SQL> drop table t; Table dropped.

 </source>

Oracle PL/SQL/System Tables Views/user dependencies

Содержание

Check dependencies for procedure

Check package reference

Check user dependencies for a given table

Query user_dependencies for cross references

Who referenced current user"s objects

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты