CODEWITHSUNDEEP
oracle-databaseplsqlexceptionoracle12c
Anders
Anders

Reputation: 8577

Saving a PL/SQL exception and raising it later?

I have a PL/SQL procedure (in an Oracle 12c database) that tries to insert some data. If that fails, it should check a table to see if it can find some info there to help solve the problem. If it finds the information everything is fine, if not it should reraise the error.

This is my code:

BEGIN
  -- Try to insert some data.
  INSERT INTO table VALUES x;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      -- Check a table to fins some info to help solve the problem.
      -- If we find a row here, we can fix it.
      -- If not, we should reraise the error.
      SELECT * INTO y FROM table WHERE a = b;
      -- Do some more stuff here to fix the problem.
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- We could not find anything in the table,
        -- so we could not handle the situation.
        -- Reraise the error.
        RAISE;
    END;
END;

The problem here is that the RAISE; statement raises the latest exception, which is the NO_DATA_FOUND that the SELECT statement threw. The original exception from the INSERT is further down in the stack, but not at the top.

Can I somehow "save" the error from the INSERT and reraise it? Or can I run a SELECT INTO that does not throw an error if it finds nothing? My goal here is to reraise the original INSERT exception without having any traces of the NO_DATA_FOUND exception on it.

EDIT: See comments as to why this is not a duplicate.

Upvotes: 2

Views: 1208

Answers (2)

hinotf
hinotf

Reputation: 1138

May be something like this:

DECLARE
  l_sqlerrm  VARCHAR2(4000);
  l_sqlerrc  NUMBER;
  l_exc      EXCEPTION;
BEGIN
  -- some code with errors
EXCEPTION
  WHEN OTHERS THEN
    l_sqlerrm := SQLERRM;
    l_sqlerrc := SQLCODE;
    -- loggin
    INSERT INTO my_log (code, text) VALUES (l_sqlerrc, l_sqlerrm);
    COMMIT;
    -- some your code "Check a table to fins some info to help solve the problem"
    -- some code to SELECT code INTO l_sqlerrc FROM my_log
    PRAGMA exception_init(l_exc, l_sqlerrc);
    RAISE l_exc;
END;

Upvotes: 1

collapsar
collapsar

Reputation: 17238

Pull the raise statement out of the block trying to fix the problem. In case you need to reraise contingent to failure of the fixing, set a flag in the inner exception handler and only execute raise when that flag is true.

As code:

DECLARE
   b_reraise BOOLEAN := FALSE;
BEGIN
  -- Try to insert some data.
  INSERT INTO table VALUES x;
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      -- Check a table to fins some info to help solve the problem.
      -- If we find a row here, we can fix it.
      -- If not, we should reraise the error.
      SELECT * INTO y FROM table WHERE a = b;
      -- Do some more stuff here to fix the problem.
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        -- We could not find anything in the table,
        -- so we could not handle the situation.
        -- Reraise the error.
        b_reraise := TRUE;
    END;

    IF b_reraise THEN
        RAISE;
    END IF;
END;

Upvotes: 4

Related Questions