Packages and Dependent Objects
In my last post, I’ve challenged you with a quiz about package dependency. The question was about when a dependent object will be invalidated by Oracle when the package on which it depends is recreated.
The choices which give the „INVALID” status are:
- Choice 2
- Choice 6
Let me explain why those are the only choices that constitute the correct answer.
Choice 1 (Incorrect)
CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / CREATE OR REPLACE PROCEDURE print_message AS BEGIN dbms_output.put_line(dummy_pkg.g_message); END print_message; / CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / SELECT status FROM all_objects WHERE object_name = 'PRINT_MESSAGE'; STATUS ------- VALID
In this case, Oracle did not invalidate dependent print_message procedure, because the dummy_pkg has not changed, so there is no need to invalidate dependent objects.
Choice 2 (Correct)
CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / CREATE OR REPLACE PROCEDURE print_message AS BEGIN dbms_output.put_line(dummy_pkg.g_message); END print_message; / CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cookie?'; END dummy_pkg; / SELECT status FROM all_objects WHERE object_name = 'PRINT_MESSAGE'; STATUS ------- INVALID
This is the first correct choice – dependent procedure was invalidated, because the package has changed (turned out the „Cake” was actually a Cookie!)
Choice 3 (Incorrect)
ALTER SESSION SET PLSQL_CCFLAGS = ''; / CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / CREATE OR REPLACE PROCEDURE print_message AS BEGIN dbms_output.put_line(dummy_pkg.g_message); END print_message; / CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; $IF $$hide_cake = FALSE $THEN g_hidden_cake CONSTANT VARCHAR2(20) := 'Tasty!'; $END END dummy_pkg; / SELECT status FROM all_objects WHERE object_name = 'PRINT_MESSAGE'; STATUS ------- VALID
Here we use Oracle’s conditional compilation, added to Oracle 10g R1, to make new version of dummy_pkg conditionally contain another global variable. Whether the g_hidden_cake will actually be a part of the compiled package, or not, depends on the value of custom compiler flag named hide_cake. In the beginning of the code, I empty the compiler flags with ALTER SESSION statement. The flag doesn’t exist, so the following condition:
$IF $$hide_cake = FALSE $THEN
will no be true and the g_hidden_cake will not be a part of recreated package (well hidden cake!) The compiled package doesn’t change, and we get the ’VALID’ status. However, even if the condition was true, the returned status would still be ’VALID’. Why? Check explanation of the next choice.
Choice 4 (Incorrect)
ALTER SESSION SET PLSQL_CCFLAGS = ''; / CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / CREATE OR REPLACE PROCEDURE print_message AS BEGIN dbms_output.put_line(dummy_pkg.g_message); END print_message; / ALTER SESSION SET PLSQL_CCFLAGS = 'hide_cake:false'; / CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; $IF $$hide_cake = FALSE $THEN g_hidden_cake CONSTANT VARCHAR2(20) := 'Tasty!'; $END END dummy_pkg; / SELECT status FROM all_objects WHERE object_name = 'PRINT_MESSAGE'; STATUS ------- VALID
This is the case that is the most interesting in my opinion. Just before recreating the package, I set the hide_cake compiler flag to FALSE. This way, the g_hidden_cake global variable is from now on a part of the dummy_pkg.
Why, then, do we see the ’VALID’ status? Why wasn’t the dependent procedure invalidated?
The reason is that, if the changes made to a package are after previous declarations of variables, procedures, functions etc., the dependent objects will not be invalidated. This is interesting and may be of help when you need to add additional function to a package and you do not want to invalidate dependent objects.
Check Oracle documentation for more information about dependencies:
Choice 5 (Incorrect)
ALTER SESSION SET PLSQL_CCFLAGS = ''; / CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / CREATE OR REPLACE PROCEDURE print_message AS BEGIN dbms_output.put_line(dummy_pkg.g_message); END print_message; / CREATE OR REPLACE PACKAGE dummy_pkg AS $IF $$hide_cake = FALSE $THEN g_hidden_cake CONSTANT VARCHAR2(20) := 'Tasty!'; $END g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / SELECT status FROM all_objects WHERE object_name = 'PRINT_MESSAGE'; STATUS ------- VALID
OK, same as in choice 4, but we moved the g_hidden_cake before previous declaration, in this case, of the g_message variable. And we still see the ’VALID’ status? I can hear you screaming: „Make up your mind!” Well, as you’ll see in the next choice, this would invalidate the dependent procedure, but if the conditional compilation passed – in this case, I didn’t set the hide_cake flag to FALSE, so the g_hidden_cake will not be compiled as a part of the dummy_pkg – the package doesn’t change, and the print_message procedure does not get invalidated.
Choice 6 (Correct)
ALTER SESSION SET PLSQL_CCFLAGS = ''; / CREATE OR REPLACE PACKAGE dummy_pkg AS g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / CREATE OR REPLACE PROCEDURE print_message AS BEGIN dbms_output.put_line(dummy_pkg.g_message); END print_message; / ALTER SESSION SET PLSQL_CCFLAGS = 'hide_cake:false'; / CREATE OR REPLACE PACKAGE dummy_pkg AS $IF $$hide_cake = FALSE $THEN g_hidden_cake CONSTANT VARCHAR2(20) := 'Tasty!'; $END g_message CONSTANT VARCHAR2(20) := 'Cake?'; END dummy_pkg; / SELECT status FROM all_objects WHERE object_name = 'PRINT_MESSAGE'; STATUS ------- INVALID
This is the second, and last, correct answer. I set the hide_cake flag with the ALTER SESSION command, and the definition of g_hidden_cake is before the previous definition of g_message. Since the condition:
$IF $$hide_cake = FALSE $THEN
passes, the g_hidden_cake is now part of the dummy_pkg, and because it is before previously existing declarations, the dependent procedure print_message gets invalidated.
Further Reading & Useful Links
I hope you enjoyed the first of my quizzes. Maybe you got it right, maybe even you have learned something! Either way, you deserve a cake, or, at least, a cookie:
*
Learn more about dependency:
If you have found any errors in my post (even typos), you think that I haven’t explained anything clearly enough or you have an idea how I could make it better – please, do not hesitate to contact me, or leave a comment.