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