SELECT INTO without exception handling

Always wrap SELECT INTO in BEGIN/EXCEPTION block

SELECT INTO must return exactly one row. If no rows, raises NO_DATA_FOUND. If multiple rows, raises TOO_MANY_ROWS. Without exception handling, program crashes.

Example - No exception handling (WRONG):

DECLARE

v_customer_name VARCHAR2(100);

BEGIN

SELECT name INTO v_customer_name

FROM customers

WHERE customer_id = 999; -- No customer with ID 999

DBMS_OUTPUT.PUT_LINE('Customer: ' || v_customer_name);

END;

/

-- Error: ORA-01403: no data found

-- Program crashes, no output

-- CORRECT: With exception handling

DECLARE

v_customer_name VARCHAR2(100);

BEGIN

BEGIN

SELECT name INTO v_customer_name

FROM customers

WHERE customer_id = 999;

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_customer_name := 'Unknown Customer';

WHEN TOO_MANY_ROWS THEN

v_customer_name := 'Multiple Customers';

END;

DBMS_OUTPUT.PUT_LINE('Customer: ' || v_customer_name);

END;

/

-- Output: Customer: Unknown Customer

-- Program continues gracefully

Always wrap SELECT INTO in BEGIN/EXCEPTION block or use aggregate functions (MAX, MIN) that return NULL instead of raising NO_DATA_FOUND.

Critical

NO_DATA_FOUND or TOO_MANY_ROWS exception crashes program

Using cursors for simple data processing

Use bulk operations (FORALL, BULK COLLECT) or set-based SQL

Cursors process rows one-by-one, causing context switches between PL/SQL and SQL engines. Bulk operations process arrays in single operation.