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? ...

Get Oracle PL/SQL Programming, 5th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial