Re: Clearing buffer in PL/SQL

In <318AA666.11DD_at_pacific.net.sg>, Mageshwar Sivagnanam <magesh_at_pacific.net.sg> writes:
>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)

Another option is to use a database pipe to send your records from a database procedure to a host c program and then write them to a file. Look up DBMS_PIPE in the Application Developer's Guide for more information and examples.

M.Landa Received on Fri May 03 1996 - 00:00:00 CEST