Re: Funciton & VARCHAR2 problem

From: Knut Talman <knut.talman_at_mytoys.de>
Date: Wed, 10 Apr 2002 18:50:37 +0200
Message-ID: <3CB46D5D.2187EF82@mytoys.de>

> if i hard code the variable i get the right result but if the
> parameter is passed in as a VARCHAR2 it doesn't work. what is wrong?
>
> FUNCTION dCount (p_code IN VARCHAR2)
> RETURN NUMBER IS
> result NUMBER;
> BEGIN
> select count(*) INTO result from dd a
> where a.code=p_code -- works fine if i say a.code='HR';
> RETURN result;
> END;
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 Wed Apr 10 2002 - 11:50:37 CDT