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?