Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Sailesh.

Asked: April 11, 2017 - 10:50 am UTC

Last updated: November 09, 2021 - 3:59 am UTC

Version: 11

Viewed 10K+ times! This question is

You Asked

Hello

Is there any method to get the MIME type of BLOB data file using plsql?

Ex for PDF - application/pdf
for xlsm - application/octet-stream etc

For reference you can take fnd_lobs table in Oracle Applications.

and Connor said...

A blob doesn't *have* a mime type - its just binary data.

So unless you have *stored* the content type, or can derive it (eg by file name extension if you saved the filename as well), then you might be out of luck.

If you have the file name, you could use something like the list at

https://cs.chromium.org/chromium/src/net/base/mime_util.cc?l=201

to derive a mime type.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks but need MIME type from extension

Sailesh Arya, April 11, 2017 - 12:07 pm UTC

Thanks for the response.

Is there any api or function in plsql to get the mime type by taking the extension?
Please check.
Connor McDonald
April 12, 2017 - 5:30 am UTC

We can write one pretty easily

MIME_TYPE

No built-in API, I think...

J. Laurindo Chiappa, April 11, 2017 - 1:55 pm UTC

If I understood your needs correctly, you have inside the BLOB the FULL binary stream coming from a binary file, but NO INFORMATION about the original file extension, AND you want/need to discover the original file type only with this info, right ?
If so, let´s wait for the other answers but imho there is no built-in in RDBMS Oracle for this use, you will need to write some code to get the file signature (aka the 'magic number') to obtain that information, see https://en.wikipedia.org/wiki/List_of_file_signatures for a list of the most common ones....

Regards,

J. Laurindo Chiappa
Connor McDonald
April 12, 2017 - 5:17 am UTC

Agreed.

Oracle Multimedia

John Keymer, July 30, 2018 - 8:47 am UTC

Connor McDonald
August 01, 2018 - 12:36 pm UTC

Thanks for the info

Mime Type is inside the binary stream

Christo Kutrovsky, November 08, 2021 - 4:04 pm UTC

The mime type is more than an etention of the file, it can also be deduced by looking at the binary stream.

The unix 'file' command extracts the mime type based on the binary stream. It uses reference data from /etc/magic or /usr/share/magic and analyzes the contents of the file provided.

So a BLOB can absolutely have a "mime type" and it can be extracted by loading the metadata into the database and "analysing" the file.

This was done by Scott Stephens - link here with full code - https://www.techrepublic.com/article/oracle-tip-put-some-magic-into-your-database/

Note that the blog is from 2004.

The utility "PL/SQL Developper" from Allroundautomations (not to be confused by "SQL Developper" by Oracle) is looking at the BLOB value content and extracting the mime type, then suggests to open the correct application to "load" the file into.
Connor McDonald
November 09, 2021 - 3:59 am UTC

nice info

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here