You are debugging a PL/SQL block where you suspect a SELECT INTO statement might return more than one row, but your code is not designed to handle this. You want to add exception handling to catch this scenario and display a warning message specifically. Which of the following EXCEPTION handlers is appropriate for this purpose?

Question

BlackTom AI · Accepted Answer

TOO_MANY_ROWS is the PL/SQL predefined exception raised when a SELECT ... INTO statement returns more than one row (Oracle error ORA-01422). SELECT ... INTO expects exactly one row; if zero rows are returned PL/SQL raises NO_DATA_FOUND, and if more than one row is returned it raises TOO_MANY_ROWS.

Relevant knowledge points and concepts
- SELECT ... INTO: used to fetch column values into variables; must return a single row.
- Predefined exceptions: NO_DATA_FOUND (no rows), TOO_MANY_ROWS (more than one row).
- EXCEPTION section: you can write WHEN <exception> THEN handlers to catch and handle specific runtime errors.
- DUP_VAL_ON_INDEX is raised for unique/index violations on DML, not for SELECT INTO.
- WHEN OTHERS is a catch-all but is not specific and does not identify the TOO_MANY_ROWS condition by name.

Step-by-step reasoning
1. Run the SELECT ... INTO statement. Oracle attempts to fetch exactly one row.  
2. If the query returns two or more rows, Oracle raises ORA-01422 and PL/SQL maps this to the predefined exception TOO_MANY_ROWS.  
3. In the EXCEPTION section, a WHEN TOO_MANY_ROWS THEN handler will be selected and executed. You can place a specific warning there, for example:
   WHEN TOO_MANY_ROWS THEN
     DBMS_OUTPUT.PUT_LINE('Warning: More than one row returned by SELECT INTO. Check query logic.');
4. This produces a clear, targeted warning and allows debugging or corrective action.

Why the correct answer is correct
- The explicit WHEN TOO_MANY_ROWS handler directly corresponds to the runtime condition described (SELECT INTO returning >1 row). It cleanly captures the ORA-01422 situation and allows a specific message.

Why the other options are incorrect
- WHEN NO_DATA_FOUND handles zero-row results, not multiple rows.  
- WHEN OTHERS is generic; it would catch the error but is not specific and prevents distinguishing causes easily.  
- WHEN DUP_VAL_ON_INDEX is for unique/index violation during inserts/updates, unrelated to SELECT INTO.

Conclusion: The appropriate EXCEPTION handler is WHEN TOO_MANY_ROWS, matching the provided answer.

类似问题

Consider the following PL/SQL procedure designed to retrieve customer information and handle potential errors: If get_customer_info(0) is executed, what will be the output?

According to Xenophanes, what should be avoided at a symposium?

What is "kottabos"?

Why are there so many naked figures in vase iconography?

Why was it necessary to have a "hydria" as part of the ceramic vessels used at a symposium?

For what uses other than sympotic were Greek ceramic vessels used?

Question textFill in the gap with ONE word from the text. Research can help to reduce the amount of Answer 1 Question 6[input] between fauna and humans.

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单