SQLCODE vs. ORA-nnnnn msgs
Ed Stevens
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.)
Sybrand Bakker
The sqlcodes are *identical* to the ora-nnnn messages, with the
exception of +100 for ora-1403, when you run in Ansi-compatibility
mode.
So there are *NO SPECIFIC* sqlcodes.
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Martin Doherty
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
This conversion is being done on OS/2 to NT. I do have access to a Unix system,
but am a neophyte on that platform. I'll see what I can do there; thanks for
the tip.
I think the checking of SQLCODE is mostly done by references to 88-levels in the
SQLCA copybook, but there are probably a few hard-coded values in the procedure.
Jarl Hermansson
Can't you use SQLSTATE instead of the deprecated SQLCA/SQLCODE?
SQLSTATE, a 5-character return value specified by the SQL standard,
replaced SQLCODE years ago (SQL-92?). Perhaps both Oracle and DB2/2
have support for it.
If you really want to stick to SQLCODE, you could do one list for
SQLCODE to SQLSTATE mappings for each DBMS. Then you're half the way
translating DB2/2 codes to Oracle codes.
For more info on SQLSTATE, check out:
http://developer.mimer.com/howto/howto_25.htm
Regards,
Jarl