Re: Oracle8i JDBC versus Postgresql?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 19 Sep 2002 07:05:33 -0700
Message-ID: <2687bb95.0209190605.241583d9@posting.google.com>

"David Wall" <d.wall_at_computer.org> wrote in message news:<W0ci9.2$QH2.492_at_news03.micron.net>...
> Does anybody have experience using both PG's JDBC and that for Oracle8i?
> I'm interested in any gotchas and such that I might come across.
> I have to port my app from PG7.2.2 to O8.1.7...
>
> For example, I know that I have a non-portable bit of code to detect a
> DUPLICATE KEY exception. There are times when a duplicate key exception
> alter the flow of control, and for those cases, I need a way to detect
> these. For now, the hack for PG is to check for some specific text in the
> SQLException. I suspect there will be something in Oracle, as well, though
> I hope it's cleaner, such as a specific error code within the exception.
>
> Second, I know that for some user-defined queries, I use ILIKE to find
> matches on fields without regard to case. I'll need some way to handle that
> in Oracle, which I understand may not have such a thing (is there a
> "pure/portable SQL" way to do this?).
>
> As for datatypes, in PG I've pretty much stuck with CHAR, VARCHAR, TEXT
> (String); OID(blob); INT2, INT4 and INT8 (short,int,long); BOOL (boolean);
> DATE and TIMESTAMP.
>
> Does anybody know any good sources of such info?
>
> Thanks,
> David

Normally if you are going to be looking at the return codes you will be coding in a Pro* language (Pro*C, Pro*COBOL ..) or plsql. SQL*Plus does hava a "whenever sqlerror" clause but it is generally not good for working with the exact error message.

If you attempt to insert a duplicate key you will get ORA-00001 (providing that a unique index, or a PK or UK constraint has been defined on the affected columns). On UNIX platforms Oracle provides the oerr script which will give brief error messages:

$ oerr ora 00001
00001, 00000, "unique constraint (%s.%s) violated" // *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.

//         For Trusted Oracle configured in DBMS MAC mode, you may see
//         this message if a duplicate entry exists at a different

level.
// *Action: Either remove the unique restriction or do not insert the key.

In plsql you can use the built-in (predefined) error exception, dup_val_in_index, to capture this error.

Example -
begin
...
insert into table [col list] values (value_list...); ...
exception
 when dup_val_in_index then

    null; -- ignore error or you could have logic end;

I would suggest you skim the Oracle Developers Guide - Fundamentals, and the PL/SQL Guide if you are new to Oracle and are going to be porting code. The chapter on single row functions in the SQL Manual will probably also come in very handy.

HTH -- Mark D Powell -- Received on Thu Sep 19 2002 - 09:05:33 CDT