Oracle PL/SQL Tutorial/System Tables Data Dictionary/user dependencies — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 10:08, 26 мая 2010

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.


Query user_dependencies

SQL> SELECT    name, type
  2  FROM      user_dependencies
  3  WHERE     referenced_name = UPPER("&object_name")
  4  AND       referenced_type = UPPER("&object_type")
  5  and       rownum < 2
  6  ORDER BY  name;


Query user_dependencies for a function

SQL>
SQL> set echo on
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> create or replace function count_emp return number
  2  as
  3          l_cnt number;
  4  begin
  5          select count(*) into l_cnt from emp;
  6          return l_cnt;
  7  end;
  8  /
Function created.
SQL>
SQL> select referenced_name, referenced_type
  2  from user_dependencies
  3  where name = "COUNT_EMP" and type = "FUNCTION"
  4  /

REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
STANDARD
PACKAGE
SYS_STUB_FOR_PURITY_ANALYSIS
PACKAGE
EMP
TABLE

SQL> select referenced_name, referenced_type
  2  from user_dependencies
  3  where name = "GET_ROW_CNTS" and type = "FUNCTION"
  4  /

REFERENCED_NAME
----------------------------------------------------------------
REFERENCED_TYPE
-----------------
STANDARD
PACKAGE
SYS_STUB_FOR_PURITY_ANALYSIS
PACKAGE

SQL> drop table emp;
Table dropped.


Query user_dependencies for reference name

SQL>
SQL> SELECT name, type, referenced_owner r_owner, referenced_name r_name,
  2         referenced_type r_type
  3  FROM   user_dependencies
  4  WHERE  name IN ("CALL_TEST_PROC", "TEST_PACK")
  5  OR     referenced_name IN ("CALL_TEST_PROC", "TEST_PACK");
NAME                           TYPE              R_OWNER
------------------------------ ----------------- ------------------------------
R_NAME
----------------------------------------------------------------
R_TYPE
-----------------
TEST_PACK                      PACKAGE BODY      SYS
STANDARD
PACKAGE
CALL_TEST_PROC                 PROCEDURE         SYS
SYS_STUB_FOR_PURITY_ANALYSIS
PACKAGE
NAME                           TYPE              R_OWNER
------------------------------ ----------------- ------------------------------
R_NAME
----------------------------------------------------------------
R_TYPE
-----------------
TEST_PACK                      PACKAGE BODY      PUBLIC
DBMS_OUTPUT
SYNONYM
TEST_PACK                      PACKAGE BODY      sqle
DBMS_OUTPUT
NAME                           TYPE              R_OWNER
------------------------------ ----------------- ------------------------------
R_NAME
----------------------------------------------------------------
R_TYPE
-----------------
NON-EXISTENT
CALL_TEST_PROC                 PROCEDURE         sqle
TEST_PACK
PACKAGE
TEST_PACK                      PACKAGE BODY      sqle
NAME                           TYPE              R_OWNER
------------------------------ ----------------- ------------------------------
R_NAME
----------------------------------------------------------------
R_TYPE
-----------------
TEST_PACK
PACKAGE

6 rows selected.
SQL>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/System_Tables_Data_Dictionary/user_dependencies&oldid=3916»