Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL Questions...
A copy of this was sent to Richard Fairbairn <r.fairbairn_at_zetnet.co.uk>
(if that email address didn't require changing)
On Thu, 21 May 1998 14:37:53 +0100, you wrote:
>I have one or two Procedural SQL queries and wonder if anyone has a
>few minutes to entertain them. Here's the first:
>
>How do I change ORA error messages (so they are more user friendly,
>for example).
>I've tried using PRAGMA EXCEPTION_INIT, but don't understand how it
>should work (and therefore why it does not).
>
You can use raise_application_error in PL/SQL to supply your own message. For example:
begin
sql-statement;
exception
when NO_DATA_FOUND then raise_application_error( -20000, 'Sorry, we are unable to find any data for you' ); when DUP_VAL_ON_INDEX then raise_application_error( -20001, 'Sorry, someone else already put that data in database' );end;
there are various NAMED exceptions of which I've used 2 of, they are:
CURSOR_ALREADY_OPEN ORA–06511 –6511
DUP_VAL_ON_INDEX ORA–00001 –1 INVALID_CURSOR ORA–01001 –1001 INVALID_NUMBER ORA–01722 –1722
PROGRAM_ERROR ORA–06501 –6501 ROWTYPE_MISMATCH ORA–06504 –6504 STORAGE_ERROR ORA–06500 –6500 TIMEOUT_ON_RESOURCE ORA–00051 –51
declare
resource_busy exception pragma exception_init( resource_busy, -54 ); begin sql-statement; exception when resource_busy then raise_application_error( .... );end;
Exception init is simply a tool to map any arbitrary Oracle Error code (-54 in this example) to a NAMED exception so you can catch it by name (as opposed to have a generic when others catchall that inspects the sqlcode)
>Also, how do the datatypes CHAR and VARCHAR2 differ from a SQL and
>PL/SQL P.O.V.? I've just heard that when you create a table (E.g.,
>CREATE TABLE my_tab
>(my_column VARCHAR2(20));
>
>The datatype differs as to when you use it as such:
>
>DECLARE
>my_var VARCHAR2(30);
>begin
>...
>
>I am curious about this as I am led to believe that VARCHAR2 (and
>other datatypes?) behave in different ways according to how they are used.
>
>Can someone spare a minute to answer these questions? I'd be very grateful.
>
>Best regards
>
>
>Richard Fairbairn
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 21 1998 - 10:49:47 CDT