MacLochlainns Weblog

-- Create a recursive function.
CREATE OR REPLACE
  FUNCTION drop_dependents
  (pv_base_object_type  VARCHAR2) RETURN NUMBER IS
 
  /* Declare a return data type. */
  lv_retval  NUMBER := 0;
 
  /* Declare item type. */
  lv_type_name    VARCHAR2(30);
  lv_object_name  VARCHAR2(30);
 
  /* 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_base_type  VARCHAR2) IS
    SELECT   uta.type_name
    ,        NULL AS object_name
    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   uct.type_name
    ,        NULL AS object_name
    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   CASE
               WHEN package_name IS NULL THEN
                 uo.object_type
               ELSE
                 'PACKAGE'
             END AS type_name
    ,        CASE
               WHEN package_name IS NULL THEN
                 ua.object_name
               ELSE
                 ua.package_name
             END AS object_name
    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;
 
BEGIN
 
  /* Open a parameterized cursor. */  
  OPEN  base_type(pv_base_object_type);
 
  /* Loop through return records. */
  LOOP
    /* Fetch records. */
    FETCH base_type
    INTO  lv_type_name
    ,     lv_object_name;
 
    /* Drop type without dependents, or drop leaf node dependent. */
    IF base_type%NOTFOUND THEN
 
      /* Drop functions when they include an object type or object type
         dependent as a formal parameter type or return type. Drop
         procedures when they include an object type or object type
         dependent. Drop procedures when any function or procedure
         uses an object type or object type dependent. */
      IF lv_type_name IN ('FUNCTION','PACKAGE','PROCEDURE') THEN
 
        /* Drop the base type when no dependents are found. */
        EXECUTE IMMEDIATE 'DROP '||lv_type_name||' '||lv_object_name;
 
      ELSE
 
        /* Drop the base type when no dependents are found. */
        EXECUTE IMMEDIATE 'DROP TYPE '||pv_base_object_type;
 
      END IF;
 
      /* Set exit state to one or true. */
      lv_retval := 1;
 
      /* Exit the loop. */
      EXIT;
 
    ELSE
 
      /* A type must exclude function, package, and procedure; and the
         object name must be null before you recurse to another level. */
      IF lv_type_name NOT IN ('FUNCTION','PACKAGE','PROCEDURE') AND
         lv_object_name IS NOT NULL THEN
 
        /* Drop base type when no dependents are found. */
        lv_retval := drop_dependents(lv_type_name);
 
      END IF;
 
    END IF;
  END LOOP;
 
  /* Close open cursor. */
  CLOSE base_type;
 
  /* Return 0 for false. */
  RETURN lv_retval;
END;
/

-- Create object type.
CREATE OR REPLACE 
  TYPE item_object IS OBJECT
  ( item_name     VARCHAR2(30)
  , item_subname  VARCHAR2(30));
/
 
-- Create object type.
CREATE OR REPLACE 
  TYPE identified_object IS OBJECT
  ( identified_id      NUMBER
  , identified_object  item_object);
/
 
-- Create object collection.
CREATE OR REPLACE
  TYPE item_table IS TABLE OF item_object;
/
 
-- Create object collection.
CREATE OR REPLACE
  TYPE item_varray IS VARRAY(5) OF item_object;
/
 
-- Create object type.
CREATE OR REPLACE 
  TYPE item_async_table IS OBJECT
  ( item_name        VARCHAR2(30)
  , item_collection  item_table);
/
 
-- Create object type.
CREATE OR REPLACE 
  TYPE item_async_varray IS OBJECT
  ( item_name        VARCHAR2(30)
  , item_collection  item_varray);
/
 
-- Create object collection.
CREATE OR REPLACE
  TYPE item_list IS TABLE OF item_async_table;
/
 
-- Create object collection.
CREATE OR REPLACE
  TYPE item_array IS VARRAY(10) OF item_async_varray;
/
 
-- Create package specification.
CREATE OR REPLACE PACKAGE item_package AS
 
  /* A published function of the package. */
  FUNCTION initialize_object
  ( id    NUMBER
  , name  NUMBER ) RETURN ITEM_OBJECT;
 
END item_package;
/
 
-- Create a schema function.
CREATE OR REPLACE FUNCTION get_item_object
  ( pv_id    NUMBER
  , pv_name  NUMBER ) RETURN ITEM_OBJECT IS
 
  /* Declare a local variable. */
  lv_item_object   ITEM_OBJECT;
 
BEGIN
 
  /* Initialize the object type. */
  lv_item_object := item_object(pv_id, pv_name);  
 
  /* Return the dat type. */
  RETURN lv_item_object;
END;
/

SET SERVEROUTPUT ON SIZE UNLIMITED
BEGIN
  IF drop_dependents('ITEM_OBJECT') = 1 THEN
    dbms_output.put_line('Objects dropped.');
  END IF;
END;
/