A H M A D G O H A R

Please Wait For Loading

Ahmad Gohar Featured Image 1886_826

Find PL/SQL Objects Referencing External Tables and Views

January 6, 2014 Ahmad Gohar 0 Comments

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

  1. all_dependencies:
    • This dictionary view provides information about dependencies between PL/SQL objects and database objects (tables, views, etc.).
  2. owner = USER:
    • Filters for objects created by the current user.
  3. TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION'):
    • Restricts the query to PL/SQL objects like packages, procedures, and functions.
  4. referenced_type IN ('TABLE', 'VIEW'):
    • Ensures the referenced objects are either tables or views.
  5. referenced_owner NOT IN ('SYS', 'SYSTEM'):
    • Excludes dependencies on system schemas.
  6. 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? 😊

author avatar
Ahmad Gohar
With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

Leave A Comment