FUNCTION my_salary (employee_id_in IN PLS_INTEGER) RETURN NUMBER IS l_salary NUMBER; BEGIN 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.
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: