Re: SQLCODE vs. ORA-nnnnn msgs

Ed,

I went through exactly the same exercise in 1995 (SQL/DS / IBM COBOL to Microfocus COBOL / HP-UX / Oracle7).

Unfortunately as you have found, there is no correlation between DB2 error codes and Oracle error codes (with the exception of the ANSI standard +100). I had to first look up the meaning of the IBM error number, then rummage through the Oracle errors to find the best & nearest equivalent, and change the code. I found it easiest to create logical names for each error in a global include file, so as to avoid hard-coding magic numbers into my code (changing IF SQLCODE = +100 to IF SQLCODE = DBERR-NO-DATA-FOUND for example). Sorry, I do not have the results of my translation efforts available or I would gladly sling them over to you. Of course, there is also the problem of testin / re-evaluating each SQL operation in light of Oracle architecture - there may be new error conditions that should be tested for that were not applicable in the IBM environment.

One technique you'll find useful (if you are on Unix) is to identify the data file used by the 'oerr' program, and grep around inside it to identify candidate error codes that could match your IBM error code. I also wrote a bunch of shell scripts to take advantage of Unix text searching capabilities to help automate some of the drudgery.

Wouldn't it be fabbo if someone pops out of the woodwork now brandishing a translation table of IBM-Oracle error codes?

I spent one year of my life converting those 130 COBOL programs. Not that I'm bitter, that's how I got my start in Oracle. Of course, the database schema design was changing too (in addition to the COBOL compiler, the database and the operating system) !!!!!!! :-P

Martin Doherty

Ed Stevens wrote:

>Let's try this again, perhaps I can clarify.
>
>When an app issues a SQL statement, various bits of info are returned in a
>memory sturctue usually referred to as the SQLCA area. That area contains a
>numeric SQLCODE and and an alpha SQLERRM. Our apps typically check the SQLCODE
>after each SQL statement. They check for expected values (such as +100 for 'no
>more data') then have a common error routine for 'other'.
>
>We are in the process of converting some cobol apps from DB2/2 to Oracle. We
>know for a fact that some of the specific SQLCODE values being checked don't
>have the same meaning in Oracle as they do in DB2/2. We're looking for a
>reference for those values. Everything and everyone keeps pointing me back to
>the Error Msgs manual, but I'm not finding any reference to specific values or
>SQLCODE.
>
>Is there some way to correlate the value of SQLCODE to the numeric value in
>ORA-nnnnn msgs?
>
>My question is not 'I'm getting this SQLCODE value, what does it mean?" Rather,
>it is "I want to check for this condition, what SQLCODE value should I be
>looking for?"
>--
>Ed Stevens
>(Opinions expressed do not necessarily represent those of my employer.)
>
>
Received on Fri Dec 06 2002 - 19:26:08 CST