Object Dependencies

Contact Us or call 1-877-932-8228
Object Dependencies

Object Dependencies

Code Sample:

Introducing-Subprograms/Demos/query_object_dependencies.sql
desc user_objects

desc public_dependency

SELECT child_obj.object_name,  child_obj.object_type,
       parent_obj.object_name || ' ' ||
            parent_obj.object_type as refers_to
FROM public_dependency p
     join user_objects child_obj
          on child_obj.object_id = p.object_id
     join user_objects parent_obj
          on parent_obj.object_id = p.referenced_object_id
where child_obj.object_name = 'EMP_DETAILS_VIEW';

Code Explanation

A database object is described as dependent upon another database object if it relies upon the existence and validity of the object in order to function. In the query above, the various tables that are defined as dependencies for the EMP_DETAILS_VIEW are returned by accessing the PUBLIC_DEPENDENCY data dictionary view.

When a subprogram is created, it is compiled and stored in the Oracle database. If there are compilation errors, the object will be created, but will not be functional until the errors are addressed. Although compilation errors are immediately available when an object is compiled, information on them is also stored in the database until the errors are fixed. The status of an object that contains compilation errors is INVALID.

Code Sample:

Introducing-Subprograms/Demos/query_user_errors.sql
select * from user_errors;

Code Explanation

Whenever you compile an Oracle object, you can issue the SHOW ERRORS command to list any compilation errors during the current connection session only. You can issue SHOW ERRORS PROCEDURE XXXX to list any compilation errors during the current connection session or at any time in the future. The USER_ERRORS view can also be queried to obtain this information.

Next