<<SQL_ERROR>> block

Question

In our Stored procedures we have the following code towards the very end.

  <<SQL_ERROR>>
  V_SYS_ERROR_MSG := SUBSTR(SQLERRM, 1, 252);

  DBMS_OUTPUT.PUT_LINE('ERROR IN EXECUTION IN PROCEDURE');

  DBMS_OUTPUT.PUT_LINE('THE ERROR CODE IS ' || V_SYS_ERROR || '- ' ||
                       V_SYS_ERROR_MSG);

we have statements like following which call the error block.

  IF V_SYS_ERROR <> 0 THEN
    GOTO SQL_ERROR;

the DBMS output statements come even when there is no error. How can we avoid this?

Answer 1

Hi Omnipresent,

you should avoid GOTO statements, they are messy as you have noticed. PL/SQL comes with error handling, you should use the EXCEPTION synthax to deal with errors:

BEGIN
   <code goes here>
EXCEPTION
   WHEN <exception> THEN
      <deal_with_it>
   WHEN OTHERS THEN
      <log_error>
      RAISE;
END;

Answer 2

I don't think you'll get the results you want without using an exception handler. From the PL/SQL User's Guide:

SQLERRM with no argument is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, successful completion message.

So first of all, it is possible that an error is happening but you are seeing a message saying "normal, successful completion" because that is what SQLERRM will always return in this context.

Assuming that's not the case, it sounds like you are simply allowing control to flow from the "normal" code into the "error handler". If the error handler is at the very end of the procedure, then a simple fix would be to add a RETURN statement just before the <> label.

A better fix would be to move the error-handler code to a separate procedure and call that procedure instead of using a GOTO.

Answer 3

I am not recommending this GOTO approach: as others have already said, exceptions are the correct way to handle errors in PL/SQL. But to address your specific question, you could do this:

BEGIN
  IF V_SYS_ERROR <> 0 THEN
    GOTO SQL_ERROR;
  END IF;

  GOTO PROC_END;

  <<SQL_ERROR>>
    V_SYS_ERROR_MSG := SUBSTR(SQLERRM, 1, 252);

    DBMS_OUTPUT.PUT_LINE('ERROR IN EXECUTION IN PROCEDURE');

    DBMS_OUTPUT.PUT_LINE('THE ERROR CODE IS ' || V_SYS_ERROR || '- ' ||
                         V_SYS_ERROR_MSG);

  <<PROC_END>>
  NULL;
END;

Of course, this still involves changing the code, so if you are doing that why not do it properly anyway? i.e.

DECLARE
  SQL_ERROR EXCEPTION;
BEGIN
  IF V_SYS_ERROR <> 0 THEN
    RAISE SQL_ERROR;
  END IF;

EXCEPTION

  WHEN SQL_ERROR THEN
    V_SYS_ERROR_MSG := SUBSTR(SQLERRM, 1, 252);

    DBMS_OUTPUT.PUT_LINE('ERROR IN EXECUTION IN PROCEDURE');

    DBMS_OUTPUT.PUT_LINE('THE ERROR CODE IS ' || V_SYS_ERROR || '- ' ||
                         V_SYS_ERROR_MSG);
    RAISE;

END;

By the way, DBMS_OUTPUT.PUT_LINE is not suitable for output of error messages in a production application system. Only use it for debugging during development.