Re: Funciton & VARCHAR2 problem

Hi!
Char(4) might be your problem, because a column of CHAR is always padded with blanks to get max-length
in this case 4 characters. Your p_code is VARCHAR2, and is probably trimmed so there are noe
extra blanks

Try: TRIM to a.code column but this will affect your performance if this is some indexed column, in that case
consider indexe functions on the column or alter the type of the column to VARCHAR2 which is easier to deal
with in most cases.

Frank

"miker" <mdent12_at_none.com> wrote in message news:mpu8bu8mnu5ndqo8sfsc7rnlubre72u52b_at_4ax.com...
> ver 8.1.7, Win2k.
>
> The column "code" in table dd is defined as char(4).
>
> How do I define variable p_code? The "FUNCTION dCount (p_code IN
> VARCHAR2)" declaration gives no results at all, i.e. count is 0. If I
> try to declare it as "CHAR(4)" I get compilation error.
>
> >Version? OS? Which errors?
> >
> >With 8.0.5.1.0 on Solaris 2.6 it's no problem:
> >
> >KNUT_at_TST.TALMAN.DE> create table dd (code varchar2(10));
> >
> >Table created.
> >
> >KNUT_at_TST.TALMAN.DE> insert into dd values ('AA');
> >
> >1 row created.
> >
> >KNUT_at_TST.TALMAN.DE> commit;
> >
> >Commit complete.
> >
> >KNUT_at_TST.TALMAN.DE> create or replace FUNCTION dCount (p_code IN
VARCHAR2)
> >2 RETURN NUMBER IS
> >3 result NUMBER;
> >4 BEGIN
> >5 select count(*) INTO result from dd a
> >6 where a.code=p_code;
> >7 RETURN result;
> >8 END;
> >9 /
> >
> >Function created.
> >
> >KNUT_at_TST.TALMAN.DE> select dCount('AA') from dual;
> >
> >DCOUNT('AA')
> >------------
> > 1
>
Received on Thu Apr 11 2002 - 02:49:54 CDT