Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I thought about writing a simple tail -f like utility to "trace" the progress of some figures within the database:

create or replace function tail_f return varchar2_tab pipelined as
   n number;
begin

    loop
      exit when ...

      select count(*) into n from ... where ...;

      pipe row(sysdate || ' n= ' || n);
      dbms_lock.sleep(60);
    end loop;

    return;
end tail_f;

And then I'd like to select * from table(tail_f) in SQL*Plus.

In order to fetch the rows one by one, I SET ARRAYSIZE 1. Yet, the records (except the first one) are fetched in pairs.

Is there an explanation for this and how can I get the records as soon as one is piped?

share|improve this question
add comment

2 Answers

up vote 9 down vote accepted

Metalink bug 9103343 states:

This is expected behaviour. SQL*Plus is written in oci and oci has a default prefetch value of 1 row. However prefetch upon a fetch (as opposed to upon an execute) only takes place when you are not performing an array fetch, so when arraysize is 1. Regardless of arraysize the first fetch in the trace is always 1 row as 1 row is prefetched on the execute. Then it either performs a scalar fetch, so one requested row plus one prefetched row, or it performs an array fetch so you see eg : a) arraysize = 1, fetches are: 1, 2, 2, ... b) arraysize = 2, fetches are: 1, 2, 2, ... c) arraysize = 5, fetches are: 1, 5, 5, ...

Also see Metalink doc 1265916.1

share|improve this answer
    
Thans for confirming my suspicion with the metalink note. –  René Nyffenegger Apr 3 '12 at 12:28
add comment

This seems to be a quirk of SQL*Plus and arraysize rather than pipelined functions - the following demonstrates the same effect:

create or replace function pause return integer as
begin
  dbms_lock.sleep(2);
  return 1;
end;
/

select pause from dual connect by level<10;

You can (sort of) workaround the issue by selecting the rows twice and discarding every second one:

select /*+ ordered first_rows(1) */ *
from table(tail_f) cross join (select level from dual connect by level<=2);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.