O'Reilly logo

Oracle PL/SQL Programming, Third Edition by Bill Pribyl, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required