<> block

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?

13.10.2009 15:19:10
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.

2
14.10.2009 10:06:52

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;
2
13.10.2009 15:27:47
The way you mentioned we'll have to handle exceptions when they occur right? However, that is not a good options for us. we are converting lots of sql server stored procedures to oracle and the tool we used (sql developer) made goto statements. we'd hate to go into each SP and start messing with the logic
Omnipresent 13.10.2009 15:33:57
@Omnipresent: When you say "SQL developer", do you mean Oracle SQL Developer, or some other tool of the same name?
Tony Andrews 14.10.2009 10:09:19

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.

1
13.10.2009 15:49:18