Re: using packaged functions in a sql to create view

In article <38E91A91.9908808F_at_wellesley.edu>, "John A. Pearson" <jpearson_at_wellesley.edu> wrote:
> hello,
>
> quick question? how do you stop having
> varchar2(4000) length fields be created when
> using a function, that is in a package?
>
> i am using functions as part of a view and
> every function field returns okay, but when i
> describe the table, i get varchar2(4000) field
> spec's.
>
> thanks in advance,
>
> j
>
>

Use substr:

ops$tkyte_at_8i> create or replace function foo return varchar2   2 as
  3 begin
  4 return null;
  5 end;
  6 /

Function created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace view v
  2 as
  3 select foo the_whole_thing, substr( foo, 1, 25 ) shorter_version   4 from dual
  5 /

View created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> desc v

 Name                  Null?    Type
 --------------------- -------- ------------------------------------
 THE_WHOLE_THING                VARCHAR2(4000)
 SHORTER_VERSION                VARCHAR2(25)

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Apr 03 2000 - 19:40:29 CDT