Looking for fun ways to test your knowledge of PL/SQL and other topics? Below are the Steven's Toad World Puzzlers, previously published in Toad World's monthly newsletter.

May's Puzzler:  Do you know parameters lists?
 
First, let's discuss and clarify some terminology. When I define a subprogram with a parameter list, each element in that list is referred to as a formal parameter. When I call that subprogram and pass it values or variables, each element in that list is called an actual argument.

PROCEDURE business_as_usual(
  advertising_budget_in   IN      NUMBER
 ,contributions_inout     IN OUT  NUMBER
 ,merge_and_purge_on_in   IN      DATE DEFAULT SYSDATE
 ,obscene_ceo_bonus_out   OUT     NUMBER
 ,cut_corners_in          IN      VARCHAR2 DEFAULT 'WHENEVER POSSIBLE'
)

1. 5
2. 4
3. 3
4. 2

Previous Puzzlers

• April 2008: Do you know NOCOPY?
• March 2008: Do you know your built-in functions?
• February 2008: PL/SQL Knowledge Quiz
• January 2008: PL/SQL Knowledge Quiz
• December 2007: PL/SQL Knowledge Quiz
• November 2007: Be Selective with your SELECTs!
• October 2007: Error codes can be very puzzling
• September 2007: How to save $1 billion in expenses
• August 2007: The right Datatype for the job
• July 2007: The mysteries of CASE in PL/SQL
• May 2007: How many countries has Steven visited?
• March 2007: Resolving a NO_DATA_FOUND Exception
• February 2007: Writing Deterministic Programs
• January 2007: Where did Steven spend New Year's Eve?
• December 2006: What is Steven's birthday?
• November 2006: The Lilly Pad Puzzler    

Do you know NOCOPY?

Do you know your built-in functions?

PL/SQL Knowledge Quiz  

a.

DECLARE
   CURSOR employees_cur IS
      SELECT * FROM employees;
BEGIN
   FOR employee_rec IN employees_cur
   LOOP
      do_stuff (employee_rec);
   END LOOP;
END;

b.

DECLARE
   CURSOR employees_cur IS
      SELECT * FROM employees;
   l_employee   employees%ROWTYPE;
BEGIN
   OPEN employees_cur;
   LOOP
      FETCH employees_cur INTO l_employee;
      EXIT WHEN employees_cur%NOTFOUND;
      do_stuff (l_employee);
   END LOOP;
   CLOSE employees_cur;
END;

c.

DECLARE
   CURSOR employees_cur IS
      SELECT * FROM employees;

   TYPE employees_aat IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER; 

   l_employees   employees_aat;
BEGIN
   OPEN employees_cur;
   LOOP
     FETCH employees_cur
      BULK COLLECT INTO l_employees LIMIT 100; 

      EXIT WHEN l_employees.COUNT () = 0; 

      FOR indx IN 1 .. l_employees.COUNT
      LOOP
         do_stuff (l_employees (indx));
      END LOOP;
   END LOOP;
   CLOSE employees_cur;
END;

ANSWER

How to save $1 Billion in expenses

Several years ago, Oracle made a big splash in the media with an announcement that they had saved $1B in expenses by fully utilizing their own web-based applications. A secret memo leaked to me last week shows, however, that their cost-cutting measures were much more far-reaching. Here is what the memo said:

The PL/SQL Feature Cost Evaluator (known commonly as the Piffkey) has determined that it costs Oracle Corporation $221 to declare a new exception in the PL/SQL language. Therefore, every effort should be made to reuse previously-defined exceptions whenever possible. Please notify all members of the PL/SQL Development Team to "Think Before Adding" or TBA, and we can all contribute to Larry's mighty vision of a $1B savings this year!

Well, the team must have taken that request to heart, because they reuse the exception NO_DATA_FOUND in a number of circumstances.

So, the Toad World quiz for this month is: Which of the following circumstances does not raise a NO_DATA_FOUND exception?

a. A SELECT INTO statement that finds no rows

b. A numeric FOR loop whose low value or high value is NULL

c. An attempt to get the value of an element in a collection whose index is not defined

d. Reading past the end of a file with UTL_FILE

e. Reading past the end of a BFILE with DBMS_LOB

ANSWER

The Right Datatype for the Job

PL/SQL comes with lots of different types of data: strings, numbers, dates, collections, cursor variables, records, etc.

This month's puzzle is a matching game. Assuming you have an Oracle Database 10g Release 2 installation, match the datatypes listed below to the scenarios to which they would best be applied.

Datatypes

1. Timestamp
2. String indexed collection
3. Nested table of integers
4. Weak ref cursor
5. Boolean

Scenarios

1. I work at Toys Galore! and I used BULK COLLECT to query all the toys in the "BOARD GAMES" category into a collection of records. I need to apply a formula to transform the internal product identifier codes (alphanumeric) and then display the products ordered by these new codes.
2. We are using an Oracle database to keep track of readings from a laboratory instrument. Data is generated every two-hundredth’s of a second and I need to record the time of acquisition.
3. Either the customer status is active or inactive; in the database, this information is stored as a Y or N value in a CHAR(1) column. When I manipulate account information in my PL/SQL program, I will use this datatype instead to track the status.
4. We celebrate the birthdays of employees in our company. I am writing a program to print out the days of month on which there are birthdays. I have two lists defined in my PL/SQL procedure: the days of the month of salaried and hourly workers. I need to merge them together into a single list that contains no duplicates, and then display the days of the month.
5. Sometimes I need to fetch from the department table, sometimes from the employees table, and in all cases I need to pass the dataset back to the Java frontend applet.

ANSWER

Did you know that PL/SQL supports CASE? This feature was introduced in Oracle9i database in two forms: the CASE expression and the CASE statement. CASE offers a nice alternative to the good, old IF statement, but there is at least one less-than-obvious aspect of PL/SQL's CASE.

First of all, there are two variations of CASE in PL/SQL: the CASE expression and CASE statement. The CASE expression (true to its namesake) is an expression that fits inside a PL/SQL statement. The CASE statement is a standalone executable statement.

I use CASE below in the fruit_translator function ("A" is for "Apple", "B" is for "Banana"). In the first version, I use a CASE expression, and in the second I use a CASE statement.

So here is the quiz: what will I see when I display the value returned by each function when I pass in a value of "C"?

CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2
IS
   retval   VARCHAR2 (100);
BEGIN
   RETURN CASE
      WHEN letter_in = 'A' THEN 'Apple'
      WHEN letter_in = 'B' THEN 'Banana'
   END;
END fruit_translator;
/

SET SERVEROUTPUT ON

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Expression good for you = ' 
           || NVL (fruit_translator ('C'), 'Unknown Fruit'));
END;
/

CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2
IS
   retval VARCHAR2(100);
BEGIN   
   CASE
      WHEN letter_in = 'A' THEN retval := 'Apple';
      WHEN letter_in = 'B' THEN retval := 'Banana';
   END CASE;
   RETURN retval;
END fruit_translator;
/

SET SERVEROUTPUT ON

BEGIN
   DBMS_OUTPUT.PUT_LINE ('Statement good for you = ' 
           || NVL (fruit_translator ('C'), 'Unknown Fruit'));
END;
/

ANSWER

Home Many Countries Has Steven Visited?

I write this puzzler from Buenos Aires, Argentina (actually, I am in an airplane about to leave for Lima, Peru, and then Bogota, Columbia). And so, with this trip, I have added two more countries to the list of nations and commonwealths I have visited during my relatively short life. I recently decided to create a web page that will help keep track of all those places; it is currently under construction. In the meantime, however, I did tally up the total number of countries where my footsteps can be found.
So this is the puzzle for you: sum up the answers to the clues below and tell us how many countries I have visited (actually, will have visited by September 2007 – that is, currently in my confirmed itinerary).

ANSWER 

Resolving a NO_DATA_FOUND Exception

Sometimes Oracle does things in such an elegant and thoughtful manner, that it takes my breath away. And then on occasion I look at the way it did something and scratch my head, thinking: “Why in the heck did it do it that way?”

This puzzler is more of the head-scratching kind – and it ties in very nicely with the increasing concerns about global warming. One way to reduce the negative impact of human industrial society on our planet is to reuse or recycle stuff that already exists and can be repurposed. This is a very sensible best practice for living in the real world and, of course, a critical best practice to avoid reinventing the wheel in software.

But re-use is not always a good thing. To make my point in a fairly obvious way, I could say to myself: why declare lots of different variables when I could simply analyze my program requirements, recognize that I need, oh, no more than three string variables at any time and no more than two integer variables simultaneously? And then declare my variables as follows:

DECLARE   
l_string1 VARCHAR2(32767);   
l_string2 VARCHAR2(32767);   
l_string3 VARCHAR2(32767);   
l_integer1 INTEGER;   
l_integer2 INTEGER;
BEGIN    
....

and simply re-use those variables in all the parts of my block.

Silly, right? You end up with code that is hard to read and understand.

Well, Oracle did something similar to this with the NO_DATA_FOUND exception. So the puzzler for this month is: Assume that I have created a database directory object named TEMP and can use UTL_FILE to read/’write files in that directory. I turn on server output and run the code below:

I will see a single line of output that looks like this

NO_DATA_FOUND raised this many times: NNN

As with my previous puzzlers, you could simply run the code and see the result.  But if you would like to deepen your knowledge of PL/SQL and improve your code reviewing skills, please do not run the code. Analyze it in your head, instead!

The code:

DROP TABLE some_data
/

CREATE TABLE some_data (
   a_name VARCHAR2(100)
)
/

DECLARE
   fid   UTL_FILE.file_type;

BEGIN
   INSERT INTO some_data
        VALUES ('Rhino');
   COMMIT;

   fid := UTL_FILE.fopen ('TEMP', 'some_data.txt', 'W');
   UTL_FILE.put_line (fid, 'Rhino');
   UTL_FILE.fclose (fid);
END;
/

DECLARE
   l_name          some_data.a_name%TYPE;
   l_action        some_data.a_name%TYPE;
   l_line          VARCHAR2 (1023);
   l_sum           PLS_INTEGER;
   fid             UTL_FILE.file_type;
   list_of_names   DBMS_SQL.varchar2s;

   TYPE ndf_for_aat IS TABLE OF some_data.a_name%TYPE
      INDEX BY some_data.a_name%TYPE;
   l_ndf_for       ndf_for_aat;

BEGIN
   FOR indx IN 1 .. 100

   LOOP
      BEGIN
         CASE
            WHEN NOT l_ndf_for.EXISTS ('SELECT')
            THEN
               l_action := 'SELECT'; 

               SELECT a_name
                 INTO l_name
                 FROM some_data
                WHERE a_name = 'Hippo';
               l_ndf_for (l_action) := 'Nope!';
            WHEN NOT l_ndf_for.EXISTS ('WRITEFILE')
            THEN
               l_action := 'WRITEFILE';
               fid := UTL_FILE.fopen ('TEMP', 'some_data2.txt', 'W');
               l_ndf_for (l_action) := 'Nope!';
               UTL_FILE.fclose (fid);
            WHEN NOT l_ndf_for.EXISTS ('READFILE')
            THEN
               l_action := 'READFILE';
               fid := UTL_FILE.fopen ('TEMP', 'some_data2.txt', 'R');
               UTL_FILE.get_line (fid, l_line);
               UTL_FILE.get_line (fid, l_line);
               l_ndf_for (l_action) := 'Nope!';
               UTL_FILE.fclose (fid);
            WHEN NOT l_ndf_for.EXISTS ('SELECTBULK')
            THEN
               l_action := 'SELECTBULK';
               SELECT a_name
               BULK COLLECT INTO list_of_names
                 FROM some_data
                WHERE a_name = 'Hippo';
               l_ndf_for (l_action) := 'Nope!';
            WHEN NOT l_ndf_for.EXISTS ('READARRAY')
            THEN
               l_action := 'READARRAY';
               IF list_of_names (100) > 0
               THEN
                  DBMS_OUTPUT.put_line ('Positive value at row 100');
               END IF;
               l_ndf_for (l_action) := 'Nope!';
            WHEN NOT l_ndf_for.EXISTS ('SELECTGROUP')
            THEN
               l_action := 'SELECTGROUP';
               SELECT SUM (LENGTH (a_name))
                 INTO l_sum
                 FROM some_data
                WHERE a_name = 'Hippo';
               l_ndf_for (l_action) := 'Nope!';
            ELSE
               NULL;
         END CASE;

      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_ndf_for (l_action) := 'Yep!';
            IF UTL_FILE.is_open (fid)
            THEN
               UTL_FILE.fclose (fid);
            END IF;
      END;
   END LOOP;

   l_sum := 0;
   l_name := l_ndf_for.FIRST;
   WHILE (l_name IS NOT NULL)

   LOOP
      IF l_ndf_for (l_name) = 'Yep!'
      THEN
         l_sum := l_sum 1;
      END IF;
      l_name := l_ndf_for.NEXT (l_name);
   END LOOP;

   DBMS_OUTPUT.put_line ('NO_DATA_FOUND raised this many times: ' || l_sum);

END;
/

ANSWER

Writing Deterministic Programs

How determined are you to become a PL/SQL expert? Very determined? Excellent! Then consider the following function (a variation on SUBSTR that returns the substring between two locations - rather then substring that starts at a location and extends for N characters):

CREATE OR REPLACE FUNCTION betwnstr (
   string_in IN VARCHAR2
 , start_in IN INTEGER
 , end_in IN INTEGER
)
   RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;

Notice that I have labeled it "deterministic". Rather odd, eh? OK, first part of this puzzle is multiple choice.

What does it mean for a program to be deterministic?

1. It is impossible to determine what this program will do when it is run outside of the Oracle environment.
2. This program is determined to run, no matter what input values are provided.
3. The outcome of running the function (that is, the value returned) is determined completely and solely by the inputs to the function. In other words, pass in the same values for string_in, start_in and end_in, and the function will always return the same value.

If you guessed (c), you are right! Another way of putting this is that the program has no side effects, no references to global variables, no dependencies on database tables. If it doesn't appear in the parameter list, it's not a factor in the program's execution.

Deterministic functions are very special creatures in the world of Oracle, and you must figure out what's special about them by analyzing the following code and coming up with the right answer.

OK, here is a modified version of betwnstr:

CREATE OR REPLACE FUNCTION betwnstr (
   string_in IN VARCHAR2
 , start_in IN INTEGER
 , end_in IN INTEGER
)
   RETURN VARCHAR2 DETERMINISTIC
IS
BEGIN
   DBMS_LOCK.sleep (.05);
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;
/

I have inserted a call to DBMS_LOCK.sleep, a program that can be used to pause a PL/SQL program by the specified number of seconds. In this case, I stop betwnstr for .05 seconds each time it is called.

Note: the default installation of Oracle does not grant EXECUTE on DBMS_LOCK to non-DBA accounts.

I then execute two blocks of code, each of which query the first 100 rows of employees and uses betwnstr to retrieve the first five characters of "FEUERSTEIN" for each row.

SET SERVEROUTPUT ON FORMAT WRAPPED

DECLARE /* BLOCK A */
   l_start   PLS_INTEGER;
BEGIN
   l_start := DBMS_UTILITY.get_time;

   FOR rec IN (SELECT betwnstr ('FEUERSTEIN', 1, 5)
                 FROM employees
                WHERE ROWNUM < 101)
   LOOP
      NULL;
   END LOOP;

   DBMS_OUTPUT.put_line (   'BLOCK A elapsed: '
                         || TO_CHAR (DBMS_UTILITY.get_time - l_start)
                        );
END;
/

DECLARE /* BLOCK B */
   l_string   VARCHAR2 (100);
   l_start    PLS_INTEGER;
BEGIN
   l_start := DBMS_UTILITY.get_time;

   FOR rec IN (SELECT *
                 FROM employees
                WHERE ROWNUM < 101)
   LOOP
      l_string := betwnstr ('FEUERSTEIN', 1, 5);
   END LOOP;

   DBMS_OUTPUT.put_line (   'BLOCK B elapsed: '
                         || TO_CHAR (DBMS_UTILITY.get_time - l_start)
                        );
END;
/

After running the script, I will see two lines of output:

BLOCK A elapsed: 
BLOCK B elapsed: 

And finally the puzzle question(s):  Will these two blocks run in about the same amount of time?   If not, what is the minimum time the slower block will run?

ANSWER

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;
/

ANSWER

What is Steven's Birthday?

Why should you care? You shouldn’t, except that if you can solve the puzzle below, which means you must analyze some code to figure out the day of the year on which I celebrate my birthday, then maybe, just maybe, you can win one of my books!

[Warning: this is a bit more challenging than previous puzzles, and you will probably want to take a peak at Oracle documentation, which is always available at http://tahiti.oracle.com.]

Here goes....p