How to use Drag’n’Drop in Oracle APEX

This tutorial will provide step-by-step instructions to add images to your APEX application via drag-drop. You will use a region-type plug-in named Dropzone created by Daniel Hochleitner, which is available at https://apex.world/ords/f?p=100:700. The plug-in allows you to provide nice-looking drag-drop file uploads with image previews. It is based on the JS Framework dropzone.js, which is a lightweight open-source JavaScript library that turns an HTML element into a dropzone. This means that a user can drag and drop a file onto it, and the file gets uploaded to the server via AJAX. In this section, you will drag and drop four different types of images (jpg, bmp, png, and gif) to an APEX page. You can add these images (provided in the SOURCE CODE) individually or collectively. As these images get uploaded to the dropzone region, their details are appended to an interactive report with respective previews and download links, as shown in the following figure. Initially, the images are stored temporarily in an apex collection (DROPZONE_UPLOAD) that you can make permanent by clicking the Save to Database button.

Let’s get started. 

1.     Import the dropzone2 plug-in using Shared Components | Plug-ins option. During import, you will see the Component Settings page to set values for the plug-in. For now, accept the default values and proceed with the import process. 

2.     Upload the download.png file using Shared Components | Static Application Files option. The file is provided in \SourceCode\DragnDrop folder. 

3.     Create a new Blank page – I created Page 22 for this exercise.

4.     On the Rendering tab in Page Designer, add the following process to the After Header node. The process will delete the apex collection (DROPZONE_UPLOAD) if it exists.

Property	Value
Name	Drop Collection
Type	PL/SQL Code
Location	Local Database
PL/SQL Code	begin    if apex_collection.collection_exists(p_collection_name =>                                                                      'DROPZONE_UPLOAD') THEN         apex_collection.delete_collection(p_collection_name =>                                                                     'DROPZONE_UPLOAD');    end if; end;
Point	After Header

5.     Add a region as follows to hold the Dropzone plug-in.

Property	Value
Title	Drop Zone
Type	Dropzone 2 [Plug-In]
Position	Content Body
Template	Standard

The plug-in comes with various attributes and events that you can set according to your specific requirements. Click the Attributes node under the Drop Zone node region, click a property, and read its details in the Help tab. Here are some attributes that I changed in my application:

Property	Value
Storage Type	APEX Collection
Delete Files	True
Width	100%
Height	200px

 

6.     Add the following hidden item to the page. The item, which stores the name of the uploaded file, will be used in uploading and downloading files.

Property	Value
Name	P22_FILENAME
Type	Hidden
Region	Drop Zone

 

7.     Add the following button that will execute a process to save image files from the APEX collection to the DEMO_PRODUCT_INFO database table – see Step 13.

Property	Value
Button Name	SAVE
Label	Save to Database
Region	Drop Zone
Button Position	Copy
Button Template	Text with Icon
Hot	Yes
Icon	fa-save
Action	Submit Page

8.     Add an interactive report region using the following attributes. The report will be used to show details of the uploaded images. The CSS code specified in the Header Text property ensures that all images are presented in equal size, enclosed in a border. It is referenced in the IMG_URL column’s Static ID attribute – see Step 9.

Property	Value
Title	Uploaded Files
Type	Interactive Report
Location	Local Database
Type	SQL Query
SQL Query	SELECT c001  as filename,  c002 as mime_type,  d001 as date_created,        n001 as file_id, round(dbms_lob.getlength(blob001)/1024) as file_size,        blob001 as file_content,        APEX_PAGE.GET_URL(          P_REQUEST  => 'APPLICATION_PROCESS=GET_DOWNLOAD_IMAGE',          P_ITEMS  => 'P22_FILENAME',          P_VALUES  =>  C001) as img_url,        APEX_PAGE.GET_URL(          P_REQUEST   =>  'APPLICATION_PROCESS=GET_DOWNLOAD_IMAGE',          P_ITEMS   => 'P22_FILENAME',          P_VALUES  =>  C001) as download FROM apex_collections WHERE collection_name = 'DROPZONE_UPLOAD';
Header Text	<style type="text/css">    .a-IRR-table td[headers="IMAGES"] img {      vertical-align: middle;      border:4px solid #CCC;      border-radius: 4px;      height:75px;      width:75px;    } </style>

 

9.    Expand the Columns node, click the IMG_URL column, and set the following attributes. If you omit the HTML Expression value, you will get a URL instead of the image. If the Static ID value is avoided, the images will be presented in their actual sizes.

Property	Value
HTML Expression	<img src="#IMG_URL#">
Static ID	IMAGES

10.   Click the DOWNLOAD interactive report column to set the following attributes. The column is transformed into a link, which will display the download.png image you uploaded in step 2. Clicking the image will download the corresponding file to your computer. You also set the column value (FILENAME) as the value for the P22_FILENAME page item to download the appropriate file. The link calls an AJAX process (GE_DONWLOAD_IMAGE) to get the selected file – see step 12.

Property	Value
Type	Link
Target Type	Page in this application
Page	22
Name (under Set Items)	P22_FILENAME
Value	#FILENAME#
Request (under Advanced)	APPLICATION_PROCESS = GET_DOWNLOAD_IMAGE
Link Text	<img src="#APP_IMAGES#download.png" height="42" width="42" alt="">

11.    Add a dynamic action as follows. The dynamic action uses an event of the Dropzone plug-in to refresh the interactive report after uploading an image.

Property	Value
Name	Refresh Interactive Report
Event	Dropzone File Upload Success
Selection Type	Region
Region	Drop Zone
Action	Refresh
Selection Type	Region
Region	Uploaded File

12.      On the Processing tab, add the following AJAX Callback process, which is used to get and download image files from the apex collection.

Property	Value
Name	GET_DOWNLOAD_IMAGE
Type	PL/SQL Code
Location	Local Database
PL/SQL Code	DECLARE     VfileName varchar2(200);     VmimeType varchar2(200);     VbinaryFile blob; BEGIN     SELECT c001, c002, blob001     INTO   VfileName, VmimeType, VbinaryFile     FROM   apex_collections     WHERE  collection_name = 'DROPZONE_UPLOAD' AND c001 = :P22_FILENAME;       htp.init;     owa_util.mime_header(nvl(VmimeType,'application/octet'),false,'UTF-8');     htp.p('Content-length: ' || dbms_lob.getlength(VbinaryFile));     htp.p('Content-Disposition: attachment; filename="' || VfileName || '"');     owa_util.http_header_close;     wpg_docload.download_file(VbinaryFile);     apex_application.stop_apex_engine; EXCEPTION     when others then         apex_debug.error(sqlerrm);         apex_debug.error(dbms_utility.format_error_stack); END;
Point	Ajax Callback

13.      Finally, add the following process to save images from the apex collection to the database table.

Property	Value
Name	Save Images to Database
Type	PL/SQL Code
Location	Local Database
PL/SQL Code	DECLARE   -- Get images information from saved apex_collection via a cursor   CURSOR cur_images IS     SELECT c001 as filename, c002 as mimetype, d001 as image_last_update,                  blob001 as product_image       FROM apex_collections    WHERE collection_name = 'DROPZONE_UPLOAD'; BEGIN   -- loop through the images cursor   FOR rec_images IN cur_images LOOP     -- insert images into database table     INSERT INTO demo_product_info (         product_name, filename, mimetype, image_last_update, product_image     )     VALUES     ('This is Product Name', rec_images.filename, rec_images.mimetype,         rec_images.image_last_update, rec_images.product_image     );   END LOOP;   -- Delete the collection   if apex_collection.collection_exists(p_collection_name =>                                                                     'DROPZONE_UPLOAD')  THEN     apex_collection.delete_collection(p_collection_name =>                                                                    'DROPZONE_UPLOAD');   END IF; END;
Point	Processing
Success Message	Images Saved Successfully
Error Message	Something went wrong while saving the images
When Button Pressed	SAVE

This procedure of displaying and uploading images doesn’t use MIMETYPE, FILENAME, and IMAGE_LAST_UPDATE columns. It is a simple procedure to upload and display images. However, if you want to display an image file inline; for example, as you do in Document Management System, then you must use MIMETYPE.