Press "Enter" to skip to content

How to Insert a File in Oracle Database?

In this tutorial, you will learn how to insert a file in the Oracle database. The type of file can be a PDF, an Image, or any document. I am using the BLOB data type in Oracle table to demonstrate the following example.

Follow These Steps to Insert a File (PDF, Image, Docx, Xlsx, etc.) in the Oracle Database Table’s BLOB Column

  1. Create a table in Oracle database schema with a BLOB column, as shown in the below example.
CREATE TABLE ext_files (
file_name VARCHAR2 (1000),
file_content BLOB)
/
  1. Then create a database directory object from where you want to insert the files, as shown in below example.
CREATE OR REPLACE DIRECTORY PDF_FILES As 'C:\my_pdf_files';
  1. Now create the following function to convert a file into a BLOB data type. But make sure that you change the PDF_FILES directory name in the below function with the name you created the directory object.
CREATE OR REPLACE FUNCTION file_to_blob(p_file_name VARCHAR2) RETURN BLOB AS
dest_loc BLOB := empty_blob();
src_loc BFILE := BFILENAME('PDF_FILES', p_file_name);
BEGIN
DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

DBMS_LOB.CREATETEMPORARY(
lob_loc => dest_loc
, cache => true
, dur => dbms_lob.session
);

DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);

DBMS_LOB.LOADFROMFILE(
dest_lob => dest_loc
, src_lob => src_loc
, amount => DBMS_LOB.getLength(src_loc));

DBMS_LOB.CLOSE(dest_loc);
DBMS_LOB.CLOSE(src_loc);

RETURN dest_loc;
END file_to_blob;
/

Now you are ready for the test to insert a file in Oracle database table.

Test to Insert a PDF File

DECLARE
v_blob BLOB;
BEGIN
v_blob := file_to_blob ('emp.pdf');

INSERT INTO ext_files
VALUES ('emp.pdf', v_blob);

COMMIT;
END;
/

Test to Insert an Image File (JPG)

DECLARE
v_blob BLOB;
BEGIN
v_blob := file_to_blob ('oracle-18c-install-step-1.JPG');

INSERT INTO ext_files
VALUES ('oracle-18c-install-step-1.JPG', v_blob);

COMMIT;
END;
/

Check the Table for Records

SELECT * FROM EXT_FILES;

Output

insert a file in Oracle database. A file can be a PDF, Image, etc.

See also:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
0 Comments
Inline Feedbacks
View all comments