17 July 2010

Questions raised about 16 July quiz and primary key (503)

In the 16 July quiz, we wrote: The employees table (with columns named employee_id and salary, both of type NUMBER) is defined in the same schema as this function:
FUNCTION my_salary (employee_id_in IN PLS_INTEGER)
  l_salary   NUMBER;
  SELECT e.salary
    INTO l_salary
    FROM employees e
   WHERE e.employee_id = my_salary.employee_id_in;

  RETURN l_salary;
END my_salary;
Assume that the database instance is working properly (it is not in  process of shutdown, out of storage or memory, etc.). The only variation in behavior of the  function can be caused by the value of the employee_id_in argument. Which of the following exceptions could be propagated  unhandled out of this function?

A number of players raised a concern regarding the primary key on the employees table. Namely, if the employee_id_in column is the primary key of the table (and it is enabled), then TOO_MANY_ROWS would not be raised out of the function. Yet that choice was scored as a correct answer.

One player wrote: "It [lack of mention of the primary key] might confuse the players who know that Emplyee_ID is a PK in the SCOTT  schema."

Another wrote: "Is there a primary key or unique index on employees.employee_id? You must  provide enough information for challengers to answer the question."

And another: "We can assume that employe_id column is primary key in this case rite, since it  is not specified?"

Finally, on a different topic, a player wrote: " In the question you asked there was no CREATE OR REPLACE in the Function."

Here is my response to these comments:

1. The assumptions for taking the quiz include this: 

The session and the environment in which the quiz code executes has  enabled output from DBMS_OUTPUT, and can reference only those datatypes,  programs and database objects defined in the context of the quiz or are  available in a default installation of the Oracle instance.

Given this, you clearly should not assume that there is any primary or foreign key on this table, since I do not specify it in the question content. TOO_MANY_ROWS was a correct choice for this quiz.

Having said that, I will try to avoid in the future referencing tables for which a user might have a preconceived notion. That should minimize the chance of confusion.

2. Regarding the lack of CREATE OR REPLACE. I assume the player is implying that therefore the function is not compiled into the database and so the all answers will be wrong. I disagree. I stated that the function "is defined in the schema," so clearly it was compiled. "CREATE OR REPLACE" is not a part of the function definition. It is part of the DDL statement that creates the function.


  1. Just a heads-up to everyone: I am going to change the text of this quiz to NOT use employees, and to include the DDL, to remove this ambiguity. I need to make sure that the question/answer content is as good as it can be. Thanks for your feedback!


  2. The clase to the right of the equal sign is not valid because is the name of the function dot the parameter.
    I'm trying similar construction on Oracle 10g and failed the compilation.

  3. There is nothing wrong with the expression to the right of the equal sign in the select statement (verified using Oracle XE). In fact, using the function name to qualify the a variable reference within a SQL statement is a highly recommended practice because it avoids potential name collisions with table columns.

  4. Hi SF
    I didn't get a response to my question regarding the execution method of this function on this blog. When the functioned is called from SQL Statement like : SELECT my_salary(0) FROM DUAL;
    It returns a record with NULL value and not the NO_DATA_FOUND error.

  5. Arun, thanks for pointing this out. You are right, the Oracle SQL engine "swallows" up that error and just treats is as NULL. I don't think that changes the question and answers, though. After all, NO_DATA_FOUND could be raised and propagated out of the function. It just isn't in that one circumstance.