Header Ads Widget

Responsive Advertisement

Module 12: Managing Dependencies


Managing Dependencies

01. What is local dependency?

In the case of local dependencies, the objects are on the same node in the same database. The

Oracle server automatically manages all local dependencies, using the databases internal

“depends-on” table. When a referenced object is modified, the dependent objects are sometimes invalidated. The next time an invalidated object is called, the Oracle server automatically recompiles it.

02. What is Remote Dependencies?

In the case of remote dependencies, the objects are on separate nodes. The Oracle server does not manage dependencies among remote schema objects other than local-procedure-to-remote procedure dependencies (including functions, packages, and triggers). The local stored procedure and all its dependent objects are invalidated but do not automatically recompile when called for the first time.

03. What is Recompilation? How work it? 


a. Is handled automatically through implicit run-time recompilation

b. Is handled through explicit recompilation with the ALTER statement

Work it:

If the recompilation is successful, the object becomes valid. If not, the Oracle server returns an error and the object remains invalid. When you recompile a PL/SQL object, the Oracle server first recompiles any invalid object on which it depends.

04. When Recompilation is unsuccessful?

Unsuccessful when:

a. The referenced object is dropped or renamed.

b. The data type of the referenced column is changed.

c. The referenced column is dropped.

d. A referenced view is replaced by a view with different columns.

e. The parameter list of a referenced procedure is modified Unsuccessful.

05. When Recompilation is successful?


a. The referenced table has new columns.

b. The data type of referenced columns has not changed.

c. A private table is dropped, but a public table that has the same name and structure exists.

d. The PL/SQL body of a referenced procedure has been modified and recompiled successfully.

06. How you can minimize dependency failure?

Minimize dependency failures by:

a. Declaring records with the %ROWTYPE attribute.

b. Declaring variables with the %TYPE attribute.

c. Querying with the SELECT * notation.

d. Including a column list with INSERT statements. 

Post a Comment