Thanks for the response.

Is there any api or function in plsql to get the mime type by taking the extension?
Please check.

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

I know it's now deprecated, however you might be able to make use of Oracle Multimedia...?

https://docs.oracle.com/en/database/oracle/oracle-database/18/aivug/methods-common-all-oracle-multimedia-object-types.html#GUID-1C38DE84-DFEE-416B-A9B4-DD427074BBE1

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 said: "A blob doesn't *have* a mime type - its just binary data."

But DBMS_LOB package has SETCONTENTTYPE and GETCONTENTTYPE that works with Securefile LOBs.
Apparently Securefile LOBs can have some metadata?

That's not detecting mime type - but it could be a way to store mime type on a LOB, right?

-

(
As an aside, I thought this was used by SQL Developer for VS Code, because I tried this query:

select apex_barcode.get_qrcode_png(p_value=>'https://kibeha.dk',p_scale=>2) as qr_blob from dual;

And then in the output grid I right-click, go to Single Record View and click the three dots, which brings up details about the blob, where it states that the mime type is Image/PNG.

I thought that probably apex_barcode was using SETCONTENTTYPE, so VS Code was showing the result of GETCONTENTTYPE, but then I tried:

select dbms_lob.getcontenttype(apex_barcode.get_qrcode_png(p_value=>'https://kibeha.dk',p_scale=>2)) as qr_blob_type from dual;

And that gives error:

ORA-43856: Unsupported LOB type for SECUREFILE LOB operation
ORA-06512: at "SYS.DBMS_LOB", line 2127

So I guess that means that the SQL Developer VS Code extension probably utilizes some way of detecting mime type from the content, as apparently it cannot use GETCONTENTTYPE.
)

Cheerio
/Kim