Each choice contains a combination of DDL statements and PL/SQL blocks. A choice is correct if it does not contain any unnecessary code.

Code is unnecessary if you can remove it from the choice and the result of running that code remains the same. It is then incorrect.

Special rules for this quiz:

• You cannot add or change code - only remove.
• You may not remove part of an identifier (for example, remove "PLS_" from "PLS_INTEGER" and be left with "INTEGER"). Instead, entire identifiers and syntax elements must be removed.
• White space (space, new line, etc.) is not code.

The following block, for example, is incorrect:

BEGIN
   NULL;
   DBMS_OUTPUT.PUT_LINE (1);
END;

since the NULL statement can be removed without any impact whatsoever. The following block is correct, since if you remove the NULL; statement (or BEGIN or END), the block will no longer be valid.

BEGIN
   NULL;
END;

So which choice does not contain any unnecessary code?

Program units that contain unnecessary code (code that can be removed without changing the behavior of that program) are units inviting maintenance problems. You should always be careful to remove any code that is not needed.

Also consider using the compile-time warning to help you identify at least some of these occurrences. Here is an example of doing to to identify code that could be removed (or requiring some other change in the program unit):

ALTER SESSION SET plsql_warnings = 'enable:all'
/

CREATE OR REPLACE PROCEDURE plch_plw6006
   AUTHID DEFINER
AS
   l_var   NUMBER;

   PROCEDURE not_used
   IS
   BEGIN
      NULL;
   END not_used;
BEGIN
   DBMS_OUTPUT.put_line (l_var);
END plch_plw6006;
/

SHOW ERRORS PROCEDURE plch_plw6006

/* Commit after DDL? Unnecessary */

BEGIN
   EXECUTE IMMEDIATE 'create table plch_new_table (n number)';

   COMMIT;
END;
/

SELECT COUNT (*)
  FROM user_objects
 WHERE object_name = 'PLCH_NEW_TABLE'
/

/* Confirm */

DROP TABLE plch_new_table
/

BEGIN
   EXECUTE IMMEDIATE 'create table plch_new_table (n number)';
END;
/

SELECT COUNT (*)
  FROM user_objects
 WHERE object_name = 'PLCH_NEW_TABLE'
/

DROP TABLE plch_new_table
/

/* Get count from table - all code needed */

DECLARE
   l_count   PLS_INTEGER;
BEGIN
   SELECT COUNT (*) INTO l_count FROM all_objects;

   DBMS_OUTPUT.put_line (l_count);
END;
/

/* Close a CFL cursor? Unnecessary */

CREATE OR REPLACE PROCEDURE plch_proc
IS
BEGIN
   NULL;
END;
/

DECLARE
   CURSOR source_cur
   IS
      SELECT *
        FROM user_source
       WHERE name = 'PLCH_PROC' AND line = 1;
BEGIN
   FOR rec IN source_cur
   LOOP
      DBMS_OUTPUT.put_line (rec.line);
   END LOOP;

   IF source_cur%ISOPEN
   THEN
      CLOSE source_cur;
   END IF;
END;
/

/* Confirm */

DECLARE
   CURSOR source_cur
   IS
      SELECT *
        FROM user_source
       WHERE name = 'PLCH_PROC' AND line = 1;
BEGIN
   FOR rec IN source_cur
   LOOP
      DBMS_OUTPUT.put_line (rec.line);
   END LOOP;
END;
/

DROP PROCEDURE plch_proc
/

/* Execute DML - commit is needed. */

CREATE TABLE plch_tab (n NUMBER)
/

BEGIN
   EXECUTE IMMEDIATE 'insert into plch_tab VALUES (100)';

   COMMIT;
END;
/

ROLLBACK
/

SELECT COUNT (*) FROM plch_tab
/

/* Confirm */

TRUNCATE TABLE plch_tab
/


BEGIN
   EXECUTE IMMEDIATE 'insert into plch_tab VALUES (100)';
END;
/

ROLLBACK
/

SELECT COUNT (*) FROM plch_tab
/

DROP TABLE plch_tab
/