Oracle PL/SQL Tutorial/System Tables Data Dictionary/user dependencies
Содержание
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>