Find PL/SQL Objects Referencing External Tables and Views
Display All PL/SQL Code Objects Referencing External Tables/Views
When managing Oracle PL/SQL applications, it’s essential to track dependencies between your code and database objects. Here’s how you can list all PL/SQL code objects (e.g., packages, procedures, and functions) created by a user that reference tables or views owned by others—excluding system schemas.
SQL Query
Use the following SQL query to retrieve the desired dependencies:
SELECT
owner || '.' || NAME AS refs_table,
referenced_owner || '.' || referenced_name AS table_referenced
FROM all_dependencies
WHERE owner = USER
AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
AND referenced_type IN ('TABLE', 'VIEW')
AND referenced_owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, NAME, referenced_owner, referenced_name;
Explanation of Query Components
all_dependencies
:- This dictionary view provides information about dependencies between PL/SQL objects and database objects (tables, views, etc.).
owner = USER
:- Filters for objects created by the current user.
TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
:- Restricts the query to PL/SQL objects like packages, procedures, and functions.
referenced_type IN ('TABLE', 'VIEW')
:- Ensures the referenced objects are either tables or views.
referenced_owner NOT IN ('SYS', 'SYSTEM')
:- Excludes dependencies on system schemas.
ORDER BY
:- Organizes the results for readability.
Sample Output
Referenced Object | Table/View Referenced |
---|---|
OE.PACKAGE_ORDERS |
HR.EMPLOYEES |
OE.FUNCTION_SALES |
FINANCE.REVENUES |
Use Cases
- Dependency Analysis: Identify tables and views your PL/SQL code relies on.
- Maintenance: Track objects that may need changes when external objects are modified.
- Security: Ensure no unauthorized dependencies on sensitive schemas like
SYS
.
Conclusion
This query is invaluable for developers and DBAs who must manage and audit PL/SQL dependencies. By focusing on user-created objects and excluding system schemas, you can maintain cleaner, more secure applications.
Would you like to explore further automation for dependency management in Oracle? 😊