Normalization Part -2 /Dependency in Oracle SQL - Day 56
In Oracle SQL, a dependency refers to the relationship between database objects that affects their behavior or validity when changes are made to one or more objects. When one database object depends on another, it means that any alteration or modification to the referenced object can impact the dependent object's functionality or validity.
There are two primary types of dependencies in Oracle SQL:
1. **Object Dependency**: This type of dependency occurs when one database object relies on another object to function correctly. The most common types of object dependencies include:
**Table Dependencies**: When a view, trigger, or stored procedure references a table, there is a dependency between them. If the table structure or column names change, the dependent objects may become invalid.
**View Dependencies**: Views are dependent on the underlying tables or other views they are based on. If any referenced object changes, the view may need to be recompiled to maintain accuracy.
**Procedure and Function Dependencies**: Stored procedures and functions can depend on other objects such as tables, views, or even other procedures and functions. If referenced objects change, the dependent routines may need recompilation.
**Package Dependencies**: Packages can contain multiple procedures, functions, variables, and cursors. The components within a package can have dependencies on each other.
**Trigger Dependencies**: Triggers are dependent on the tables or views they are designed to respond to. Changes in the referenced objects can affect trigger functionality.
2. **Code Dependency**: This type of dependency occurs when the source code of one object directly references the name of another object, creating a code-level connection between them. Code dependencies are particularly relevant in dynamic SQL scenarios where objects are referred to by name in SQL statements constructed at runtime.
For example, if a stored procedure dynamically constructs an SQL query using a table name provided as a parameter, a code dependency exists between the procedure and the referenced table.
It's important to manage and understand dependencies in an Oracle database, especially during schema changes or upgrades. The database automatically tracks dependencies between objects so that when a referenced object is altered, the dependent objects can be invalidated or automatically recompiled to maintain data integrity and consistency. To find dependencies in Oracle SQL, you can use various data dictionary views, such as `DBA_DEPENDENCIES`, `ALL_DEPENDENCIES`, or `USER_DEPENDENCIES`, depending on your level of access and scope requirements.