Getting file size with PL/SQL

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
)
   RETURN PLS_INTEGER
IS 
   TYPE fgetattr_t IS RECORD (
      fexists       BOOLEAN,
      file_length   PLS_INTEGER,
      block_size    PLS_INTEGER
   );

   fgetattr_rec   fgetattr_t;
BEGIN
   UTL_FILE.fgetattr (
      location         => location_in,
      filename         => file_in,
      fexists          => fgetattr_rec.fexists,
      file_length      => fgetattr_rec.file_length,
      block_size       => fgetattr_rec.block_size
   );
   RETURN fgetattr_rec.file_length;
END flength; 

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;


size_in_bytes
-------------
        18568

CAUTION: If you are using 9.2.0.6 then there is a database bug which might not return the exact file size. This bug has been fixed in 9.2.0.7 version.

For more information visit here.