Re: How can I catch a specific ORACLE error in my PRO*C application

Abiy ALEMU wrote:

> I would like to make something like
> EXEC SQL WHENEVER (SQLCODE = 6502) CONTINUE
>
> I know that I can't do that but I would like to have a possibility to
> continue the processing for a specific type of error and to exit for
> some other types of errors.

You can do it, just probably not in the manner that you would like. There are two ways:

1. Write highly structured code and isolate all oracle actions in their own function. That means that OPEN should be in a function by itself, FETCH should also have its own function (don't do array fetches because that is more difficult to handle), CLOSE should be in its own function, COMMIT, etc. etc. Before the statement have the normal EXEC SQL WHENEVER SQLERROR GOTO SQLERR; where SQLERR is the error handler for that function. Have code in there that will return values that can be interpretted by the calling function to determine what to do.
2. Handle sqlcode yourself. The value you are seeking is in the sqlca structure and can be handled by something like:

if (sqlca.sqlcode == 6502){

    /* your error handling code here */ }

This really isn't recommended and it isn't guaranteed to be the same in future releases (but then again, it isn't really likely to change unless Oracle comes up with a better way), but I've used it in the past to handle specific situations in which an oracle "error" really isn't an error within the context of what I am trying to do.

One thing you should really try to do is master the contents of the sqlca and the oraca structures. There are some useful data items there and you may be able to improvde your code by knowing how to use those data elements.

Ken Received on Tue Jul 27 1999 - 07:30:17 CDT