Clearing buffer in PL/SQL

From: Mageshwar Sivagnanam <magesh_at_pacific.net.sg>
Date: 1996/05/03
Message-ID: <318AA666.11DD_at_pacific.net.sg>#1/1

Hi ne1 out there,

I have created a PL/SQL script as follows. The fundu. objective of this script is to read a set of tables (master-detail) and place/write them into a file. The script goes something like this :

set echo off;
.....
set serverout on;
spool <filename>.dat
declare
  variables declared.
......

  cursors declared.
......

  l_rec varchar2 (2000);
begin
  Loop
    l_rec := null;

    ......
    call to a stored procedure.
    ......
    select * into l_rec from table_A;
    dbms_output.put_line (l_rec);

    l_rec := null;
    select * into l_rec from table_B;
    dbms_output.put_line (l_rec);

    l_rec := null;
    select * into l_rec from table_C;
    dbsm_output.put_line (l_rec);
  end loop;
end;

---

Each record from a table is 240 bytes long. This script will be run on a daily basis on all these tables, for all the records, so as to get the current status of each and every record.

The problem is that I get a buffer overflow. [No I cannot increase the serverout size to the max. value of 1 million bytes, since then I can only get 4000 odd records, by this method.] The core issue here seems to be clearing the buffer (akin to fflush in 'C') and i'm unable to do that. There is an option in SQL*Plus, but again I'm not able to implement the SQL*Plus command in the PL/SQL block.

Any suggestions will be highly appreciated. Since the fundamental issue is to write the records onto a file, one option is to use Pro*C; but is there any other way.?

TIA,
Magesh (magesh_at_pacific.net.sg) Received on Fri May 03 1996 - 00:00:00 CEST