Home » Developer & Programmer » Precompilers, OCI & OCCI » SQLCODE values (Oracle 10g - HP-UX B.11.11)
SQLCODE values [message #332049] Mon, 07 July 2008 06:10 Go to next message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Hi all!
Well, i'm not sure this forum it's the most suitable for my post...
I'm migrating Informix based application (wich uses ESQL/C) to Oracle (using Pro*C). But my doubt is about the uses and values of SQLCODE.
Well, the most used values are '0' (success) and '100' (no more rows). This two values are the same in Informix and Oracle. But the rest of values are different things (obviously, are different DBs).
For example, my application test this Informix error:
-271 -- Could not insert new row into the table

Well, i'm thinking in force my application to get this kind of error. This way, i'll see the error code that Oracle gives me. But i'm not sure that this solution could be 100% reliable. And it could be very cumbersome.
Did anybody make something similar?
Do anybody knows an existant document like 'Migrating SQLCODE from Informix to Oracle'? -- that would be unbeatable--.
I wasn't find nothing useful in Oracle documentation, and the Migration Workbench don't make anything about this business.
Anybody can help me? Any advice will be accepted
Thanks in advance,
Donato
Re: SQLCODE values [message #332058 is a reply to message #332049] Mon, 07 July 2008 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To be fully compatible you have to use SQLSTATE and MODE=ANSI and not SQLCODE in both environments.
Otherwise you have to check each code, I don't know any document that gives you the matching (this does not mean it does not exist).

Regards
Michel
Re: SQLCODE values [message #332068 is a reply to message #332049] Mon, 07 July 2008 07:32 Go to previous messageGo to next message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Hi Michel,
i have to use SQLCODE. Otherwhise would be a new application, not a migration -customer demands :?-.
I think i have to check code by code.
Thanks!

Re: SQLCODE values [message #332073 is a reply to message #332068] Mon, 07 July 2008 07:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I think i have to check code by code.

This is what I'd do if I had to do it.
Please here your findings for future readers.

Regards
Michel
Re: SQLCODE values [message #333488 is a reply to message #332049] Fri, 11 July 2008 22:00 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
I could have sworn that I once saw a listing of the "common" SQLCODEs - but of course I cannot locate such a listing now.

The Oracle Precompilier Guild states that SQLCODEs less than zero are the same as the Oracle 7 server error codes. I suppose that someone still has that book around somewhere, but I'm not going to try to look for it.

But, seriously, do you really need to decipher the exact error code? I've done a fair amount of precompilier programming, and I've never needed anything more than a 3-way test:

SQLCODE = 0 ==> operation successful.

SQLCODE > 0 (equals 1403 or 100 depending on switch settings) ==> the NO_DATA_FOUND exception was raised. On a SELECT or FETCH, nothing was returned (end of cursor on a FETCH). On a DELETE, INSERT, or UPDATE, nothing was DELETEd, INSERTed, or UPDATEd.

SQLCODE < 0 ==> your request failed.

The thing is, at this point, I've never been able to fix things on the fly and continue. In other words, even if I knew exactly why things failed, there has never been anything I could do about it.

The SQLCA does give you the actual error message, so you can at least let people know why you are aborting your job. I suppose you could even check it for that rare case where you can recover from a real error. But, I don't see what you could do if you knew exactly why things failed, as opposed to just knowing they failed.
Re: SQLCODE values [message #333505 is a reply to message #333488] Fri, 11 July 2008 23:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
negative SQLCODE are those in Database Error Messages, the problem is to have a correspondance with Informix codes.
SQLSTATE is a standard.
SQLCODE is not (except for 0 and 100, if you works with MODE=ANSI).

Quote:
But, I don't see what you could do if you knew exactly why things failed, as opposed to just knowing they failed.

Some errors can be automatically handled by the progam.
This is the same thing that EXCEPTION block in PL/SQL.

Regards
Michel
Re: SQLCODE values [message #333574 is a reply to message #332049] Sat, 12 July 2008 21:49 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Quote:
SQLSTATE is a standard.
SQLCODE is not (except for 0 and 100, if you works with MODE=ANSI).


I completely agree that SQLSTATE should be preferred to SQLCODE. The only reason to use SQLCODE is that every other program in the shop was written with SQLCODE, and your standards do not allow for any change. Which appears to be the case with the O.P. I was trying to give him (or her) some hope.

Quote:

Some errors can be automatically handled by the progam.
This is the same thing that EXCEPTION block in PL/SQL.


True enough -- but -- in my (maybe sheltered) experience, anything which would raise a negative SQLCODE in a precompilier program would get handled by a WHEN OTHERS block in PL/SQL.

Oh, let's take a look at the predefined PL/SQL exceptions, and see how many are likely to be handleable:

ACCESS_INTO_NULL - Do people normally write code where one doesn't know if an object is initialized or not? This is an open question; it seems to me to be a bad practice

CASE_NOT_FOUND - Seems to me to be a logic bug
COLLECTION_IS_NULL - Same question as ACCESS_INTO_NULL
CURSOR_ALREADY_OPEN - Again this looks like always a logic error.
DUP_VAL_ON_INDEX - O.K., back before the MERGE statement existed, I would code my UPSERTS as an UPDATE followed by an INSERT if the UPDATE failed. And I would key on NO_DATA_FOUND. Which would generate a positive SQLCODE in the 3GL world. I suppose some people might prefer an INSERT followed by an UPDATE. So, I can see now where DUP_VAL_ON_INDEX might have to be tested explicitly.
INVALID_CURSOR - Logic bug
INVALID_NUMBER - Bad data
LOGIN_DENIED - Can't recover from this
NO_DATA_FOUND - Positive SQLCODE
NOT_LOGGED_ON - I can imagine some situations where a daemon might have to test if it is logged on. But I can't imagine any which would not be done easier with the Job Scheduler, which eliminates this problem.
PROGRAM_ERROR - Doomed
ROWTYPE_MISMATCH - Cannot recover
SELF_IS_NULL - See my first comment
STORAGE_ERROR - I don't see how one can recover
SUBSCRIPT_BEYOND_COUNT - Unless you are trying to write a buffer overflow, I don't see how you can recover from this one.
SUBSCRIPT_OUTSIDE_LIMIT - Ditto
SYS_INVALID_ROWID -Ditto
TIMEOUT_ON_RESOURCE - Don't tell me you're trying to do DDL in embedded SQL ...
TOO_MANY_ROWS - Logic bug
VALUE_ERROR - Logic bug
ZERO_DIVIDE - Data bug

It still looks to me that the number of times you can do anything when you get a negative SQLCODE is very low. So the number of codes where the O.P. would have to actually know the correspondence between Informix and Oracle will be very few.
Re: SQLCODE values [message #333580 is a reply to message #333574] Sun, 13 July 2008 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I disagree with you on TOO_MANY_ROWS, there may be case where this is not abnormal as well as NOt_DATA_FOUND.
I use them as well as many others that are not in the predefined list. Negative code are thousand ones.
For instance, there are code when a RAC fail over happens, then it is good to warn the user to wait a while until it ends.
NOT_LOGGED_ON can also happen during a failure which may allow an application server to reconnect and reissue the treatment.
VALUE_ERROR or INVALID_NUMBER may be useful to determine if a string is a valid number (or date with other codes) and then handle this data differently depending on the case.
And so on...

Regards
Michel
Re: SQLCODE values [message #334064 is a reply to message #332049] Tue, 15 July 2008 06:36 Go to previous message
donato
Messages: 53
Registered: November 2007
Location: Barcelona, Spain
Member
Hi people!
The cases in wich tha application tests for SQLCODE special values ar very punctuals, and i have to migrate this code as similar as possible.
These tests are mostly looking for row or table locks. And this locks works different way between Informix and Oracle, so i have to be very careful.
Well, by the moment i'm on other project parts. When i have obtained results, i'll tell you something more.

Regards,
Donato
Previous Topic: Issue with Pro*C/C++ 10.2.0.2 application
Next Topic: /usr/lib/pa20_64/dld.sl: Unsatisfied code symbol 'dladdr' in load module ...libclntsh.sl.10.1
Goto Forum:
  


Current Time: Thu Mar 28 09:34:28 CDT 2024