Oracle PL/SQL Programming, 5th Edition

Maintaining Object Types and Object Views

If you work much with object types, you will learn a number of ways to get information about the types and views that you have created. Once you reach the limits of the SQL*Plus DESCRIBE command, this could involve direct queries from the Oracle data dictionary.

Data Dictionary

The data dictionary term for user-defined types (objects and collections) is simply TYPE. Object type definitions and object type bodies are both found in the USER_SOURCE view (or DBA_SOURCE, or ALL_SOURCE), just as package specifications and bodies are. Table 26-4 lists a number of helpful queries you can use.

Table 26-4. Data dictionary entries for object types

To answer the question...	Use a query such as
What object and collection types have I created?	SELECT * FROM user_types; SELECT * FROM user_objects WHERE object_type = 'TYPE';
What do my object type hierarchies look like?	SELECT RPAD(' ', 3*(LEVEL-1)) || type_name FROM user_types WHERE typecode = 'OBJECT' CONNECT BY PRIOR type_name = supertype_name;
What are the attributes of type foo?	SELECT * FROM user_type_attrs WHERE type_name = 'FOO';
What are the methods of type foo?	SELECT * FROM user_type_methods WHERE type_name = 'FOO';
What are the parameters of foo’s methods?	SELECT * FROM user_method_params WHERE type_name = 'FOO';
What datatype is returned by foo’s method called bar?	SELECT * FROM user_method_results WHERE type_name = 'FOO' AND method_name = 'BAR';
What is the source code for foo, including all ALTER statements? ...