Where did Steven spend New Year's Eve?

This month’s puzzler asks you to figure out the island on which I spent New Year's Eve. I know, I know...why should you care? Still...give it a try. And if you submit a solution, you just may get an email showing you what I did on New Year's Eve.

This puzzle uses something called a cursor variable, which is a variable that points to a result set (rows and columns). Since it is a variable you can pass it from one program to another, and even assign it to another cursor variable. Fun stuff!

I offer a sequence of DDL statements and PL/SQL blocks below. As with the previous puzzle, I urge you not to simple copy-and-paste the code, and then run it. You will see the results and from that you will still need to deduce the answer, but even so you will not have learned very much from the puzzle.

Instead, I recommend that you read the code. Figure out for yourself what it is going to do by logically analyzing the statements and what they do (I offer a little utility block after the main puzzle code that you might find useful in decoding the activity of the two functions, first_word and second_word). If you do this, you will become better at generally analyzing code, which will help you become a better debugger and code reviewer for others.

The Puzzle Code:

DROP TABLE names
/
CREATE TABLE names (
   NAME VARCHAR2(100)
)
/

BEGIN
   INSERT INTO names
        VALUES ('Robert');

   INSERT INTO names
        VALUES ('Theresa');

   INSERT INTO names
        VALUES ('Sally');

   INSERT INTO names
        VALUES ('Pedro');

   INSERT INTO names
        VALUES ('Christopher');

   INSERT INTO names
        VALUES ('Humberto');

   INSERT INTO names
        VALUES ('Orphelia');

/*
Next:
The name of a 1 year old girl, daughter of the owner
of the Lazy Parrot, a restaurant we visited on Dec 30.
Her mother came by the table to deliver flowers and
India (in her arms) immediately reached out her arms
to me. And her mom even let me hold her. I was overjoyed!
*/
   INSERT INTO names
        VALUES ('India');

   COMMIT;
END;
/

CREATE OR REPLACE FUNCTION first_word
   RETURN VARCHAR2
IS
   TYPE names_t IS TABLE OF names.NAME%TYPE;

   l_names    names_t;
   l_return   VARCHAR2 (4);
BEGIN
   SELECT   NAME
   BULK COLLECT INTO l_names
       FROM names
   ORDER BY CASE ASCII (SUBSTR (NAME, 1, 1))
               WHEN 82
                  THEN 1
               WHEN 73
                  THEN 2
               WHEN 67
                  THEN 3
               WHEN 72
                  THEN 4
               ELSE 5
            END;

   FOR indx IN 1 .. 4
   LOOP
      l_return := l_return || SUBSTR (l_names (indx), 1, 1);
   END LOOP;

   RETURN l_return;
END first_word;
/

CREATE OR REPLACE FUNCTION second_word
   RETURN VARCHAR2
IS
   cv1        sys_refcursor;
   cv2        sys_refcursor;
   l_name     names.NAME%TYPE;
   l_return   VARCHAR2 (4);
BEGIN
   OPEN cv1 FOR
      SELECT   NAME
          FROM names
      ORDER BY CASE ASCII (SUBSTR (NAME, 1, 1))
                  WHEN 80
                     THEN 1
                  WHEN 79
                     THEN 2
                  WHEN 82
                     THEN 3
                  WHEN 84
                     THEN 4
                  ELSE 5
               END;

   cv2 := cv1;

   LOOP
      FETCH cv1
       INTO l_name;
      EXIT WHEN cv1%ROWCOUNT = 5;
      l_return := l_return || SUBSTR (l_name, 1, 1);
   END LOOP;

   CLOSE cv1;

   LOOP
      FETCH cv2
       INTO l_name;

      EXIT WHEN cv2%NOTFOUND;
      l_return := l_return || l_name;
   END LOOP;

   RETURN 'NICE PLACE!';
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN l_return;
END second_word;
/

BEGIN
   DBMS_OUTPUT.put_line (first_word () || ' ' || second_word ());
END;
/

The utility block

Don't forget to "set serveroutput on"!

DECLARE
   l_characters   VARCHAR2 (26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
BEGIN
   FOR indx IN 1 .. 26
   LOOP
      DBMS_OUTPUT.put_line (   SUBSTR (l_characters, indx, 1)
                            || ' = '
                            || ASCII (SUBSTR (l_characters, indx, 1))
                           );
   END LOOP;
END display_ascii_codes;
/