Hi Jeroen!
As far as I know this (operating system specific) number
limits the total amount of characters in one
single line including the new line character.
So if you are using 'utl_file.put' only, you're appending
characters to the very same single line, as soon as you exceed the limit
Oracle will raise an error (if no error is raised, it is a bug).
Do you have embedded newline characters in your long variable?
Do you get the same error, when replacing 'utl_file.put' by
'utl_file.put_line' ?
Why do you use utl_file.put instead of utl_file.put_line?
With utl_file.puts you cannot put more characters in one line than the
allowed by the limit.
A solution would be to write the output in pieces, each piece
as in the following procedure:
create or replace
procedure WriteToFile(FileHandle in utl_file.file_type in, TextToWrite in
long) is
rest long;
limit pls_integer:=1023;
begin
rest := TextToWrite;
while rest is not null loop
-- you can do substr on longs in PL/SQL
utl_file.put_line(FileHandle,substr(rest,1,limit-1));
rest := substr(rest,limit);
end loop;
end;
/
Regards,
Andreas
-----Original Message-----
Sent: Monday, March 24, 2003 9:49 AM
To: Multiple recipients of list ORACLE-L
When I have for example a long variable with lengths between 1000 and 3500
chars
and I write with use of utl_file.put I can process lots of rows succesfully
and
then I get write error.
Are you suggesting the 1023 limit is a global setting that is also
applicable for utl_file.put ?
Details: oracle 7.3.4 hp-ux 10.20 (cannot change the limit like in oracle 8)
Tia,
Jeroen
-----Oorspronkelijk bericht-----
Van: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
Verzonden: vrijdag 21 maart 2003 16:54
Aan: Multiple recipients of list ORACLE-L
Onderwerp: RE: utl_file.put error
The default maximum number of characters per line for UTL_FILE.FOPEN is
1023.
When using repeatedly UTL_FILE.PUTS, it appends characters to the
current output line, Oracle counts the the total number of characters
per line written, and it seems, your program exceeded this maximum.
Either issue a UTL_FILE.PUT_LINE before you reach this limit,
(this will start a new line and reset our imaginary internal character
counter)
or use an overloaded UTIL_FILE.FOPEN version,
which allows you to set the maximum number of characters per line
explicitly.
Excerpt from oracle documentation:
UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER -- up to 32767 characters per line
(including the new line character)
);
HTH
Andreas
-----Original Message-----
Sent: Thursday, March 20, 2003 5:09 PM
To: Multiple recipients of list ORACLE-L
[BUG:458336] <ml2_documents.showDocument?p_id=458336&p_database_id=BUG>
UTL_FILE.WRITE_ERROR RAISED DOING MULTIPLE PUTS OF <1023 CHARS
EACH (TOTAL>1023)
[BUG:385936] <ml2_documents.showDocument?p_id=385936&p_database_id=BUG>
UTL_FILE.PUT WITH BUFFER > 1023 CHARACTERS CAUSES ERROR
I'm not able to read these bugreports in metalink, anybody familiar with
these problems?
Details: Oracle 7.3.4 HP-UX 10.20
Tia,
Jeroen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jeroen van Sluisdam
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.net
--
Author: Jeroen van Sluisdam
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
