Oracle PL/SQL Tutorial/System Tables Data Dictionary/user dependencies

Содержание

1 Check dependencies for procedure
2 Check package reference
3 Query user_dependencies
4 Query user_dependencies for a function
5 Query user_dependencies for reference name

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>

Query user_dependencies

   <source lang="sql">

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;</source>

Query user_dependencies for a function

   <source lang="sql">

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.</source>

Query user_dependencies for reference name

   <source lang="sql">

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></source>

Oracle PL/SQL Tutorial/System Tables Data Dictionary/user dependencies

Содержание

Check dependencies for procedure

Check package reference

Query user_dependencies

Query user_dependencies for a function

Query user_dependencies for reference name

Навигация

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

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

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты