Packages and Dependent Objects
I’ve got the following quiz for you today:
After running which of the following code snippets will the ‘INVALID’ status be returned?
Assumptions:
- Version of Oracle: 11g R2.
- Before each snippet is run, both
dummy_pkg
andprint_message
objects do not exist.
I’ll provide the answer in my next post.
The choices:
Choice 1
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';
Choice 2
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';
Choice 3
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';
Choice 4
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';
Choice 5
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';
Choice 6
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';