It's September 2012 and the PL/SQL Challenge development team is implementing Favorites for questions and topics. We've created tables for each of the different types of favorites:

CREATE TABLE plch_fav_questions
(
   fav_question_id   INTEGER,
   user_id           INTEGER,
   question_id       INTEGER
)
/

CREATE TABLE plch_fav_topics
(
   fav_topic_id   INTEGER,
   user_id        INTEGER,
   topic_id       INTEGER
)
/

The first column in each table is the primary key (its value is assigned in a BEFORE INSERT trigger). The second column is the user who is "liking" the question or topic. Third column is the "favorite ID" - the foreign key back to a row in the table of questions or topics.

I've created a single package to manage all the favorites, with a single procedure that correctly adds a favorite to either of those tables. Here's the specification of the package:

CREATE OR REPLACE PACKAGE plch_favorites_mgr
IS
   /* Favorites Types */

   c_fav_question   CONSTANT VARCHAR2 (100) := 'question';
   c_fav_topic      CONSTANT VARCHAR2 (100) := 'topic';

   PROCEDURE add_favorite (user_id_in         IN INTEGER,
                           favorite_id_in     IN INTEGER,
                           favorite_type_in   IN VARCHAR2);
END;
/

But now we've decided to also keep track of favorite Roundtable discussions, so I need to create a new table like this:

CREATE TABLE plch_fav_discussions
(
   fav_discussion_id   INTEGER,
   user_id             INTEGER,
   discussion_id       INTEGER
)
/

Which of the choices implement the body of plch_favorites_mgr, so that after I create plch_fav_discussions, I will be able to call plch_favorites_mgr.add_favorite to add a row to that table, without making any changes to the procedure?

Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables.

PL/SQL provides two ways to write dynamic SQL:

• Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements

• DBMS_SQL package, an API for building, running, and describing dynamic SQL statements

Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and data types of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL package.

Repetition in code is another form of hard-coding and something to be avoided whenever possible.

Repetition in code implies a pattern and there are generally two ways to resolve the pattern:

1. Write a reusable subprogram whose parameters accept all the variations in the pattern, and then implement the generalized pattern inside the subprogram.

2. Use dynamic SQL,  with EXECUTE IMMEDIATE and not DBMS_SQL if at all possible, to express the pattern as a statement constructed, and then bind values into the statement at runtime.

CREATE TABLE plch_fav_questions
(
   fav_question_id   INTEGER,
   user_id           INTEGER,
   question_id       INTEGER
)
/

CREATE TABLE plch_fav_topics
(
   fav_topic_id   INTEGER,
   user_id        INTEGER,
   topic_id       INTEGER
)
/

CREATE OR REPLACE PACKAGE plch_favorites_mgr
IS
   /* Favorites Types */

   c_fav_question   CONSTANT VARCHAR2 (100) := 'question';
   c_fav_topic      CONSTANT VARCHAR2 (100) := 'topic';

   PROCEDURE add_favorite (user_id_in         IN INTEGER,
                           favorite_id_in     IN INTEGER,
                           favorite_type_in   IN VARCHAR2);
END;
/

/* Full blown repetition with CASE */

CREATE OR REPLACE PACKAGE BODY plch_favorites_mgr
IS
   PROCEDURE add_favorite (user_id_in         IN INTEGER,
                           favorite_id_in     IN INTEGER,
                           favorite_type_in   IN VARCHAR2)
   IS
   BEGIN
      CASE favorite_type_in
         WHEN c_fav_question
         THEN
            INSERT INTO plch_fav_questions (user_id, question_id)
                 VALUES (user_id_in, favorite_id_in);
         WHEN c_fav_topic
         THEN
            INSERT INTO plch_fav_topics (user_id, topic_id)
                 VALUES (user_id_in, favorite_id_in);
      END CASE;
   END add_favorite;
END;
/

/* Now create another table and try to add a favorite */

CREATE TABLE plch_fav_discussions
(
   fav_discussion_id   INTEGER,
   user_id             INTEGER,
   discussion_id       INTEGER
)
/

/* ORA-06592: CASE not found while executing CASE statement */

BEGIN
   plch_favorites_mgr.add_favorite (100, 100, 'discussion');
END;
/

/* Shift to Dyn SQL but still constrained by hard codings
   in CASE statement

   ORA-00928: missing SELECT keyword
*/

CREATE OR REPLACE PACKAGE BODY plch_favorites_mgr
IS
   PROCEDURE add_favorite (user_id_in         IN INTEGER,
                           favorite_id_in     IN INTEGER,
                           favorite_type_in   IN VARCHAR2)
   IS
   BEGIN
      EXECUTE IMMEDIATE
            'INSERT INTO '
         || CASE favorite_type_in
               WHEN c_fav_question THEN 'plch_fav_questions'
               WHEN c_fav_topic THEN 'plch_fav_topics'
            END
         || ' (user_id, '
         || CASE favorite_type_in
               WHEN c_fav_question THEN 'question_id'
               WHEN c_fav_topic THEN 'topic_id'
            END
         || ') VALUES (:user_id, :favorite_id)'
         USING user_id_in, favorite_id_in;
   END add_favorite;
END;
/

BEGIN
   plch_favorites_mgr.add_favorite (100, 100, 'discussion');
END;
/

CREATE OR REPLACE PACKAGE BODY plch_favorites_mgr
IS
   PROCEDURE add_favorite (user_id_in         IN INTEGER,
                           favorite_id_in     IN INTEGER,
                           favorite_type_in   IN VARCHAR2)
   IS
   BEGIN
      EXECUTE IMMEDIATE
            'INSERT INTO plch_fav_'
         || favorite_type_in
         || 's (user_id, '
         || favorite_type_in
         || '_id) VALUES (:user_id, :favorite_id)'
         USING user_id_in, favorite_id_in;
   END add_favorite;
END;
/

BEGIN
   plch_favorites_mgr.add_favorite (100, 100, 'discussion');
END;
/

/* Concatenation instead of binding, OK */

CREATE OR REPLACE PACKAGE BODY plch_favorites_mgr
IS
   PROCEDURE add_favorite (user_id_in         IN INTEGER,
                           favorite_id_in     IN INTEGER,
                           favorite_type_in   IN VARCHAR2)
   IS
   BEGIN
      EXECUTE IMMEDIATE
            'INSERT INTO plch_fav_'
         || favorite_type_in
         || 's (user_id, '
         || favorite_type_in
         || '_id) VALUES ('
         || user_id_in
         || ','
         || favorite_id_in
         || ')';
   END add_favorite;
END;
/

BEGIN
   plch_favorites_mgr.add_favorite (100, 100, 'discussion');
END;
/

/* Should be two rows in table (a row per a correct choice) */

SELECT * FROM plch_fav_discussions
/

/* Clean up */

DROP TABLE plch_fav_topics
/

DROP TABLE plch_fav_questions
/

DROP TABLE plch_fav_discussions
/

DROP PACKAGE plch_favorites_mgr
/