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.