SQL & PL/SQL

Laury wrote:
Hi,
Does soneone know if there is a way to check if a BLOB image is either jpg, png, or gif?

yes, it is possible & MS Windows does it incessantly every second.

First you start with enumerating file content differences between the different image types.

Compare the "magic numbers".

https://en.wikipedia.org/wiki/List_of_file_signatures

Normally, I'd say "use Oracle MultiMedia data type ORD_IMAGE" -- but - that was deprecated/no longer supported.

MK

Hi, and inside a PL/SQL block?

With great difficultly I suspect.

Why don't you just store the image type in a separate column when inserting the blob?

Laury wrote:
Hi, and inside a PL/SQL block?

Use the following:

• TO_BLOB(HEXTORAW())
• DBMS_LOB.SUBSTR()
• DBMS_LOB.COMPARE()

Normally, you want to store the following data inside the table

• actual BLOB data
• MIMEType - This is what you actually need to determine "image/jpeg" vs "image/bmp" (never "file extension")
• (opt)Filename -- or have a process to calculate it dynamically
• (opt)BLOB size -- can be calculated dynamically with DBMS_LOB.GetLength().  This is required by most frameworks.  Storing the value in the table saves a few jiffies

I'm assuming that you need to find out what the MIMEType is.

MK

@Cookiemonster76:

This is OK if know the image type before to store it in a table.

How can I get that type at the time I download that image?

@Mike:

Nice tips, but with these functions, how can I get the image type (jpeg, bmp,...)?

Kind Regards

Laury wrote:

@Mike:
Nice tips, but with these functions, how can I get the image type (jpeg, bmp,...)?
Kind Regards

Read the section "Magic Number in Files" to understand what you need to do.  (ie compare first n bytes to something)

https://en.wikipedia.org/wiki/Magic_number_(programming)

The "List of file signatures" has the something you need to compare to.

https://en.wikipedia.org/wiki/List_of_file_signatures

I leave it as an exercise to the student to create a function that discovers what the MIME Type is for a file based on a "short list" of "known image/* MIMETypes".

You may need to use one or more of the following:  (the student should read the documentation on such functions)

• TO_BLOB(HEXTORAW()) -- static string to BLOB (I haven't tried comparing RAW to BLOB)
• DBMS_LOB.SUBSTR() -- extract "first n bytes"
• DBMS_LOB.COMPARE() -- compare BLOB to BLOB

Final hint:  IN OUT NOCOPY

Beyond that, you should post up "what you have tried".

My $0.02

MK

Hi Mike,

I have seen your post, I will come back to it as soon as I can.

Kind Regards

Hi,

Thanks for all the answers.

@Mike:

You asked: Beyond that, you should post up "what you have tried".

At that stagem I had tried nothing. Just when dowmloading an image from the filesystem to the database using a PL/SQL procedure, I wanted to check what kind of image it was: PNG, JPG,...

So, in fact to be able to identify a mimetype.

I know it is possble when loading it through APEX...

You posted very interesting links, and I feel I am like a student :-)

@Cormaco:

Yes, you posted a quick solution, that is actually good enough for my needs.

@John:

Yes, that's possible... but you didn't explained how it can be catched by PL/SQL up.

Kind Regards