Re: function signature varchar2 no size

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 27 Jul 1998 19:29:43 GMT
Message-ID: <35bed4e7.24462355@192.86.155.100>

A copy of this was sent to Gregory Lipman <Gregory.Lipman_at_fmr.com> (if that email address didn't require changing) On Mon, 27 Jul 1998 17:38:02 GMT, you wrote:

>Hi
>I wrote a function to be used in a view as follows :
>
>create function abc (par in varchar2)
>RETURN VARCHAR2
>as
>patin varchar(10) := 'abcdef';
>patout varchar(10) := '123456';
>begin
>return (translate(par,patin,patout));
>end;
>
>Now, view is :
>
>create view aaa as
>select abc(field1), ....
>from tab...
>
>When I link this table to MSAccess
>it assumes field name as size 4000 - default for maximum varchar2 width
>and creates MEMO field istead of TEXT of size.
>MEMO in Access is like TEXT in normal databases
>and TEXT is like char
>So you cannot join this field or operate as normal char.
>
>So the problem is in signature of abc function.
>When I try in RETURN datatype to use VARCHAR2(100) instead
>of VARCHAR2 function would not compile.
>I complains on '('.
>
>How do I change this function signature to be RETURN VARCHAR2(100) ????
>Thank,
> Gregory

You can use substr to do it... Consider:

SQL> create function some_string return varchar2   2 as
  3 begin
  4 null;
  5 end;
  6 /
Function created.

SQL> create or replace view some_string_view 2 as select some_string from dual;
View created.

SQL> desc some_string_view;

 Name                            Null?    Type
 ------------------------------- -------- ----
 SOME_STRING                              VARCHAR2(4000)

SQL> create or replace view some_string_view 2 as
3 select substr( some_string, 1, 100 ) some_string from dual; View created.

SQL> desc some_string_view;

 Name                            Null?    Type
 ------------------------------- -------- ----
 SOME_STRING                              VARCHAR2(100)

Hope this helps (and hope it works with MS access, didn't try that)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jul 27 1998 - 14:29:43 CDT

Re: function signature varchar2 no size - question for experts