MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Find Type Dependents

without comments

Somebody thought it was nice to show how to drop object type dependents in Oracle Database 11g, but they thought I should show how you discover dependent object types first. More or less, they were concerned how they discover type dependents when they raise the following error:

DROP TYPE item_object
*
ERROR at line 1:
ORA-02303: cannot DROP OR REPLACE a TYPE WITH TYPE OR TABLE dependents

They had a great point, so here I’m providing and object table function (object table functions are covered in Chapter 8 of Oracle Database 12c PL/SQL Programming book) that displays an object dependency tree.

Unlike table functions in Microsoft SQL Server or PostgreSQL, Oracle requires that you create the object type before you can return a result set table from a parameter-driven function. Here’s the object type for this object table function:

1
2
3
4
5
6
7
8
9
10
11
-- Create an object type that mimics a record structure.  
CREATE OR REPLACE TYPE type_tree IS object
( level_id     NUMBER
, type_name    VARCHAR2(30)
, object       VARCHAR2(10)
, parent_type  VARCHAR2(30)); 
/
 
-- Create a UDT Collection.
CREATE OR REPLACE TYPE type_tree_table IS TABLE OF type_tree;
/

Now, you can create the object table function that returns an object type dependent tree. This function uses branching recursion because you need to walk the dependency tree. The cursor against the three types of metadata in the database catalog lets us walk the tree. Each object type, and their dependents may have:

  • A dependent object type
  • A dependent collection, which uses the object type as a base object type.
  • A dependent schema function, which uses the object type as a parameter data type or return data type.
  • A dependent schema procedure, which uses the object type as a parameter data type.
  • A dependent package, which contains a function or procedure using the object type as a parameter data type, or a function using the object type as a return data type.

Here’s the type_dependent function that walks the tree and displays the hierarchical results:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
CREATE OR REPLACE
  FUNCTION type_dependents
  ( pv_base_object_type  VARCHAR2
  , pv_level_id          NUMBER DEFAULT 1
  , pv_collection        TYPE_TREE_TABLE DEFAULT NULL ) RETURN TYPE_TREE_TABLE IS
 
  /* Declare a return data type. */
  lv_level_id     NUMBER;
 
  /* Declare item type. */
  lv_type_name    VARCHAR2(30);
  lv_object_type  VARCHAR2(30);
  lv_parent_type  VARCHAR2(30);
 
  /* Declare a collection variable. */
  lv_collection   TYPE_TREE_TABLE := type_tree_table();
  lv_incoming     TYPE_TREE_TABLE := type_tree_table();
 
  /* The first part of the cursor finds the dependent type names of complex object 
     types, and the second part of the cursor finds the dependent collection types.
     Effectively the set operator finds two distinct branches because you may use
     any base type as an element of a complex object or of a collection. */
  CURSOR base_type 
  ( cv_level_id   NUMBER
  , cv_base_type  VARCHAR2 ) IS
    SELECT  (cv_level_id) AS level_id
    ,        LPAD(' ', 2*(cv_level_id - 1)) || ut.type_name AS type_name
    ,        ut.typecode AS object_type
    ,        NULL AS parent_type
    FROM     user_types ut
    WHERE    ut.type_name = cv_base_type;
 
  CURSOR dependent_type 
  ( cv_level_id   NUMBER
  , cv_base_type  VARCHAR2 ) IS
    SELECT   cv_level_id AS level_id
    ,        LPAD(' ', 2*(cv_level_id - 1)) || uta.type_name AS type_name
    ,        ut.typecode AS object_type
    ,        ut.type_name AS parent_type
    FROM     user_type_attrs uta INNER JOIN user_types ut
    ON       uta.attr_type_name = ut.type_name
    WHERE    ut.type_name = cv_base_type
    UNION ALL
    SELECT   cv_level_id AS level_id
    ,        LPAD(' ', 2*(cv_level_id - 1)) || uct.type_name AS type_name
    ,        CASE
               WHEN uct.coll_type = 'TABLE' THEN
                 uct.coll_type
               ELSE
                'VARRAY'
             END AS object_type
    ,        ut.type_name AS parent_type
    FROM     user_types ut INNER JOIN user_coll_types uct
    ON       ut.type_name = uct.elem_type_name
    WHERE    uct.elem_type_name = cv_base_type
    UNION ALL
    SELECT   cv_level_id AS level_id
    ,        CASE
               WHEN package_name IS NULL THEN
                 LPAD(' ', 2*(cv_level_id - 1)) || ua.object_name
               ELSE
                 LPAD(' ', 2*(cv_level_id - 1)) || ua.package_name
             END AS type_name
    ,        CASE
               WHEN package_name IS NULL THEN
                 uo.object_type
               ELSE
                'PACKAGE'
             END AS object_type
    ,        ua.type_name AS parent_type
    FROM     user_arguments ua LEFT JOIN user_objects uo
    ON       ua.package_name = uo.object_name
    OR       ua.object_name = uo.object_name
    WHERE    type_name = cv_base_type
    ORDER BY object_type;
 
BEGIN
 
  /* Set the call level indicator. */
  lv_level_id := pv_level_id;
 
  /* Check if the collection holds values, which should occur in recursive calls.  */
  IF pv_collection IS NOT NULL THEN
    lv_collection := pv_collection;
  ELSE
    /* Open the base type and record level 1. */
    OPEN  base_type (lv_level_id, pv_base_object_type);
    FETCH base_type
    INTO  lv_level_id
    ,     lv_type_name
    ,     lv_object_type
    ,     lv_parent_type;
    CLOSE base_type;
 
    /* Extend the collection. */
    lv_collection.EXTEND;
    lv_collection(lv_collection.COUNT) := type_tree( lv_level_id
                                                   , lv_type_name
                                                   , lv_object_type
                                                   , lv_parent_type );
 
    /* Increment the type dependency level. */
    lv_level_id := lv_level_id + 1;
 
  END IF;
 
  /* Loop through and return records. */
  FOR i IN dependent_type(lv_level_id, pv_base_object_type) LOOP
 
    /* Extend the collection. */
    lv_collection.EXTEND;
    lv_collection(lv_collection.COUNT) := type_tree( i.level_id
                                                   , i.type_name
                                                   , i.object_type
                                                   , i.parent_type );
 
    /* Recursively call down to dependent types. */
    IF i.type_name <> i.parent_type THEN
      lv_collection := type_dependents(TRIM(i.type_name), (lv_level_id + 1), lv_collection);
    END IF;
 
  END LOOP;
 
  /* Return 0 for false. */
  RETURN lv_collection;
END;
/

You would query the function with the TABLE function, like this:

COLUMN level_id     FORMAT 99999 HEADING "Level|ID #"
COLUMN type_name    FORMAT A30   HEADING "Type Name"
COLUMN object       FORMAT A10   HEADING "Object Type"
COLUMN parent_type  FORMAT A30   HEADING "Parent Type"
 
SELECT   *
FROM     TABLE(type_dependents('ITEM_OBJECT',1));

It displays the following results based on the sample types created by the original blog post:

 Level
  ID # Type Name                      Object Typ Parent Type
------ ------------------------------ ---------- ------------------------
     1 ITEM_OBJECT                    OBJECT
     2   GET_ITEM_OBJECT              FUNCTION   ITEM_OBJECT
     2   IDENTIFIED_OBJECT            OBJECT     ITEM_OBJECT
     2   ITEM_PACKAGE                 PACKAGE    ITEM_OBJECT
     2   ITEM_TABLE                   TABLE      ITEM_OBJECT
     3     ITEM_ASYNC_TABLE           COLLECTION ITEM_TABLE
     4       ITEM_LIST                TABLE      ITEM_ASYNC_TABLE
     2   ITEM_VARRAY                  VARRAY     ITEM_OBJECT
     3     ITEM_ASYNC_VARRAY          COLLECTION ITEM_VARRAY
     4       ITEM_ARRAY               VARRAY     ITEM_ASYNC_VARRAY
 
10 rows selected.

I hope this helps those working with Oracle Database 10g (de-supported), 11g, or 12c. As a note, you don’t need to drop type dependents in Oracle 12c because it supports type evolution. I cover type evolution in Appendix B of Oracle Database 12c PL/SQL Programming. As always, I hope this helps those trying to determine type dependents in an Oracle database.

Written by maclochlainn

March 8th, 2014 at 10:33 am