PRAGMA EXCEPTION_INIT in db procedures?

From: Jennifer R. Amon <bamon_at_ocvaxc.cc.oberlin.edu>
Date: Wed, 09 Mar 1994 07:14:18 -0500
Message-ID: <bamon-090394071418_at_amon.cc.oberlin.edu>

Does the pragma execption_init not work from stored database procedures?

I have the following procedure:

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;
      bad_date_1   EXCEPTION;
      too_short    EXCEPTION;
      bad_year     EXCEPTION;
      bad_month    EXCEPTION;
      bad_date_2   EXCEPTION;
      not_number   EXCEPTION;
      not_letter   EXCEPTION;
      bad_delim    EXCEPTION;
      PRAGMA EXCEPTION_INIT(bad_date_1,-1839);
      PRAGMA EXCEPTION_INIT(too_short,-1840);
      PRAGMA EXCEPTION_INIT(bad_year,-1841);
      PRAGMA EXCEPTION_INIT(bad_month,-1843);
      PRAGMA EXCEPTION_INIT(bad_date_2,-1847);
      PRAGMA EXCEPTION_INIT(not_number,-1858);
      PRAGMA EXCEPTION_INIT(not_letter,-1859);
      PRAGMA EXCEPTION_INIT(bad_delim,-1897);

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

      success_flag :='N';
      out_message := 'Date not valid for month specified.';
    when too_short then
      success_flag :='N';
      out_message := 'Input value for date not long enough.';
    when bad_year then
      success_flag :='N';
      out_message := 'Year must be between -4713 and +4713.';
    when bad_month then
      success_flag :='N';
      out_message := 'Not a valid month.';
    when bad_date_2 then
      success_flag :='N';
      out_message := 'Day must be between 1 and the last day of the

month.';

    when not_number then

      success_flag :='N';
      out_message := 'A letter was found when a number was expected.';
    when not_letter then
      success_flag :='N';
      out_message := 'A number was found when a letter was expected.';
    when bad_delim then
      success_flag :='N';
      out_message := 'Invalid delimeter in the date input string.';
    when others then
      success_flag :='N';
      out_message := 'Unidentified error. Possible bad date format.';

  end oaso0009;
.
/

And I do the obligatory:

   grant execute on oaso0009 to public;
   create public synonym check_date for owner.oaso0009;

Then, there's a little test routine to go with it:

declare
  parm_mask varchar2(40);
  parm_date varchar2(40);
  ok number(1);
  ok_char varchar2(1);
  results varchar2(255);
begin

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

  ok := 0;
  results := 'Initialized';
  parm_date := '12:30';
  parm_mask := 'HH24:MI';
  check_date(parm_date,parm_mask,ok,results);   if ok = 1 then
    ok_char := 'Y';
  else
    ok_char := 'N';
  end if;
  insert into ops$bamon.test_oaso0009 values
(parm_date,parm_mask,ok_char,results);

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

  ok := 0;
  results := 'Initialized';
  parm_date := '30-FEB-1994 12:30';
  parm_mask := 'DD-MON-YYYY HH24:MI';
  check_date(parm_date,parm_mask,ok,results);   if ok = 1 then
    ok_char := 'Y';
  else
    ok_char := 'N';
  end if;
  insert into ops$bamon.test_oaso0009 values
(parm_date,parm_mask,ok_char,results);

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

  ok := 0;
  results := 'Initialized';
  parm_date := '01-JAN-9876 12:30';
  parm_mask := 'DD-MON-YYYY HH24:MI';
  check_date(parm_date,parm_mask,ok,results);   if ok = 1 then
    ok_char := 'Y';
  else
    ok_char := 'N';
  end if;
  insert into ops$bamon.test_oaso0009 values
(parm_date,parm_mask,ok_char,results);

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

  ok := 0;
  results := 'Initialized';
  parm_date := '01-MAR-94 12:30';
  parm_mask := 'DD-MON-YYYY HH24:MI';
  check_date(parm_date,parm_mask,ok,results);   if ok = 1 then
    ok_char := 'Y';
  else
    ok_char := 'N';
  end if;
  insert into ops$bamon.test_oaso0009 values
(parm_date,parm_mask,ok_char,results);

  etc., etc., etc. ad nauseum....

And here's what happens when I run it:

  SQL> _at_test_oaso0009
  declare
  *
  ERROR at line 1:

  ORA-06502: PL/SQL: numeric or value error
  ORA-06512: at "OBERLIN.OASO0009", line 35
  ORA-06512: at line 26
  ORA-01839: date not valid for month specified

Hmmmm. I'm supposedly trapping for that.

Suggestions?

---

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 - 13:14:18 CET