Should I use too many rows as an exception clause in my Oracle Update statement?

Question

I have a number of update statements that I need to use in my Oracle package. This is rare, but there may be a random and inevitable user error, which would lead to the fact that one of the update statements, throwing "Subquery of one row, returns one or more rows" Error.

I am handling exception handling for oracle PL / SQl and I am a little fixated on how and what to use to catch this exception, so the package does not crash.

I know the prebuilt "too many lines" exception clause that exists, but everything I read seems to say that it is used for incorrect insertion instructions.

Can this be used as my exception? Or do I need to create my own exception condition. I had never built them before, and I had only a rough idea of ​​where to put everything necessary for this.

The following code basically consists of how updates are installed in this particular procedure, but for brevity, I use only the bare-bones example, what it looks like.

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 create 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. TOO_MANY_ROWS exception for ORA-01422 when issuing a SELECT .. INTO statement that returns more than one row. The exception you will encounter in your case is ORA-01427. A 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 the exception name with the error:

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

You can then 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 need to return the caller code of some code / message:

 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 specific exception handlers and return common oracle messages in the WHEN OTHERS handler:

 EXCEPTION WHEN OTHERS THEN p_err := SQLERRM; END; 

The advantage with the first approach is that you can customize your messages so that they are more convenient for end users when the result of the process is directly transmitted to the user. An advantage of the latter approach is less coding. Error handling is an important and often abbreviated 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 errors using a series of procedure calls, you can declare an error variable with the scope of the package, set it when an error occurs, and FOLLOW 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 an exception occurs in proc_z, it is processed and then raised again. It is distributed back through proc_y (there is no handler there), and then returned to the user in proc_x. Errors not set in the global g_err receive a generic Oracle error message. This avoids passing the initial error parameter in the entire packet.