RE: UTL_FILE limitation

Kieran, I have to wonder... why are you using utl_file instead of sql*loader?

Lisa Koivu
Oracle Database Tank
Fairfield Resorts, Inc.
954-935-4117

> -----Original Message-----
> From: Kieran Murray [SMTP:kieran.murray_at_norkom.com]
> Sent: Friday, April 12, 2002 2:13 PM
> To: Multiple recipients of list ORACLE-L
> Subject: UTL_FILE limitation
>
> Hi,
> I'm trying to load a table with 37 columns, from a flat file. I'm getting
> ORA-06502 error after about 400 rows have been added to the file. When I
> read read from other tables this works fine (although they don't have as
> many columns). I've tried using the overloaded UTL_FILE.FOPEN procedure
> (setting max_linesize to 32767) but to no avail. The strange thing is, if
> I
> run a wc -c on any of the lines created they average about 170 bytes,
> which
> doesn't even come close to the 1023 bytes limit. Would anyone know of a
> workaround?
>
> Cheers,
> Kieran Murray
> Norkom Technologies,
> 43 Upper Mount Street,
> Dublin 2, Ireland
>
> P.S. relevant commands are :
> fileid := utl_file.fopen(file_dir,file_name,'w',32767);
> v_data := <table data which is tab-delimited>;
> utl_file.put_line(fileid, v_data);
> utl_file.fclose(fileid);
>
> Table description is:
> CREATE TABLE CDM_RESULTS_FILE (
> CUS_ID NUMBER NOT NULL,
> BAN NUMBER NOT NULL,
> CTN VARCHAR2 (12),
> CNAME1 VARCHAR2 (100),
> CNAME2 VARCHAR2 (100),
> CMTITLE VARCHAR2 (50),
> CMADD1 VARCHAR2 (50),
> CMADD2 VARCHAR2 (50),
> CMCNTY VARCHAR2 (50),
> CMCITY VARCHAR2 (50),
> CMZIP VARCHAR2 (50),
> CMSTATE VARCHAR2 (50),
> CATTN VARCHAR2 (50),
> CWPH VARCHAR2 (50),
> CWPHEX VARCHAR2 (50),
> CHPH VARCHAR2 (50),
> COTHPHON VARCHAR2 (50),
> SCORE NUMBER,
> SCORE10 NUMBER,
> SCORE20 NUMBER,
> SCORE50 NUMBER,
> SCORE100 NUMBER,
> REASON1 NUMBER,
> REASON2 NUMBER,
> REASON3 NUMBER,
> REASON4 NUMBER,
> REASON5 NUMBER,
> REASON6 NUMBER,
> REASON7 NUMBER,
> REASON8 NUMBER,
> REASON9 NUMBER,
> REASON10 NUMBER,
> MCABA NUMBER,
> MSRATA NUMBER,
> MSRTA NUMBER,
> MSRACA NUMBER,
> MROAM NUMBER)
>
>
>
>
> The information contained in this e-mail transmission is confidential
> and may be privileged. It is intended only for the addressee(s) stated
> above. If you are not an addressee, any use, dissemination, distribution,
> publication, or copying of the information contained in this e-mail is
> strictly prohibited. If you have received this e-mail in error, please
> immediately notify our IT Department by telephone at 353-1-6769333
> or e-mail internal.support_at_norkom.com and delete the e-mail from your
> system.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Kieran Murray
> INET: kieran.murray_at_norkom.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Koivu, Lisa
  INET: lisa.koivu_at_efairfield.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).