Re: PRAGMA EXCEPTION_INIT in db procedures?

In article <bamon-090394071418_at_amon.cc.oberlin.edu>, bamon_at_ocvaxc.cc.oberlin.edu (Jennifer R. Amon) wrote:

Duh! I found it. I've got to stop working at 5a.m. :-)

I'm trying to assign 'N' to a success_flag that's a number.

Change the 'N' to 0 (zero) and you're all set.

It works beautifully.

>
> 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
> _____________________________________________________________________

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
_____________________________________________________________________