Re: How to capture error message using oraperl ?

From: Loren Davidson <loren_at_wombat.net>
Date: 1996/08/09
Message-ID: <320B8133.7004_at_wombat.net>#1/1


J-P Korkiakoski wrote:
>
> I am trying to communicate with our Oracle7 database using oraperl.
> I don't know how to capture the error message the database gives if,
> for example, a user tries to insert a duplicate value to a table and
> violates the primary key constraint. In order to display the right error
> messages to the users instead of some general "database error" I should
> be able to capture the error message. I hope this is possible.
> (The oraperl script is a CGI-script.)

You want to capture & print the variable $ora_errstr, which contains the text of the error message, if it exists. Your &ora_open, &ora_fetch or &ora_do will return values depending on what you're doing. Test the return values for errors (i.e. a null array from &ora_fetch) and put a PRINT statement inside an IF block to get the error message.

If you want to get really fancy, you could trap the error number (I forget the variable name at the moment) and use an array to match it to a custom error message that you create. Oracle error messages, even the text ones, are still often cryptic.

Finally, another way to resolve this for the primary key issue would be to do an &ora_fetch SELECT statement before attempting the insert. Query the database for any matches to the user-submitted primary key value, and generate your own error message in Perl for the user if it's a duplicate. If it's not, go ahead with the insert.

The oraperl man page (assuming you're running UNIX of some flavor) describes this and all the functions/variables available to you, and has a better memory than I do. :)

Good luck,

Loren

-- 
Loren Davidson
loren_at_wombat.net
http://www.batnet.com/beauty/
The First Amendment went too far.  It should have said, "Congress shall 
make no law".
Received on Fri Aug 09 1996 - 00:00:00 CEST

Original text of this message