Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL Questions...

Re: PL/SQL Questions...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 21 May 1998 15:49:47 GMT
Message-ID: <35674ac9.12549465@192.86.155.100>


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

LOGIN_DENIED ORA–01017 –1017
NO_DATA_FOUND ORA–01403 +100
NOT_LOGGED_ON ORA–01012 –1012
PROGRAM_ERROR ORA–06501 –6501
ROWTYPE_MISMATCH ORA–06504 –6504
STORAGE_ERROR ORA–06500 –6500
TIMEOUT_ON_RESOURCE ORA–00051 –51

TOO_MANY_ROWS ORA–01422 –1422
VALUE_ERROR ORA–06502 –6502
ZERO_DIVIDE ORA–01476 –1476 But lets say you want to catch ORA-54 "Resource Busy" and redefine its error message. You would:

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  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US