Oracle function to return the size of a clob in megabytes

Question

Hi all,

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!

Answer 1

Hope this might help you -

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

Answer 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! :-)