Oracle PL/SQL/System Tables Views/user dependencies

Материал из SQL эксперт
Версия от 10:01, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Check dependencies for procedure

    
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>



Check package reference

    
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.



Check user dependencies for a given table

    
SQL> select name, type,
  2   referenced_name, referenced_type
  3   from user_Dependencies
  4   where name = "EMP_MAINT"
  5  /
no rows selected
SQL>



Query user_dependencies for cross references

   
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.



Who referenced current user"s objects

    
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.
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/System_Tables_Views/user_dependencies&oldid=2209»