Re: How to set the size of string returned by PL/SQL functions?

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Sat, 06 Oct 2007 18:02:25 -0000
Message-ID: <1191693745.451306.154550@y42g2000hsy.googlegroups.com>

On Oct 6, 8:05 am, "John Heitmuller." <john.heitmul..._at_jrfcorp.net> wrote:
> > but....WHY ? why do you care ? and why do you THINK you want to do it ?
>
> Well, my thought was that if a desc of the view was showing a size of
> 4000 for the column returned by the function that I was consuming 3985
> (4000-15) extra bytes for every record returned by a query of the
> view. Is that correct thinking? Or, is oracle smarter than that?
>
> John

Hi John,

Oracle won't return 4000 bytes if there isn't 4000 bytes to return. Below is a test case that (unscientifically) shows this...kinda...

SQL> create or replace function return_big_string   2 return varchar2 is
  3 begin
  4 return rpad('x',15,'x');
  5 end;
  6 /

Function created.

SQL> create or replace view view_big_string as   2 select return_big_string from dual;

View created.

SQL> desc view_big_string

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

SQL> exit

...

SQL> set serveroutput on
SQL> declare
  2 l_num number;
  3 begin
  4 for i in 1..20 loop

  5      for cur in (select * from view_big_string) loop
  6        null;
  7      end loop;

  8 end loop;
  9 select value

 10      into l_num
 11      from v$mystat
 12      where statistic# = (select statistic#
 13                            from v$statname
 14                            where name = 'bytes sent via SQL*Net to

client');
 15 dbms_output.put_line(l_num);
 16 end;
 17 /
4822

PL/SQL procedure successfully completed.

SQL> Only 4822 bytes is returned, which is far less than 80000 (20 loops * 4000 bytes).

One point that Tom Kyte makes frequently is that an arbitrary varchar2(4000) datatype (either in the table or a view) will screw up third party query tools formatting, so that may be a consideration for determining whether the column size in the view description matters.

HTH, Steve Received on Sat Oct 06 2007 - 13:02:25 CDT