How to find the size of a file by plsql?

Asked

Viewed 134 times

2

Hello, I’m having doubts about how to find the file size in the PL/SQL language.

I’ve searched several places and found this solution as a pattern:

declare
  vExists     BOOLEAN;
  vFileLength NUMBER;
  vBlocksize  NUMBER;
begin
  UTL_FILE.FGETATTR('directory_location',
                    'file.extension',
                    vExists,
                    vFileLength,
                    vBlocksize);
  dbms_output.put_line('vFileLength :' || vFileLength);
  dbms_output.put_line('vBlocksize :' || vBlocksize);
  dbms_output.put_line('MB :' || (vFileLength / 1000000));
end;

But it returns me null in all cases.

Would anyone have another way to find out the size of a file in pl?

Thanks in advance! :)

oracle pl-sql

  • That’s a good question, I’d also like to know.

  • What Exists has returned ?

  • Basically this is just how you are using, I will post the Function that I use and have no problems, maybe your file is not being found.

1 answer

0

It seems that the function is correct, the problem may be related to the path of the file directories, it follows the function I use to return the size in bytes.

SELECT flength('diretorio_teste','teste.txt') size_in_bytes FROM dual;

-- Function
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;
/

Browser other questions tagged oracle pl-sql

You are not signed in. Login or sign up in order to post.