Re: Spooling large records

A copy of this was sent to "Jeremy Ovenden" <jovenden_at_hazelweb.co.uk> (if that email address didn't require changing) On Sun, 7 Nov 1999 11:15:59 -0000, you wrote:

>If my understanding is correct, in sqlplus you cannot set linesize param to
>more than 255 chars and that utl_file in pl/sql is restricted to a line size
>of 255 chars too.
>

sqlplus

tkyte_at_8.0> set linesize 50000
linesize option 50000 out of range (1 through 32767) tkyte_at_8.0>

the limit is 32k/line in SQL plus. A single field will be 4000 bytes or less (excepting clobs and longs) meaning if you plan on concatenating a bunch of fields together like:

select a || ',' || b || ',' || .... || ',' || z from T;

the sum of the lengths of the columns for any given row cannot exceed 4000 bytes.

utl_file in 8.0.6 defaults to 1022 but utl_file.fopen has a 4'th parameter:

FUNCTION FOPEN RETURNS RECORD

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
   ID                           BINARY_INTEGER          OUT
 LOCATION                       VARCHAR2                IN
 FILENAME                       VARCHAR2                IN
 OPEN_MODE                      VARCHAR2                IN
 MAX_LINESIZE                   BINARY_INTEGER          IN

that lets you set the linesize upto 32k (this was added in 8.0.5)

>I need to create a flat file (solaris 2.6) which contains data from the
>oracle (8.0.6) database but the record size created will likely be circa
>1000 characters. What is the best way of creating this?
>
>Jeremy Ovenden
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Nov 07 1999 - 08:57:59 CST