What is an elegant way to return a readable 'file size' of a file stored in an oracle blob column using SQL?

Question

How would you write a SQL query to get the size of blob in a more human readable form? The example would be something like a large word document is being stored in a blob on a table. I would want to execute something like:

select fnc_getReadableSize(documents.doc) from documents where id = ?

output: 23.4 MB

Answer 1

Here's a pure SQL solution to the issue of formatting. It uses a base-1024 logarithm to get the length of the number.

WITH my_values AS (
   SELECT                              4 AS my_value FROM DUAL UNION ALL
   SELECT                             42 AS my_value FROM DUAL UNION ALL
   SELECT                            424 AS my_value FROM DUAL UNION ALL
   SELECT                           4242 AS my_value FROM DUAL UNION ALL
   SELECT                          42424 AS my_value FROM DUAL UNION ALL
   SELECT                         424242 AS my_value FROM DUAL UNION ALL
   SELECT                        4242424 AS my_value FROM DUAL UNION ALL
   SELECT                       42424242 AS my_value FROM DUAL UNION ALL
   SELECT                      424242424 AS my_value FROM DUAL UNION ALL
   SELECT                     4242424242 AS my_value FROM DUAL UNION ALL
   SELECT                    42424242424 AS my_value FROM DUAL UNION ALL
   SELECT                   424242424242 AS my_value FROM DUAL UNION ALL
   SELECT                  4242424242424 AS my_value FROM DUAL UNION ALL
   SELECT                 42424242424242 AS my_value FROM DUAL UNION ALL
   SELECT                424242424242424 AS my_value FROM DUAL UNION ALL
   SELECT               4242424242424242 AS my_value FROM DUAL UNION ALL
   SELECT              42424242424242424 AS my_value FROM DUAL UNION ALL
   SELECT             424242424242424242 AS my_value FROM DUAL UNION ALL
   SELECT            4242424242424242424 AS my_value FROM DUAL UNION ALL
   SELECT           42424242424242424242 AS my_value FROM DUAL UNION ALL
   SELECT          424242424242424242424 AS my_value FROM DUAL UNION ALL
   SELECT         4242424242424242424242 AS my_value FROM DUAL UNION ALL
   SELECT        42424242424242424242424 AS my_value FROM DUAL UNION ALL
   SELECT       424242424242424242424242 AS my_value FROM DUAL UNION ALL
   SELECT      4242424242424242424242424 AS my_value FROM DUAL UNION ALL
   SELECT     42424242424242424242424242 AS my_value FROM DUAL UNION ALL
   SELECT    424242424242424242424242424 AS my_value FROM DUAL UNION ALL
   SELECT   4242424242424242424242424242 AS my_value FROM DUAL
),
exponents AS (
   SELECT my_value,
          TRUNC((LN(my_value) / LN(1024))) AS exponent
     FROM my_values
),
base_values AS (
  SELECT my_value,
         exponent,
         ROUND(my_value / POWER(1024, exponent), 3) AS base_value
    FROM exponents
),
prefixes AS (
   SELECT 0 AS exponent,    'B' AS unit FROM DUAL UNION ALL
   SELECT 1 AS exponent,  'KiB' AS unit FROM DUAL UNION ALL
   SELECT 2 AS exponent,  'MiB' AS unit FROM DUAL UNION ALL
   SELECT 3 AS exponent,  'GiB' AS unit FROM DUAL UNION ALL
   SELECT 4 AS exponent,  'TiB' AS unit FROM DUAL UNION ALL
   SELECT 5 AS exponent,  'PiB' AS unit FROM DUAL UNION ALL
   SELECT 6 AS exponent,  'EiB' AS unit FROM DUAL UNION ALL
   SELECT 7 AS exponent,  'ZiB' AS unit FROM DUAL UNION ALL
   SELECT 8 AS exponent,  'YiB' AS unit FROM DUAL),
formatted AS (
   SELECT b.my_value,
          b.exponent,
          b.base_value,
          TO_CHAR(b.base_value, '999.000') || ' ' || p.unit AS engineering_notation
     FROM base_values b
     JOIN prefixes p
       ON p.exponent = b.exponent)
SELECT * FROM formatted;



  MY_VALUE   EXPONENT BASE_VALUE ENGINEERING_NOTATION
---------- ---------- ---------- ------------------------------------------------
         4          0          4    4.000 B
        42          0         42   42.000 B
       424          0        424  424.000 B
      4242          1      4.143    4.143 KiB
     42424          1      41.43   41.430 KiB
    424242          1    414.299  414.299 KiB
   4242424          2      4.046    4.046 MiB
  42424242          2     40.459   40.459 MiB
 424242424          2    404.589  404.589 MiB
4242424242          3      3.951    3.951 GiB
4.2424E+10          3     39.511   39.511 GiB
4.2424E+11          3    395.107  395.107 GiB
4.2424E+12          4      3.858    3.858 TiB
4.2424E+13          4     38.585   38.585 TiB
4.2424E+14          4    385.846  385.846 TiB
4.2424E+15          5      3.768    3.768 PiB
4.2424E+16          5      37.68   37.680 PiB
4.2424E+17          5    376.803  376.803 PiB
4.2424E+18          6       3.68    3.680 EiB
4.2424E+19          6     36.797   36.797 EiB
4.2424E+20          6    367.972  367.972 EiB
4.2424E+21          7      3.593    3.593 ZiB
4.2424E+22          7     35.935   35.935 ZiB
4.2424E+23          7    359.347  359.347 ZiB
4.2424E+24          8      3.509    3.509 YiB
4.2424E+25          8     35.093   35.093 YiB
4.2424E+26          8    350.925  350.925 YiB

and here's a similar solution in PL/SQL

SET SERVEROUTPUT ON
DECLARE
   FUNCTION get_human_readable_byte_size(
      byte_size_ IN NUMBER) RETURN VARCHAR2;

   FUNCTION get_human_readable_byte_size(
      byte_size_ IN NUMBER) RETURN VARCHAR2
   IS
      exponent_   NUMBER;
      base_value_ NUMBER;
      unit_       VARCHAR2(255);
      formatted_  VARCHAR2(255);
   BEGIN
      exponent_ := TRUNC((LN(byte_size_) / LN(1024)));
      base_value_ := ROUND(byte_size_ / POWER(1024, exponent_), 3);
      SELECT DECODE(exponent_,
                    0, 'B',
                    1, 'KiB',
                    2, 'MiB',
                    3, 'GiB',
                    4, 'TiB',
                    5, 'PiB',
                    6, 'EiB',
                    7, 'ZiB',
                    8, 'YiB',
                    '* 1024 ^ ' || exponent_) INTO unit_ 
        FROM DUAL;
      formatted_ := TO_CHAR(base_value_, '999.000') || ' ' || unit_;
      RETURN formatted_;
   END get_human_readable_byte_size;
BEGIN
   dbms_output.put_line(get_human_readable_byte_size(4));
   dbms_output.put_line(get_human_readable_byte_size(42));
   dbms_output.put_line(get_human_readable_byte_size(424));
   dbms_output.put_line(get_human_readable_byte_size(4242));
   dbms_output.put_line(get_human_readable_byte_size(42424));
   dbms_output.put_line(get_human_readable_byte_size(424242));
   dbms_output.put_line(get_human_readable_byte_size(4242424));
   dbms_output.put_line(get_human_readable_byte_size(42424242));
   dbms_output.put_line(get_human_readable_byte_size(424242424));
   dbms_output.put_line(get_human_readable_byte_size(4242424242));
   dbms_output.put_line(get_human_readable_byte_size(42424242424));
   dbms_output.put_line(get_human_readable_byte_size(424242424242));
   dbms_output.put_line(get_human_readable_byte_size(4242424242424));
   dbms_output.put_line(get_human_readable_byte_size(42424242424242));
   dbms_output.put_line(get_human_readable_byte_size(424242424242424));
   dbms_output.put_line(get_human_readable_byte_size(4242424242424242));
   dbms_output.put_line(get_human_readable_byte_size(42424242424242424));
   dbms_output.put_line(get_human_readable_byte_size(424242424242424242));
   dbms_output.put_line(get_human_readable_byte_size(4242424242424242424));
   dbms_output.put_line(get_human_readable_byte_size(42424242424242424242));
   dbms_output.put_line(get_human_readable_byte_size(424242424242424242424));
   dbms_output.put_line(get_human_readable_byte_size(4242424242424242424242));
   dbms_output.put_line(get_human_readable_byte_size(42424242424242424242424));
   dbms_output.put_line(get_human_readable_byte_size(424242424242424242424242));
   dbms_output.put_line(get_human_readable_byte_size(4242424242424242424242424));
   dbms_output.put_line(get_human_readable_byte_size(42424242424242424242424242));
   dbms_output.put_line(get_human_readable_byte_size(424242424242424242424242424));
   dbms_output.put_line(get_human_readable_byte_size(4242424242424242424242424242));
   dbms_output.put_line(get_human_readable_byte_size(42424242424242424242424242424));
   dbms_output.put_line(get_human_readable_byte_size(424242424242424242424242424242));
   dbms_output.put_line(get_human_readable_byte_size(4242424242424242424242424242424));
END;
/


4.000 B
42.000 B
424.000 B
4.143 KiB
41.430 KiB
414.299 KiB
4.046 MiB
40.459 MiB
404.589 MiB
3.951 GiB
39.511 GiB
395.107 GiB
3.858 TiB
38.585 TiB
385.846 TiB
3.768 PiB
37.680 PiB
376.803 PiB
3.680 EiB
36.797 EiB
367.972 EiB
3.593 ZiB
35.935 ZiB
359.347 ZiB
3.509 YiB
35.093 YiB
350.925 YiB
3.427 * 1024 ^ 9
34.270 * 1024 ^ 9
342.700 * 1024 ^ 9
3.347 * 1024 ^ 10

Answer 2

SELECT DBMS_LOB.GETLENGTH(COLUMN_NAME) FROM DOCUMENTS

Answer 3

The 'readable' part is what I should have emphasized more. Here is what I put together for now.

WITH file_sizes AS
     (SELECT 1048576 MEGABYTE, 1024 KILOBYTE,
             DBMS_LOB.GETLENGTH (BLOB_COLUMN) byte_size
        FROM BLOB_COLUMN)
SELECT (CASE TRUNC (byte_size / MEGABYTE)
           WHEN 0
              THEN TO_CHAR ((byte_size / KILOBYTE), '999,999') || ' KB'
           ELSE TO_CHAR ((byte_size / MEGABYTE), '999,999.00') || ' MB'
        END
       ) display_size
  FROM file_sizes


Output:

DISPLAY_SIZE  
--------------
       1.88 MB
     433 KB   
     540 KB   
     333 KB   
       1.57 MB
       1.17 MB