Re: SET SERVEROUTPUT ON SIZE - only 1000000 bytes?

From: Hakan Eren <heren_at_home.com>
Date: 2000/02/15
Message-ID: <38A97F9A.B6A187E7@home.com>#1/1

Hi Miguel,

Last week I wrote the following for a friend. This describes how you use utl_file by personal oracle. It is same for the server.

To write to a text file from PL/SQL.

1. Add the following entry to your init file (Orawin95\database\initxxxx.ora) utl_file_dir = *

   (Here, you define all accessible folders. * means all folders)

2. Try the following in SQL*PLUS. This is writing. Reading from a text file can be done.

DECLARE
  out_file UTL_FILE.FILE_TYPE;
BEGIN
/*
|| open a new file test.txt for writing in d:\temp folder */
  out_file := UTL_FILE.FOPEN('d:\temp', 'test.txt', 'W'); /*
|| Read all records from part table and write to text file in a loop */
  FOR part_rec IN (SELECT part_no, description, lead_time FROM part) LOOP

      UTL_FILE.PUT_LINE(out_file, part_rec.part_no || ',' ||
                                  part_rec.description || ',' ||
                                  TO_CHAR(part_rec.lead_time));

  END LOOP;
/*
|| Close the file
*/
  UTL_FILE.FCLOSE(out_file);
END;
/

You mwy need to add your error-handling routines.

3. It can be customized easily. However there are some restrictions:

• A line of text has no more than 1022 bytes.

Miguel Nunes Pinto wrote:
>
> Hi Hakan,
>
> Thank you.
>
> >insert into temp_progress
> > values('Var 1: ' || var1 || ' Number 1: ' || TO_CHAR(numvar1) || '
> >Date 1: ' || TO_CHAR(datevar1, 'MMDDYYYY HH24:MI') ....)
> >/
> >
> >Then I insert all information that I need to know.
> >If insert statement is not in a cursor I can use commit.
> >This way I can query the table from another session.
>
> But for what i need this doesn't help me.
> I think i didn't explain well:
> I don't want to insert anything. And it isn't for debug purposes.
> I just want to extract information from some tables and put it into a
> file. And my problem is the limit of the SIZE option of the SET
> SERVEROUTPUT ON command.
> There is any other way to do this (refreshing the buffer?)
>
> Thanks anyway,
>
> Miguel
Received on Tue Feb 15 2000 - 00:00:00 CST