0

I have a table, let's call it attachments.

I have an attachment id and the attachment filedata. However, unfortunately, when the files are uploaded, the size of the file is never put into a filesize field.....so I have a bit of a predicament.

Is there a way I could do a query to calculate the size of a. a files size in megabytes and b. to get the total size of all files.

Furthemore, I'm also aware that if I have a huge number of attachments then it'd be a slow query. So I'll be caching the query result in my webapp. (refresh every 30mins or so via a cron)

Any help would be hugely appreciated.

Thanks!

Improve this question

2 Answers 2

Reset to default
2

For those interested, I found the solution:

create or replace function filesize_in_mb(filedata in blob) return float is Result float; begin select ROUND(((dbms_lob.getlength(filedata)/1024/1024) * 2), 2) into Result from dual; return Result; end filesize_in_mb;

I'm multiplying by two because the database i'm working with is in utf-16.

And to call it:

SELECT filesize_in_mb(a.filedata) as filesize FROM file_attachment a

All rather simple really! :-)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Hope this might help you -

http://www.java2s.com/Code/Oracle/System-Packages/ThisblockdemonstratesDBMSLOBGETLENGTH.htm

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.