Hi,
we use UTL_FILE to read files in our PL/SQL Program.
Today we got an exceptional file, which is greater than 2 GB.
our program failed with the error as:

SQLCODE <-29284> SQLERRM <ORA-29284: file read error>

Is there any restriction on the file size we can use with UTL_FILE?
In case if there is any restriction, is there any way to overcome this
restriction?

Thanks,
Giridhar Kodakalla.

If there is such a restriction it is either being caused by a 32-bit
O/S or by 32-bit database software.
UTL_FILE has only record limits, no file limits.
Obviously, using 32-bit Oracle, you can't create, read or write files
greater than 2G.
This restriction can be lifted by installing the 64-bit version of
Oracle.

--
Sybrand Bakker
Senior Oracle DBA

Thanks Sybrand for your reply.
I checked with our DBA and he confirmed that we are on 64 bit.
Do we get such errors even if we are on 64 bit database?
Details of our v$version is as follows, which also confirms we are on
64 Bit:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production

Please guide me further to avoid such errors in future.

Thanks again.
Giridhar Kodakalla

Giridhar wrote:
> Thanks Sybrand for your reply.
> I checked with our DBA and he confirmed that we are on 64 bit.
> Do we get such errors even if we are on 64 bit database?
> Details of our v$version is as follows, which also confirms we are on
> 64 Bit:
>
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
> PL/SQL Release 9.2.0.5.0 - Production
> CORE 9.2.0.6.0 Production
> TNS for Solaris: Version 9.2.0.5.0 - Production
> NLSRTL Version 9.2.0.5.0 - Production
>
> Please guide me further to avoid such errors in future.
>
> Thanks again.
> Giridhar Kodakalla

If you have already checked the obvious i.e. filename and directory
names are valid and you have read/write access to the directory then
make sure that there aren't any lines in the file that exceed the max
linesize. When you do utl_file.fopen and do not specify the max
linesize as the last parameter then it is 1000 bytes by default. You
can specify any value between 1 and 32767.

Regards
/Rauf

"Giridhar" <gkodakalla@gmail.com> wrote in message
news:1136468790.128064.162420@g44g2000cwa.googlegr oups.com...
> Hi,
> we use UTL_FILE to read files in our PL/SQL Program.
> Today we got an exceptional file, which is greater than 2 GB.
> our program failed with the error as:
>
> SQLCODE <-29284> SQLERRM <ORA-29284: file read error>
>
> Is there any restriction on the file size we can use with UTL_FILE?
> In case if there is any restriction, is there any way to overcome this
> restriction?
>
> Thanks,
> Giridhar Kodakalla.
>

Does your filesystem support files greater than 2 GB ? What OS are you using
?