-
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
-
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
-
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
-
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
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|