Re: Oracle views and function problems

In article <6ilvm7$m5l$1_at_nnrp1.dejanews.com>, dbarker_at_tpgi.com.au wrote:

> snip
> 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?
>
> snip
>
> FUNCTION lineitem(accnbri in VARCHAR2)
>
> RETURN VARCHAR IS tmp VARCHAR(5);
> snip

In your function definition the return datatype is varchar (unbounded). Note that the "IS" indicates the start of the function definition and that the "tmp VARCHAR(5);" is the first declaration in that definition (see PL/SQL syntax Guide).

The describe operation does not have an insight into declaration made *inside* your function definition, so returns the maximum for the varchar datatype on your platform, viz. 2000.
This does not mean that any permanent storage space is allocated unnecessarily and should not cause you any major problems, provided that you remember to specify a "column" format for the returned item if you are using SQL*Plus.

HTH --
Chrysalis

"FABRICATE DIEM PVNC"
(To to protect and to serve)
Motto of the City Guard
Terry Pratchett Received on Wed May 06 1998 - 00:41:18 CDT