In an Oracle database, the BLOB type was used for long texts, instead of CLOB. I don't know exactly why, it was a decision made by the developer years ago.
Now, I need to identify which BLOB fields in the database have text content and which have other content types (images, documents, etc.).
Is there a safe way to identify the content type of a BLOB field?
You have probably a limited set of file types in your DB - text, PDF, JPEG, PNG, TIFF, ZIP and a few more like old Office format - so you could inspire yourself from the "file" Unix utility and using a table of patterns you can sniff the beginning of the BLOBs in a PL/SQL function and looking at the patterns you want to support, return the corresponding type. It could be the lighest path to a quick solution.