Re: Oracle views and function problems

From: Rainer Scheel <rainer.scheel_at_sno.drs1.x400.sni.de>
Date: Tue, 5 May 1998 09:54:15 +0200
Message-ID: <6imgmo$kvm$1@horus.mch.sni.de>

dbarker_at_tpgi.com.au wrote in message <6ilvm7$m5l$1_at_nnrp1.dejanews.com>...
>I have created an Oracle function that takes one input VARCHAR parameter
and
>returns a VARCHAR2(5) value. I have created a view that selects records
from
>a table and uses this function. When I perform a DESC on the view the
>function is of type VARCHAR2(2000). Does anybody have any ideas?
>
>FUNCTION lineitem(accnbri in VARCHAR2)
>
>RETURN VARCHAR IS tmp VARCHAR(5);

>BEGIN
> tmp := SUBSTR(accnbri, 12, 5);
> return tmp;
>END;
>
>CREATE OR REPLACE VIEW V_ACTUALS AS SELECT lineitem(accnbri) as line_item,
>glf_ldg_acct.* FROM glf_ldg_acct
>

1. You created a function returning VARCHAR, not VARCHAR2(5).
2. Inside the function, you have a local variable being VARCHAR2(5).
3. You cannot specify a length in a function argument datatype (See Oracle7

Server SQL Reference Manual).
Hth,
Rainer Received on Tue May 05 1998 - 02:54:15 CDT