Re: Function Results

In article <31EBCB9B.6662_at_ibm.net>, Tom Gerahty <tg19905_at_glaxo.com> writes:

>We have created a function that returns varchar string.
>The function works fine except it returns a 2000 byte
>string regardless of the length of the result. Is there a
>way to influence the lenght of the output? Doc says the
>return length cannot be provided because system determines
>it. Any thoughts? Has anyone come up with a creative
>get-around? Thanks in advance.
>
>Tom Gerahty
>Glaxo Wellcome
>gerahty_at_ibm.net
>
>

I have run into something like this, see below:

SQL> create table a (col1 varchar2(6));

Table created.

SQL> create or replace package test1 as
  2 function test2 return a.col1%TYPE;   3 pragma restrict_references(test2,wnds,rnds,wnps,rnps);   4 end test1;
  5 /

Package created.

SQL> show errors
No errors.
SQL> create or replace package body test1 as   2 function test2 return a.col1%TYPE as   3 begin
  4 return 'This';
  5 end test2;
  6 end test1;
  7 /

Package body created.

SQL> show errors
No errors.
SQL> desc test1.test2

FUNCTION test1.test2 RETURNS VARCHAR2(6) <<<NOTE

SQL> create or replace view test3 as
  2 select test1.test2 from dual;

View created.

SQL> desc test3

 Name                            Null?    Type
 ------------------------------- -------- ----
 TEST2                                    VARCHAR2(2000)   <<<NOTE????

SQL> create or replace view test4(col1) as   2 select substr(test1.test2,1,6) from dual;

View created.

SQL> desc test4

 Name                            Null?    Type
 ------------------------------- -------- ----
 COL1                                     VARCHAR2(6)       <<<NOTE

So, my "workaround" was to use substrings to resize the response.

Steve Stutheit
Usual disclaimers Received on Fri Jul 19 1996 - 00:00:00 CEST