In this blog post, I am sharing a function with which you can check if a file is an image file or not using PL/SQL. The program will check the file by reading its header not the extension name of the file. Currently, the function can identify three types of images which are JPG, PNG, and GIF.
PL/SQL Function to Check if File is an Image File
The following PL/SQL function will read the file header and then will return the image type in varchar2 data type. The function get_image_type takes two arguments, and they are as follows:
- Oracle Directory Object (as i_dir) Learn How to Create Oracle Directory Object.
- Filename (as i_file) file should exist in the directory.
CREATE OR REPLACE FUNCTION get_image_type (i_dir IN VARCHAR2, i_file IN VARCHAR2) RETURN VARCHAR2 AS l_bfile BFILE; l_blob BLOB; l_file_type VARCHAR2 (50); BEGIN DBMS_LOB.createtemporary (l_blob, FALSE); l_bfile := BFILENAME (i_dir, i_file); DBMS_LOB.fileopen (l_bfile, DBMS_LOB.file_readonly); DBMS_LOB.loadfromfile (l_blob, l_bfile, DBMS_LOB.getlength (l_bfile)); DBMS_LOB.fileclose (l_bfile); IF RAWTOHEX (DBMS_LOB.SUBSTR (l_blob, 8, 1)) = '89504E470D0A1A0A' THEN l_file_type := 'PNG'; ELSIF DBMS_LOB.SUBSTR (l_blob, 3, 1) = '474946' THEN l_file_type := 'GIF'; ELSIF RAWTOHEX (DBMS_LOB.SUBSTR (l_blob, 3, 1)) = 'FFD8FF' THEN l_file_type := 'JPG'; ELSE l_file_type := 'The file is not an image.'; END IF; RETURN l_file_type; EXCEPTION WHEN OTHERS THEN IF DBMS_LOB.fileisopen (l_bfile) = 1 THEN DBMS_LOB.fileclose (l_bfile); END IF; DBMS_LOB.freetemporary (l_blob); RETURN 'The file does not exists.'; END get_image_type;
In the below PL/SQL program, change the MY_FILES Oracle directory object with your directory object and change the file name with the file available in MY_FILES directory location.
SET SERVEROUTPUT ON; DECLARE i_image_type VARCHAR2 (50); BEGIN i_image_type := get_image_type ('MY_FILES', 'oracle-logo-gif-to-use.gif'); IF i_image_type IN ('JPG', 'GIF', 'PNG') THEN DBMS_OUTPUT.put_line ('The file is a valid ' || i_image_type || ' image.'); ELSE DBMS_OUTPUT.put_line (i_image_type); END IF; END; /
The file is a valid GIF image. PL/SQL procedure successfully completed.