使用 SQL 返回存储在 oracle blob 列中的文件的可读“文件大小”的优雅方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/840966/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is an elegant way to return a readable 'file size' of a file stored in an oracle blob column using SQL?
提问by Brian
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:
您将如何编写 SQL 查询以更易读的形式获取 blob 的大小？该示例类似于将大型 Word 文档存储在表上的 blob 中。我想执行以下操作：
select fnc_getReadableSize(documents.doc) from documents where id = ?
output: 23.4 MB
输出：23.4 MB
回答by Brian
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
回答by dpbradley
SELECT DBMS_LOB.GETLENGTH(COLUMN_NAME) FROM DOCUMENTS
