Storing file in Oracle database using Oracle Application Express

In this example I will illustrate how to upload a file into a table in oracle database and display the image in Oracle Application Express page. I will also show how to link that file for downloading purposes. I have tried to make it simple to demonstrate in oracle APEX. This feature can be used when you are required to attach a reference file in your application.

Here is the very simple table I have created in Oracle database. You can use TOAD or inbuild Oracle SQL workshop to create this table.

CREATE TABLE  "RTT_FILES"    (
	"FIL_ID" NUMBER(10,0) NOT NULL ENABLE NOVALIDATE,
	"SOURCE" VARCHAR2(3) NOT NULL ENABLE NOVALIDATE,
	"SOURCE_ID" NUMBER(10,0) NOT NULL ENABLE NOVALIDATE,
	"FILE_NAME" VARCHAR2(240), 	"FILE_TYPE" VARCHAR2(48),
	"FILE_DATE" DATE, 	"FILE_LOCATION" VARCHAR2(240),
	"FILE_DESCRIPTION" VARCHAR2(50),
	"FILE_CONTENTS" BLOB,
	CONSTRAINT "FIL_PK" PRIMARY KEY ("FIL_ID") ENABLE NOVALIDATE
)

Your table is ready, now let’s create the page functionality to upload image or document into that table. Assuming you are familiar with APEX and know how to create item in page. Click on edit page and in the left under the item section, click on the Add icon. Select FileBrowse and name it i.e. P12_FILE_UPLOAD, select a region to display this item. Click on next button, follow the prompt to create this item. I have also created couple more text boxes to store additional information about the image file i.e. description, date, and location etc.

Now, once all the associated items are created, I have created a button to process request to upload file and displayed on the top of the region. I have also created a few item level validation control to validate the file browse and other fields.

Let’s create a process to handle the onClick event for upload file button. I have created a process point “ON SUBMIT – after computations and validations” and selected the upload button from dropdown list to action this process.

DECLARE  v_type rtt_files.source%TYPE := 'LIC';

BEGIN
	IF ( :P74_UPLOAD_FILE is not null ) THEN
		INSERT INTO RTT_FILES (source, source_id, file_date,
								file_contents, file_type, file_description)
							SELECT   v_type, :P74_OLIC_ID, sysdate,
								blob_content,mime_type,:P74_FILE_DESCRIPTION
									from wwv_flow_files where name = :P74_UPLOAD_FILE;
		DELETE FROM wwv_flow_files where name = :P74_FILE_NAME;
	END IF;
END;

-- Retrieving the file from the table
SELECT fil_id,
		file_date,
		file_Description,
		file_location,
		file_type,
		'<a href="#OWNER#.ratis_util.display_rtt_image?p_file=' ¦¦ nvl(fil_id,0)¦¦'" target="_blank">Download</a>' "File"
FROM rtt_files
WHERE source_id = :P74_OLIC_IDAND source  = 'LIC'

The above process basically uploading the file into temp location into the server and insert that file into files table then delete it from the temp location.

This will display the image thumbnail and you can also download the image from there.