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.

The 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 21-4 lists a number of helpful queries you can use.

Table 21-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 ...

Get Oracle PL/SQL Programming, Third Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.