| SQLCODE values [message #332049] |
Mon, 07 July 2008 06:10  |
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 #332068 is a reply to message #332049] |
Mon, 07 July 2008 07:32  |
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 #333488 is a reply to message #332049] |
Fri, 11 July 2008 22:00 |
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 #333574 is a reply to message #332049] |
Sat, 12 July 2008 21:49 |
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 #334064 is a reply to message #332049] |
Tue, 15 July 2008 06:36 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
