Re: PRAGMA EXCEPTION_INIT in db procedures?

From: Jennifer R. Amon <bamon_at_ocvaxc.cc.oberlin.edu>
Date: Wed, 09 Mar 1994 08:17:25 -0500
Message-ID: <bamon-090394081725_at_amon.cc.oberlin.edu>

Okay, assuming that you are willing to use Oracle's standard messages, which in this case, I am -

Here's the short version:

create or replace procedure
  oaso0009 (date_string in varchar2,

            date_mask in varchar2,
            success_flag out number,
            out_message out varchar2) is
    /********************************************/


    /* Check that the date_string is a valid    */


    /* date or time which corresponds to the    */

    /* specified date format mask.              */

    /* Return a success flag and a message      */

    /********************************************/

    temp_date date;
  begin
    success_flag := 1;
    temp_date := to_date(date_string,date_mask);     out_message := 'Success';
  exception
    when others then

      success_flag := 0;
      out_message := substr(sqlerrm,1,255);

  end oaso0009;
.
/

Now, here's the follow up-question:

I get the following results in my test:

SQL> select * from test_oaso0009;

PARM_DATE PARM_MASK OK?

-------------------- -------------------- ---

RESULTS

---

01JAN94 DD-MON-YY N ORA-01843: not a valid month

11:69                HH24:MI              N

ORA-01851: minutes must be between 0 and 59

24:30                HH24:MI              N

ORA-01850: hour must be between 0 and 23

01-DEC-1994 12:30 DD-MON-YY HH24:MI N ORA-01830: date format picture ends before converting entire input string

30-FEB-1994 12:30 DD-MON-YYYY HH24:MI N ORA-01839: date not valid for month specified

01-JAN-9876 12:30 DD-MON-YYYY HH24:MI N ORA-01841: (full) year must be between -4713 and +4713

01-MAR-94 12:30 DD-MON-YYYY HH24:MI Y Success

WHY does the last one succeed?

AND, There used to be an 1897 (Bad delimeter) error. What happened to that one?

In case you couldn't tell, this code was stolen and modified from a menu procedure that I wrote several years ago. I'm cleaning up leftovers. The original procedure was written for validation of date parameters entered through SQL*Menus. It's still valid for that purpose, but you need a BUNCH of exception definitions and pragma exception_init statements if you want to replace the Oracle messages with something friendlier. I guess if I wanted to do that, I'd put the substitute message into a table in the database and select them from there on the basis of the error number.

Enjoy.

---

Jennifer R. Amon            PHONE: (216) 775-6987
Houck Computing Center        FAX: (216) 775-8573
Oberlin College
Oberlin, OH 44074        INTERNET: bamon_at_ocvaxc.cc.oberlin.edu
_____________________________________________________________________

Received on Wed Mar 09 1994 - 14:17:25 CET