UTL_FILE fgetattr with variable
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: UTL_FILE fgetattr with variable

Hybrid View

  1. #1
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28

    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
    Oracle Version: 9.2.0.1.0
    OS Client & Server: Windows XP Pro

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28
    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.
    Oracle Version: 9.2.0.1.0
    OS Client & Server: Windows XP Pro

  4. #4
    Join Date
    Feb 2005
    Location
    Luxembourg
    Posts
    28
    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...
    Oracle Version: 9.2.0.1.0
    OS Client & Server: Windows XP Pro

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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?
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width