UTL_FILE procedure has been enhanced in Oracle 9i and since then it provides a procedure fgetattr to return file size. PL/SQL Evangelist Steven Feuerstein has come up with this function that returns file length.
CREATE OR REPLACE FUNCTION flength (
location_in IN VARCHAR2,
file_in IN VARCHAR2
TYPE fgetattr_t IS RECORD (
location => location_in,
filename => file_in,
fexists => fgetattr_rec.fexists,
file_length => fgetattr_rec.file_length,
block_size => fgetattr_rec.block_size
Pass a directory structure name as first parameter and the file name as the second parameter.
Create directory TEST as '/tmp';
select flength('TEST','test.txt') size_in_bytes from dual;
CAUTION: If you are using 18.104.22.168 then there is a database bug which might not return the exact file size. This bug has been fixed in 22.214.171.124 version.
For more information visit here.