Re: Error not thrown back to Java app?

"Ernie Harrison" <ernieh_at_felisonca.com> wrote in message news:9ec6f352.0309231049.203844c5_at_posting.google.com...
> Help! One of our contractors is saying that Oracle (8i) is not
> throwing back an error message on a primary key violation, so his app
> doesn't know the transaction failed. He isn't an Oracle developer,
> I'm not a Java developer, so we have a disconnect in the middle. The
> app did work until late last week, when he made some changes, but he
> says his changes couldn't have done anything. I'm trying to get this
> resolved before it turns into a puking contest. He wants me to "check
> the transaction log" to see why Oracle is failing to throw the error.
> I'm not sure there is such a thing, at least the way he seems to
> think. On my end, the table simply doesn't have the new rows
> inserted, because there was a primary key violation. But is there a
> situation where Oracle would not throw the error back to the app? Or
> is there something he needs to add to his app to catch it? The app
> seems to have caught other errors at other times; he's experienced
> with Java against DB2, so he's not new to the Java-database
> programming world. But I'm not sure where to turn next on this.
>
> Thanks,
> Harry Boswell

I had a similar thing happen to me with a program that wasn't mine (thank goodness). The program was reporting unique constraint errors from Oracle. At first I thought an index took a dump, or something else strange, so I was nosing around in DBA Studio (Oracle 8i, Win2k). In there, you can see each user's session, and the last SQL that they sent to the database. I had a user run the program, and refreshed the DBAStudio display after every option they clicked. After they clicked "add record", I refreshed the display, saw the SQL command, and was able to figure out the error in the person's program (a counter had reset itself, and the program was trying to put the "bad" pre-existing counter value into a new row's primary key). Never quite bothered to learn the SQL command to look at a user's last command; I probably should do that one day... I'm assuming it's in v$session? But, maybe you can do something similar.

Some of the developer's changes might've unearthed a problem in another section of code he hasn't touched in years, and that might be why he's insisting that his code is fine. Is it very old code? Something stupid might have happened at a magic number, like 32767- even though an Oracle NUMBER can go much higher- and then maybe the counter reset to 0, added 1, and guess what, a primary key value of 1 already exists.

As far as I see, being a developer and database guy, he shouldn't be depending on Oracle to throw back an error. He should be preventing that primary key violation from happening before it ever goes to Oracle. I'd rather query the system for the value first, and then insert it, then try to insert it right away and get an error back. Or, I'd write a PL/SQL to handle the insertion- and the error. Or am I way off base on this?

Good luck,

-Thomas Received on Tue Sep 23 2003 - 18:43:48 CDT