function / varchar width... varchar2(4000)?

From: W. Scott Moore <sirws_at_hotmail.com>
Date: Tue, 28 Dec 1999 10:30:19 -0700
Message-ID: <zu6a4.94$t%2.2648@news.uswest.net>

I have a function:

function APN_NUM_KEY_F (bookmap IN NUMBER, width IN NUMBER)   return varchar2
  is
    bookmapstr varchar2(5);
  begin
    if width = 3
    then
      if bookmap < 10
   then

        bookmapstr := '00' || to_char(bookmap);
        return(bookmapstr);
      elsif (bookmap < 100 and bookmap >= 10)
     then
          bookmapstr := '0' || to_char(bookmap);
          return(bookmapstr);
   else
        bookmapstr := to_char(bookmap);
        return(bookmapstr);
      end if;

 elsif width = 2
   then

        if bookmap < 10
         then
            bookmapstr := '0' || to_char(bookmap);
            return(bookmapstr);
     else
       bookmapstr := to_char(bookmap);
          return(bookmapstr);

   end if;
 end if;
  end;

and when I call it like:

select apn_num_key_f(apn_map, 3) from lb_parcel_pnt

it works fine. The same goes when I use it in a view like:

CREATE OR REPLACE VIEW LB_PARCEL_POLY_V ( PPOLYID, APN_BOOK, APN_MAP, PARCEL_ID, APN
 ) AS select a.ppolyid, c.apn_book, c.apn_map, c.parcel_id, apn_num_key_f(c.apn_book, 3) || apn_num_key_f(c.apn_map, 2) || c.parcel_id as APN from lb_parcel_poly a,

    lb_parcel_pnt_poly_join b,
    lb_parcel_pnt c
where b.ppolyid = a.ppolyid and b.ppntid = c.ppntid

however, when I am using toad to look at the data, it says it is a varchar2(4000), when I would like it to be a varchar2(10).

Any ideas would be greatly appreciated.

Thanks,
Scott Received on Tue Dec 28 1999 - 11:30:19 CST