Re: Function in view allways returns VARCHAR2(4000)

In article <8ivhtp$qbs$1_at_nnrp1.deja.com>,   jeanch_at_my-deja.com wrote:
> Folks,
>
> o I've got a view that call a function like below
> [CREATE OR REPLACE VIEW fish ( NAME, MID) AS SELECT getName(F.NAME,
> F.MID) NAME,F.MID MID FROM FISH_T F]
>
> o getName returns a VARCHAR2(50) however when I do a describe like
> on that view it shows that NAME is of type VARCHAR2(4000);
> basically oracle has overwritten my datatype to be the maximum
 varchar2
>
> o I want the type to stay as I defined it because varchar2(4000)
 causes
> me problems when I try to write reports using MSAccess; Varchar2 is
> converted into a MEMO instead of text; and I know that I cannot join
> table that have memo in them.
>

CREATE OR REPLACE VIEW fish ( NAME, MID) AS SELECT SUBSTR( getName(F.NAME,F.MID),1,50) NAME,

       F.MID MID
  FROM FISH_T F if you use substr with constants, it'll constrain the return type

> o So if you could tell me how I could keep that type safe that'd be
> brilliant. Alternatively how could I make MSAcess understand my types
>
> Cheers
> JC
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

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


Sent via Deja.com http://www.deja.com/
Before you buy.