O'Reilly logo

Oracle PL/SQL Programming, 5th 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.

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

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