UTL_FILE fgetattr with variable

Hi,

i struggling with UTL_FILE.fgetattr. Why is the following code returns: "file doesnt exist" as soon as i'm using the variable "date_convert" instead of the file name directly?

Code:

---

DECLARE
  ex          BOOLEAN;
  file_length  NUMBER;
  blsize      NUMBER;
  date_convert char(250);
  test        char(250);
BEGIN
  date_convert := 'ABC_TEST_' ||
                  to_char(sysdate - 1, '""YYYY-""MM-""DD".csv"');
  test        := 'ABC_TEST_2006-01-11.csv';
  DBMS_OUTPUT.PUT_LINE(date_convert); --shows filename
  DBMS_OUTPUT.PUT_LINE(test); --shows filename
  utl_file.fgetattr('ETL_FOLDER1',
                    date_convert,
                    ex,
                    file_length,
                    blsize);
  IF ex THEN
    dbms_output.put_line('File Exists');
  ELSE
    dbms_output.put_line('File Does Not Exist');
  END IF;
  dbms_output.put_line('File Length: ' || TO_CHAR(file_length));
  dbms_output.put_line('Block Size: ' || TO_CHAR(blsize));
END;
/

---

i did the same thing with UTL_FILE.fopen and that's working fine!
Of course a have the file in the correct folder and i did create the directory object correct. It is working, if i'm using the filename directly.

Oracle: 9.2.0.1
OS: Win XP

Can't reproduce - it works fine for me! (By luck I had an un-patched 9.2.0.1 on XP.)

Does Metalink say anything? In any case I'd be thinking of bringing the version up to date.

In desperation you might try using VARCHAR2 for the file names - that's black magic, not logic.

Hi,

thanks for trying! I still get the same (strange) error. Only if i'm using the variable instead the name directly.

What i'm trying to do is to check whether a file with a specific file name exist in the folder.

Is it correct, that there is no way that i can get the filename directly via UTL_FILE? I have seen some Java code that could do that, but my Java Knowledge is not worth to mention it ;)

I will try to do the same with UTL_FILE.isopen. Maybe that will work.

ahhh, i've found the error:

it was the

Code:

---

date_convert char(250);

---

variable.

If the variable is char <(228) it works and the filename is recognized by the Procedure.

I have no clue why, because 250 doesn't seems to me as such a big value for a character variable...

Quote:

---

Originally Posted by buck

ahhh, i've found the error:

it was the

Code:

---

date_convert char(250);

---

variable.

If the variable is char <(228) it works and the filename is recognized by the Procedure.

I have no clue why, because 250 doesn't seems to me as such a big value for a character variable...

---

In any case, you should be using VARCHAR2 datatype, not CHAR. Never use CHAR for variable length strings.

Might it have something to do with the maximum length of a command under windows . . . . ?

But in any case - why does it work for me?