Scribd
Upload
79 views2 pages

Oracle File Storage

The document provides steps to store file data in an Oracle database: 1. Create a table with a BLOB column to store file contents. 2. Create a directory object pointing to the file location and a function to convert files to BLOB for insertion. 3. Test inserting a PDF and JPG file by calling the function, inserting the BLOB, and committing. 4. Query the table to check that the file records were inserted.

Uploaded by

beenu phillips
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
79 views2 pages

Oracle File Storage

The document provides steps to store file data in an Oracle database: 1. Create a table with a BLOB column to store file contents. 2. Create a directory object pointing to the file location and a function to convert files to BLOB for insertion. 3. Test inserting a PDF and JPG file by calling the function, inserting the BLOB, and committing. 4. Query the table to check that the file records were inserted.

Uploaded by

beenu phillips
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

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)
/
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';
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;

You might also like