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 and print_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';