Home » SQL & PL/SQL » SQL & PL/SQL » wrong filesize with utl_file.fgetattr ?
icon5.gif  wrong filesize with utl_file.fgetattr ? [message #231283] Mon, 16 April 2007 04:27 Go to next message
SaraM
Messages: 9
Registered: March 2007
Junior Member
Hi,

need a little help:

the procedure utl_file.fgetattr always returns a wrong filesize, for example:

the real filesize of a file is about 4kB, the procedure returns about 992526851. Or: the real size is 27kb, returning 1157874436.

What is wrong, do I have to convert the number in any way.... ?

Thank you very much !!


Oracle-Version is 9.2
Re: wrong filesize with utl_file.fgetattr ? [message #231500 is a reply to message #231283] Tue, 17 April 2007 05:28 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Check This

Read the topic Getting Attributes of a File
There is a good example by Steven Feuerstein on how to do this.

Good Luck
Re: wrong filesize with utl_file.fgetattr ? [message #231658 is a reply to message #231500] Tue, 17 April 2007 23:16 Go to previous messageGo to next message
SaraM
Messages: 9
Registered: March 2007
Junior Member
Hi tahpush,

thank you for your reply.

But my function is the same as in the example...

Could it be a bug in the Oracle-Version 9.2?
Re: wrong filesize with utl_file.fgetattr ? [message #231691 is a reply to message #231658] Wed, 18 April 2007 02:17 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

There are some reported bugs on the utl_file package.
Oracle have released patches to fix this.

Have a look at metalink for the bugreports.


Re: wrong filesize with utl_file.fgetattr ? [message #232163 is a reply to message #231658] Thu, 19 April 2007 12:30 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
It works fine for me in Oracle 9.2 Enterprise Edition on Windows XP. Are you sure you are comparing the same file in the same directory? Can you post a copy and paste showing the complete code, including directory object creation and comparison to operating system, like below?

D:\oracle2>dir daylist.txt
 Volume in drive D has no label.
 Volume Serial Number is 70AE-6E52

 Directory of D:\oracle2

04/19/2007  10:04 AM             9,145 daylist.txt
               1 File(s)          9,145 bytes
               0 Dir(s)  10,050,473,984 bytes free

D:\oracle2>


scott@ORA92> CREATE OR REPLACE DIRECTORY my_dir AS 'D:\oracle2'
  2  /

Directory created.

scott@ORA92> DECLARE
  2    v_fexists      BOOLEAN;
  3    v_file_length  NUMBER;
  4    v_block_size   BINARY_INTEGER;
  5  BEGIN
  6    UTL_FILE.FGETATTR
  7  	 ('MY_DIR', 'daylist.txt', v_fexists, v_file_length, v_block_size);
  8    DBMS_OUTPUT.PUT_LINE (v_file_length);
  9  END;
 10  /
9145

PL/SQL procedure successfully completed.

scott@ORA92> CREATE OR REPLACE FUNCTION flength (
  2  	location_in   IN   VARCHAR2,
  3  	file_in       IN   VARCHAR2
  4  )
  5  	RETURN PLS_INTEGER
  6  
  7  IS
  8  	TYPE fgetattr_t  IS RECORD (
  9  	   fexists	 BOOLEAN,
 10  	   file_length	 PLS_INTEGER,
 11  	   block_size	 PLS_INTEGER
 12  	);
 13  
 14  
 15  	fgetattr_rec   fgetattr_t;
 16  BEGIN
 17  	UTL_FILE.fgetattr (
 18  	   location	    => location_in,
 19  	   filename	    => file_in,
 20  	   fexists	    => fgetattr_rec.fexists,
 21  
 22  	   file_length	    => fgetattr_rec.file_length,
 23  	   block_size	    => fgetattr_rec.block_size
 24  	);
 25  	RETURN fgetattr_rec.file_length;
 26  END flength;
 27  /

Function created.

scott@ORA92> SELECT flength ('MY_DIR', 'daylist.txt') FROM DUAL
  2  /

FLENGTH('MY_DIR','DAYLIST.TXT')
-------------------------------
                           9145

scott@ORA92>

Re: wrong filesize with utl_file.fgetattr ? [message #232258 is a reply to message #232163] Thu, 19 April 2007 23:25 Go to previous messageGo to next message
SaraM
Messages: 9
Registered: March 2007
Junior Member
Hi Scott,

thank you for your help!

I created the directorys with this commands:

CREATE OR REPLACE DIRECTORY EDIBAK AS 'c:\tmp';
GRANT ALL ON DIRECTORY EDIBAK TO PUBLIC;

CREATE OR REPLACE DIRECTORY EDISVR AS 'c:\tmp';
GRANT ALL ON DIRECTORY EDISVR TO PUBLIC;

...

----------------------------------------------

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\tmp>dir
Volume in Laufwerk C: hat keine Bezeichnung.
Volumeseriennummer: 007D-7C1D

Verzeichnis von C:\tmp

19.04.2007 08:53 <DIR> .
19.04.2007 08:53 <DIR> ..
18.04.2007 06:47 24.770 EDI_OLD_1.dat
18.04.2007 06:48 22.007 EDI_OLD_11.dat
18.04.2007 06:48 21.256 EDI_OLD_12.dat
18.04.2007 06:48 22.386 EDI_OLD_14.dat
18.04.2007 06:48 24.429 EDI_OLD_15.dat
18.04.2007 06:48 24.581 EDI_OLD_18.dat
18.04.2007 06:48 25.087 EDI_OLD_21.dat
18.04.2007 06:49 22.113 EDI_OLD_25.dat
18.04.2007 06:47 22.283 EDI_OLD_5.dat
18.04.2007 06:47 22.050 EDI_OLD_8.dat
.....
45 Datei(en), 952.391 Bytes
2 Verzeichnis(se), 3.542.843.392 Bytes frei


----------------------------------------------

in my Package is this function:

FUNCTION getFileSize(sDIRECTORY IN VARCHAR2,sFILENAME IN VARCHAR2) RETURN NUMBER IS
bEx BOOLEAN;
nLen NUMBER;
nBlock NUMBER;
BEGIN
dbms_output.put_line ('DIR ' || sDIRECTORY);
dbms_output.put_line ('FILE: ' || sFILENAME);

utl_file.fgetattr(sDIRECTORY, sFILENAME, bEx, nLen, nBlock);
if bEx then
dbms_output.put_line('EX: TRUE');
dbms_output.put_line('L:' || to_char(nLen));
dbms_output.put_line('B:' || to_char(nBlock));
else
dbms_output.put_line('EX: FALSE');
end if;

return nLen;
END;


the output is this:

DIR: EDIBAK
FILE: EDI_OLD_1.dat
EX: TRUE
L:805552900
B:0




Re: wrong filesize with utl_file.fgetattr ? [message #232521 is a reply to message #232258] Fri, 20 April 2007 18:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SaraM,

My name is Barbara, not Scott. I use the scott schema for demos. I searched around a bit and found that there is a bug where utl_file.fgetattr produces the wrong filesize in Oracle 9.2.0.6 and that it was fixed in Oracle 9.2.0.7. I am using Oracle 9.2.0.1. The bug may have existed in different versions for different operating systems. The difference in versions was demonstrated by Todd Barry in the following thread on the OTN forums:

http://forums.oracle.com/forums/thread.jspa?messageID=1131461&#1131461

Barbara
Re: wrong filesize with utl_file.fgetattr ? [message #232794 is a reply to message #232521] Mon, 23 April 2007 05:13 Go to previous message
SaraM
Messages: 9
Registered: March 2007
Junior Member
Hi Barbara, thank you very much, we will patch it!
Previous Topic: display of duplicate rows from same table
Next Topic: SQL Doubt: Update of salary ALL employees by 10%
Goto Forum:
  


Current Time: Sat Dec 10 20:56:23 CST 2016

Total time taken to generate the page: 0.20632 seconds