Re: Playing with PIPELINED Functions

This is probably the result of an optimisation mechanism hidden in OCI8. Even though you have set arraysize one in SQL*Plus, OCI8 always does an array fetch of at least two rows.

You can see this in 'normal' SQL. Set sql_trace true, set arraysize 1, and then execute a multi-row query. When you look at the raw trace file, you will find that most of the FETCH lines all have "r=2" (2 row fetch).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

Rene Nyffenegger wrote in message ...


>Hello

>

>I am fascinated by the pipelined functions offered in 9i. They seem

>to make some queries a little easier. In order to get familiar with

>these, I wrote the following little types an function. The purpose of

>the funcion is to show how many bytes were sent to the client in an

>interval of one second. However -and a little unexpected- Oracle

>seems to deliver the input to a 'pipe row' not until two calls

>to it have been made (except after the first call.). Is there a way

>to change this behaviour (pragma, init parameter, anything?)

>

>Rene

>

>

>

>

>set arraysize 1

>alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';

>

>create or replace type o_date_varchar2 as object (

>  d date,

>  v varchar2(99)

>)

>/

>

>create or replace type t_date_varchar2 as table of o_date_varchar2

>/

>

>create or replace function f_date_varchar2(j in integer) return

t_date_varchar2


>  pipelined

>as

>  a_date_varchar2 o_date_varchar2 := o_date_varchar2(null,null);

>begin

>  for a in 1..j loop

>    select  to_char(value),    sysdate

>      into  a_date_varchar2.v, a_date_varchar2.d

>      from  sys.v_$sysstat

>      where name ='bytes sent via SQL*Net to client';

>

>    pipe row (a_date_varchar2);

>    sys.dbms_lock.sleep(1);

>  end loop;

>  return;

>end;

>/

>

>show errors

>

>select * from table(cast(f_date_varchar2(20) as t_date_varchar2));

>

>

>drop function f_date_varchar2;

>drop type t_date_varchar2;

>drop type o_date_varchar2;

>

>set arraysize 15