Earlier,
dependency information was recorded only with the granularity of the
whole object. In the example in Code_6, it would be recorded just that the
current PL/SQL unit depends on the table t.
Now, in Oracle Database 11g, it is
recorded that the PL/SQL unit depends on the columns t.v and t.PK within
table t. The new approach aims to reduce unnecessary invalidation by avoiding it
when a referenced object is changed in a way which is immaterial for the
dependant. In this example, the beginner might think at first that the addition of
a new column to t, when the dependant PL/SQL unit refers to only certain
named columns in t, would be immaterial. But the discussion of name capture
shows that this is not always the case: the name of the new column might collide
with what used to be an escaping identifier that was resolved in PL/SQL scope.
The only way to guarantee that the PL/SQL has the correct meaning in the
regime of the altered table is to invalidate it in response to the addition of the
new column so that it will be recompiled and the name resolution will be done
afresh.
-- Code_6 Fine_Grained_Dependency_Test.sql
1 2 3
| select v1
into l_v1
from t where PK = p_PK; |
Doing SQL from PL/SQL: Best and Worst Practices page 9
21-September-2008
http://www.oracle.com/technology/tec...from_plsql.pdf
The use of qualified names, as used in Code_3, changes the analysis. The qualified
identifier b.PK, cannot possibly mean a column (existing or new) in the table
whose alias in the query is a. This is easily confirmed by experiment. Create table
t with columns PK and v, procedure p1 containing Code_3 and procedure p2
containing Code_6. Confirm, with a User_Objects query, that both are valid. Then
alter t to add a column (for example c1 of datatype number) and repeat the
User_Objects query; p1 remains valid but p2 becomes invalid13.
Partager