Create an Upload & Download Application with Oracle Application Express (APEX) and Autonomous Database

Oracle APEX (Oracle Application Express) is a web-based software development system that runs on an Oracle Database. It comes as standard with all Oracle Database editions and Oracle Autonomous Database service and, naturally, is fully supported.

In other words, Oracle APEX is a low-code development platform for building scalable and secure enterprise apps that solve real problems and provide immediate value. Then, you can deploy those apps anywhere. You won’t need to be an expert in a vast array of technologies to deliver excellent solutions.

CodingSight - Create and Upload & Download Application with Oracle Application Express (APEX) and Autonomous Database

Autonomous Database Service does not require you to install anything to start using APEX. After you provision an Autonomous Database, the APEX platform is also installed and ready to use.

The current tutorial will explain how to create an application to Upload and Download files. The data is hosted in the BLOB column of the Autonomous Database.

Access Oracle APEX Platform

This tutorial assumes that you have already possessed some basic knowledge of Oracle Autonomous Data Warehouse (ADW). You may refer to the article about provisioning an Oracle Autonomous Database to refresh your knowledge.

Now, you need to access the Oracle Cloud Infrastructure Console (OCI) and go to the provisioned Autonomous Database instance:

Access APEX Platform

Click on Service Console – you will get to the Service Console page where the ADMIN user of Autonomous Database can monitor the database system and execute administrative tasks.

Navigate to Development. Oracle APEX is already installed on top of Oracle Autonomous Database:

Navigate to Development at Oracle Cloud

You get to the APEX login page.

Log in as an ADMIN user – click Sign In to Administration. The purpose is to create an APEX user. Then you will use it to develop your application.

APEX login page

APEX requires you to create a workspace with a shared work area. Multiple developers can work there when building their applicationі:

APEX requires you to create a workspace with a shared work area

Click on Create Workspace. You need to provide the Autonomous Database user.

Create Workspace at Oracle APEX

Confirm by clicking the Create Workspace button.

Click the Create Workspace button at APEX

Create an APEX Developer

Navigate to the Manage Workspaces menu and choose Manage Developers and Users under the Workspace Actions section:

Create an APEX developer

A dedicated page appears. You will see the two default users there:

  • ADMIN user is on the INTERNAL workspace
  • DEMO user is on the DEMO workspace (you created the workspace in the previous step).

Both users are of the Workspace Administrator account type.

 Workspace Administrator account type

Click Create User in the top-right corner. The Create / Edit User page appears. Here, you have to create a developer who will be assigned to develop the Upload/Download application.

Use the following details:

User Attributes

  • Username: dev_1
  • Email: you should enter a valid email
  • First Name: DEV 1

Account Privileges

  • Workspace: DEMO (this workspace created)
  • Default Schema: DEMO (the database user schema in Autonomous Database)
  • User is an administrator: No
  • User is a developer: Yes
  • Keep other attributes as default

Password (For authentication against workspace user account repository only)

  • Password to use access Workspace
  • Password: Oracle#202105 (Password must satisfy the Oracle Database password policy)
  • Confirm Password: Oracle#202105
  • Require Change of Password on First Use: No (this option would require you to change the password in the first-time login)
 Create / Edit User page at APEX

Click Create User, and you can see the new user in the list. The account type of the new user is Developer.

The account type of the new user is Developer

Click on the ADMIN profile icon on the top-right corner and Sign Out.

Let’s try to access APEX with the new dev_1 user we’ve just created.

On the login page, provide the workspace assigned to your account, enter your username and password. In this test case, log in with dev_1 and password Oracle#202105:

APEX login page

You can see the home page (the Developer account type). Now you are ready to build your application.

Install Upload and Download Application

The APEX platform has some pre-built applications, such as plug-in. You can re-use them quickly.

Click on App Gallery and get the menu. It offers a list of prebuilt apps (sample apps) available in APEX:

Install, Upload and Download application

Click on Sample File Upload and Download > Install App to add this application to your workspace:

Add the application to your APEX workspace

If you are authorized successfully, click Next to install this app.

Install the app

Click on the Run icon to execute the application.

Now, let’s try to upload a sample file with the installed application.

On the left panel, create a new project (e.g., My Upload Project) by clicking on the plus icon:

Create a new project

Fill the mandatory fields:

Fill the mandatory fields

The project is created, and you can upload files. Navigate to the Home page and click on the Plus icon on the Recent Files panel.

Navigate to the Home page and click on the Plus icon on the Recent Files panel

The Upload page appears. Browse to the file you want to upload and click Add File:

Browse to the file you want to upload and click Add File

Note: The file is uploaded and stored in the BLOB column of Autonomous Database.

The file is uploaded and stored in the BLOB column of Autonomous Database

Let’s access your Autonomous Database instance and query the data. You will see two tables created there:

  • EBA_DEMO_FILES to store the information of files you uploaded
  • EBA_DEMO_FILE_PROJECT to store the information of your projects
 Autonomous Database instance

Conclusion

Oracle APEX uses the database encapsulated simple metadata-driven architecture. It provides fast data access, top performance, and scalability out of the box.

With APEX, you can develop and deploy your application anywhere with minimum programming skills. APEX also provides the prebuilt set of sample applications – you only need to install them to start using them.

Dung Dinh

Dung Dinh

BI Specialist, Data Modelling, working as Oracle Consultant in Oracle Cloud.

Leave a Reply

Your email address will not be published. Required fields are marked *