Should I use the Too Many Rows Error as my exception clause in my Oracle Update statement?

Question

I have a series of update statements that I need to use in my Oracle package. It's rare but there may be an occasional and unavoidable user error that would result in one of the update statements throwing a "Single row sub-query returns one or more rows" Error.

I've been looking into exception handling for oracle PL/SQl and I'm a bit stuck on how and what to use to catch this exception so the package doesn't crash.

I know of the pre-built "Too Many Rows" exception clause that exists but everything I read seems to say it is used for improper insert statements.

Can I use this as my exception? Or do I need to build my own exception clause. I've never built one myself before and have only a rough idea on where to put everything needed for it.

The following code is basically how the updates are set up in this particular procedure but for the sake of brevity I'm only using a bare bones example of how it looks.

INSERT INTO TempTable... --(Initial insert statement)

UPDATE TempTable t SET t.Row_one = (SELECT (Statement_One))
WHERE T.Row_One is NULL

UPDATE TempTable t SET t.Row_one = (SELECT (Statement_Two))
WHERE T.Row_One is NULL

UPDATE TempTable t SET t.Row_one = (SELECT (Statement_Three))
WHERE T.Row_One is NULL

-- Does the exception clause start here?
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
(What do I tell the Procedure to do here, what am I able to tell it to do?)

--end of updates that need the exception handling

-- more insert statements into other tables based on data from the preceding Temp Table

END;

Will this work or do I need to build a custom exception?

Thanks in advance.

Answer 1

First, the TOO_MANY_ROWS exception will not catch the case where your select statements return multiple rows. The TOO_MANY_ROWS exception is for ORA-01422 when you issue a SELECT .. INTO statement that returns more than one row. The exception you'll encounter in your case is ORA-01427, Single row subquery returns more than one row.

If you want to handle this specific error in your procedure, use the EXCEPTION_INIT pragma to associate an exception name with the error:

too_many_values EXCEPTION;
PRAGMA EXCEPTION_INIT(too_many_values, -1427);

Then you can reference this name in your exception handler:

EXCEPTION
    WHEN TOO_MANY_VALUES THEN
       {perform your handler here}

What you put in the handler depends on what your procedure does. Many times you'll want to return some sort of error code/message to your caller:

PROCEDURE my_proc(p_one VARCHAR2, p_err OUT VARCHAR2) IS
    too_many_values EXCEPTION;
    PRAGMA EXCEPTION_INIT(too_many_values, -1427);
BEGIN
...
EXCEPTION
   WHEN TOO_MANY_VALUES THEN
      p_err := 'More than one value available to assign in the update';
      RAISE;  -- re-raise the exception for the caller

   WHEN OTHERS THEN
      p_err := SQLERRM;  -- return the oracle message for the unexpected error
      RAISE;
END;

Another approach is to skip the specific exception handlers and return generic oracle messages in the WHEN OTHERS handler:

EXCEPTION
  WHEN OTHERS THEN
    p_err := SQLERRM;
END;

The advantage with the first approach is you can customize your messages to be more end-user friendly when the output from the process is fed directly back to the user. The advantage with the latter approach is there is less coding involved. Error handling is an important and often skimped on aspect of any application.

The documentation from Oracle is here.

EDIT:

If this is a package, and you want to avoid passing a long chain of error variables through a series of procedure calls, you could declare an error variable with package scope, set it when the error is encountered, and RAISE the error again.

PACKAGE BODY my_pkg is
  g_err  VARCHAR2(256);

PROCEDURE procx(... , p_err OUT VARCHAR2) IS...
  ...
  proc_y(p1);
EXCEPTION
  WHEN OTHERS THEN
    p_err := NVL(g_err, SQLERRM);
END;

PROCEDURE proc_y(p1 VARCHAR2) IS
...
proc_z(p2);

END;

PROCEDURE proc_z(p2 VARCHAR2) IS
  too_many_values EXCEPTION;
  PRAGMA EXCEPTION_INIT(too_many_values, -1427);
BEGIN
  ....
EXCEPTION
   WHEN TOO_MANY_VALUES THEN
      g_err := 'More than one value available to assign in the update';
      RAISE;  -- re-raise the exception for the caller
END;

When the exception is raised in proc_z, it is handled and then raised again. It propagates back through proc_y (no handler there) and then gets returned to the user in proc_x. Errors not set in the global g_err get the generic Oracle error message. This avoids having to pass the initial error parameter throughout the package.