Re: help with D2K

From: Rohrbacher, Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: Tue, 30 Jun 1998 22:16:53 +0200
Message-ID: <359947B4.8249F193@sbox.tu-graz.ac.at>

yonglove_at_usa.net wrote:

> I'm new to Oracle but can someone please tell me why code section #1 works
> (does not hit the exception clause), but #2 fails (hits the exception
> clause)???
>
> --- code section #1 ---
>
> FUNCTION get_prov_name (v_prov# CHAR)
> RETURN CHAR IS
> v_provname VARCHAR2(29) ;
> BEGIN
> select RTRIM(firstname) || ' ' || RTRIM(lastname)
> into v_provname
> from health.provider_at_gtw
> where prov# = 'HSP40113F' ;
> RETURN v_provname ;
>
> EXCEPTION
> WHEN OTHERS THEN
> RETURN '-1';
>
> END;
>
> --- code section #2 ---
>
> FUNCTION get_prov_name (v_prov# CHAR)
> RETURN CHAR IS
> v_provname VARCHAR2(29) ;
> BEGIN
> select RTRIM(firstname) || ' ' || RTRIM(lastname)
> into v_provname
> from health.provider_at_gtw
> where prov# = v_prov# ;
> RETURN v_provname ;
>
> EXCEPTION
> WHEN OTHERS THEN
> RETURN '-1';
>
> END;
>

This is a case where you locked yourself succesfully out. Why : You catch the exception that appears and return '-1' and now you can't say where things went wrong.

So solution in first place would be to avoid the exception handler completely.
Resulting in an error message from forms that will tell you whats wrong.

But anyway the most common problem given the code above is :

a SELECT xxx INTO vvvv can only fetch ONE value.

So if you get LESS then one Oracle throws exception : NO_DATA_FOUND and if you get MORE than one Oracle throws : TOO_MANY_ROWS

In your first case you have as I guess a proven test value for which you know that the query will work correctly. But in example 2 you use your parameter. You defined it CHAR ( why not VARCHAR2 ??) maybe this pad your parameter v_prov# to a certain length
and therefore you not comparing 'ABCD' to prov# but

'ABCD                              '    if your parameter is 'ABCD'.

Anyway if the query doesn't find anything Oracle will raise an exception.

You can avoid this in different ways :

a.) You do a cursor instead of a "inline" - SQL statement. like :

advantage : Faster , because of SQL92 standard Oracle has to do two fetches for select ... into .

                        First fetch to see if you get only only one value and

the second fetch the data itself.

disadvantage : You don't get an exception when the query finds no data. Maybe you want this

FUNCTION get_prov_name ( cv_prov VARCHAR2 ) RETURN VARCHAR2 IS

  cursor c_name is
    select RTRIM(firstname) || ' ' || RTRIM(lastname)       from health.provider_at_gtw
    where prov# = cv_prov ;

maybe you define a synonym for health.provider_at_gtw. Makes your code more generic.
If @gtw changes you don't have to run to your code but simple change synonym.

v_provname VARCHAR2(29) ;

BEGIN    open c_name ;
   fetch c_name into v_provname ;
   close c_name ;

RETURN v_provname ;

EXCEPTION
WHEN OTHERS THEN
RETURN to_char(sqlcode) ;

END; b.) simple catch the exception an do the right thing

FUNCTION get_prov_name ( cv_prov VARCHAR2 ) RETURN VARCHAR2 IS

v_provname VARCHAR2(29) ;

BEGIN select RTRIM(firstname) || ' ' || RTRIM(lastname) into v_provname from health.provider_at_gtw where prov# = cv_prov ;

RETURN v_provname ;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN 'No prov name for '||cv_prov ;    WHEN TOO_MANY_ROWS THEN
     RETURN 'More than one prov name for '||cv_prov ;    WHEN OTHERS THEN
     RETURN 'ORA'||to_char(sqlcode)||' happend. Oijeh'; END; HTH Regards
Robo Received on Tue Jun 30 1998 - 15:16:53 CDT