2

I have an table contain three columns ID,Obj_name,Object in a table. Object refers to metadata/File which is located in folder. How can write a script to check what is the file size of each object.

Output like

ID,Obj_name,Object,File_size.

let me know if there is any idea.

Improve this question
2
  • 1
    What does "located in folder" mean? Are you saying that the object column is a bfile? A path to a file on the database server operating system? A path for which an Oracle directory object has been created (and that you have been given permission on)? A path on a different file system? Commented Feb 24, 2016 at 16:46
  • yes, object is an bfile and path to a file is on DB operating system. Commented Feb 24, 2016 at 16:50

2 Answers 2

Reset to default
3

Try this :

 DECLARE v_fexists BOOLEAN; v_file_length NUMBER; v_block_size BINARY_INTEGER; BEGIN UTL_FILE.FGETATTR ('NFS_DIR', 'west.txt', v_fexists, v_file_length, v_block_size); DBMS_OUTPUT.PUT_LINE (v_file_length); END;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Since object is a bfile, you can do something like

CREATE OR REPLACE FUNCTION( p_id IN INTEGER ) RETURN INTEGER IS l_bfile bfile; l_length integer; BEGIN SELECT object INTO l_bfile FROM your_table WHERE id = p_id; DBMS_LOB.OPEN(l_bfile, DBMS_LOB.LOB_READONLY); /* Get the length of the LOB: */ l_length := DBMS_LOB.GETLENGTH(l_bfile); DBMS_LOB.CLOSE(l_bfile); RETURN l_length; END;

and then call that function from your query passing in the id. Note that this example is taken directly from the documentation on LOBs

Improve this answer

11 Comments

ID Object+path object 1 c:/users/ text.txt 2 c:/users/ text1.txt 3 c:/users/ text2.txt 4 c:/users/ text3.txt
ID Object+path object 1 c:/users/ text.txt 2 c:/users/ text1.txt 3 c:/users/ text2.txt 4 c:/users/ text3.txt
@user3280677 - OK. So then object is not a bfile like you originally said? It's a varchar2? Is this a path to a file on the database server? Is there an Oracle directory object for c:\users? Do you have read access on that Oracle directory object?
Sorry i missed it.Path i have given temporary. we do have read access to that object.
@user3280677 - Not sure I follow. What do you mean that the path is temporary? What is the name of the Oracle directory object (that appears in all_directories) that points to c:\users on the database server file system?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.