UTL_FILE.fGetAttr

DECLARE
  lexists      BOOLEAN;
  lfile_len    NUMBER;
  lblocksize   NUMBER;
BEGIN
  UTL_FILE.fgetattr(
    location    => 'a',
    filename    => 'b',
    exists      => lexists,
    file_length => lfile_len,
    blocksize   => lblocksize);
END;

I was trying to use this procedure in a 9i database and kept getting:

PLS-00103: Encountered the symbol ">"...

– complaining about line 8 (the “exists” parameter). If I removed the parameter names, it worked fine. Something was wrong with that “exists” parameter name.
In the 9i and 10g documentation:

UTL_FILE.FGETATTR(
    location    IN VARCHAR2,
    filename    IN VARCHAR2,
    exists      OUT BOOLEAN,
    file_length OUT NUMBER,
    blocksize   OUT NUMBER);

In the 11g documentation:

UTL_FILE.FGETATTR(
    location    IN VARCHAR2,
    filename    IN VARCHAR2,
    fexists     OUT BOOLEAN,
    file_length OUT NUMBER,
    blocksize   OUT BINARY_INTEGER);

Ah – the parameter was actually called “fexists”. Ok. Try again:

PLS-00306: wrong number or types of arguments in call to 'FGETATTR'

Aaarrgh. Time for more googling.
According to psoug:

utl_file.fgetattr(
    location    IN  VARCHAR2,
    filename    IN  VARCHAR2,
    fexists     OUT BOOLEAN,
    file_length OUT NUMBER,
    block_size  OUT BINARY_INTEGER);

Thank goodness I’ve got access to more than just the Oracle docs!

A Good Bad Example
Weird SQL Results

Leave a Reply

Your email address will not be published / Required fields are marked *