Table of Contents

Overall Solution

Utilizing object storage generally includes two sections：

Upload objects to object storage cloud service
Download / display objects from object storage cloud service

Both operations need to integrate APEX with cloud service. At this time, we are using Qcloud service, and their cloud service document has been pulished here.

Oracle APEX is natively using PLSQL as program language. Unfortunately, there is no PLSQL SDK provided by Qcloud service, so we have to choose other SDK. Python SDK is a relatively easy way among all of the available SDKs. To make its invocation available from Oracle APEX, we are publishing custom python script as REST service by python library FastAPI, and integrate the whole process as following image.

Install and Setup Environment

As prerequisition, we already have Oracle 21c XE DB and Oracle 23.1 APEX installed. Upon that, we will further install and setup following components:

Install Python Components

python 3.11 and pip
venv
cos-python-sdk-v5
FastAPI
uvicorn and gunecorn

Use yum or dnf to install python 3.11

Use following script to install Python 3.11 in Oracle Linux.

dnf install python3.11

If there are multiple python3 version installed, use following script to switch to use specified version.

update-alternatives --config pythton3

Enable venv to isolate python context

[oracle@prod apex]$ python -m venv venv
[oracle@prod apex]$ ll
total 0
drwxr-xr-x. 5 oracle oinstall 74 Sep 3 20:21 venv
[oracle@prod apex]$ source venv/bin/activate
(venv) [oracle@prod apex]$

Use yum or dnf to install pip

It is good practise to install pip inside venv. After initiated venv in above step, use following script to install pip.

(venv) [oracle@prod apex]$ python -m pip install --upgrade pip
Requirement already satisfied: pip in ./venv/lib64/python3.11/site-packages (22.3.1)
Collecting pip
  Downloading pip-23.2.1-py3-none-any.whl (2.1 MB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 2.1/2.1 MB 82.8 kB/s eta 0:00:00
Installing collected packages: pip
  Attempting uninstall: pip
    Found existing installation: pip 22.3.1
    Uninstalling pip-22.3.1:
      Successfully uninstalled pip-22.3.1
Successfully installed pip-23.2.1
(venv) [oracle@prod apex]$ pip -V
pip 23.2.1 from /home/oracle/python/apex/venv/lib64/python3.11/site-packages/pip (python 3.11)

Install Qcloud service SDK

As guided by Qcloud, use following script to install Qcloud service Python SDK.

pip install -U cos-python-sdk-v5

Install FastAPI

Use following script to install FastAPI

pip install FastAPI

Install uvicorn and gunicorn

Use following scripts to install uvicorn and gunicorn

pip install uvicorn

pip install gunicorn

Setup Oracle Database

Oracle database with APEX has natively support REST service invocation. It has provided API APEX_WEB_SERVICE and document can be found here. However, to use it in our solution, we have to handle 3 more steps.

Add ACL rule to allow http call to REST service host
Grant read/write access from PLSQL to OS directory
Add additional columns to store OSS information

Add ACL rule to allow outbound http call

Oracle PLSQL has built-in package UTL_HTTP for HTTP call. It is used inside APEX_WEB_SERVICE and foundation to provide HTTP call service.

For security consideration, there are ACL rules to control the outbound HTTP invocation. It is preventing all outbound transactions by default. To enable the capability to use python REST service hosted on the same machine, we use following script to permit the outbound trasaction.

declare
  l_username varchar2(30) := 'APEX_230100';
begin
  dbms_network_acl_admin.append_host_ace(
    host => 'localhost',
    lower_port => 8000,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                        principal_name => l_username,
                        principal_type => xs_acl.ptype_db));

  commit;
end;
/

Ensure it completes without error.

PL/SQL procedure successfully completed.

Grant read/write access from PLSQL to OS directory

Oracle PLSQL has another built-in package UTL_FILE for file read/write. It is validation user’s privilege whether the use has the access to database directory. To enable access to particular OS directory, firstly the OS directory should be defined as a database directory.

CREATE DIRECTORY APEX_COS AS '/home/oracle/python/apex/tmp';

Then it is necessary to grant WRITE and READ access to DB user.

GRANT READ,WRITE ON DIRECTORY APEX_COS TO STUDENT;

Finally, verify whether the access is granted successfully.

SELECT *
FROM   all_tab_privs
WHERE  table_name = 'APEX_COS';

Add additional columns in DB table to store OSS information

In Qcloud, the objects in the OSS bucket can be stored in folders structure, which is easy to manage and maintain. ObjectKey is the full name of the file, include the whole folder. Etag is the uploaded file MD5 value. These 2 attributes are useful, so we will store them in Oracle APEX.

We use APEX object browser to add these 2 additional columns in DB.

Or simply add these 2 columns using SQL

ALTER TABLE "STUDENT"."STDT_TASKS" ADD ("IMAGE_03_KEY" VARCHAR2(4000))
/
ALTER TABLE "STUDENT"."STDT_TASKS" ADD ("IMAGE_03_ETAG" VARCHAR2(4000))
/

Repeat above statements for all attachment field.

Development

We will develop following object in order to articulate the whole solution.

Python script as REST service to handle file upload

Full source code can be found here.

There general processes include:

Define REST service request and response payload. See definition of class File_Upload_Req(BaseModel) and class File_Upload_Res(BaseModel).
Get object storage secret id and secret key. This is confidential, should be handled dynamically and not hardcoded anywhere in the source.
Use python to instantiate CosS3Client.
Upload file to Qcloud object storage bucket, using CosS3Client.put_object.
Delete the file from local server after successful upload.

Python script as REST service to generate download URL

Full source code can be found here.

The general processes include:

Define REST service request and response payload. See definition of class File_Download_Req(BaseModel) and class File_Download_Res(BaseModel).
Similarly, Get object storage secret id and secret key.
Get Qcloud object URL by file’s object key, using CosS3Client.get_presigned_url.

This function will be invoked in SQL, so that each object in the DB table can be queried and URL can be retrieved by SQL directly.

PLSQL procedure to call Python REST service to upload files

Use this PLSQL wrapper procedure to call Python REST service to upload files to OSS.

PLSQL function to call Python REST service to get OSS download URL

Use this PLSQL wrapper function to call Python REST service to get pre-signed download URL.

PLSQL script to tentatively store upload file and invoke python REST service

In processing section, add one more process to handle to upload upload to cloud

Detail process can be found user the definition of Upload Files to COS here.

Deployment

Specify temp directory for upload file

This temp directory should be same to the Oracle DB directory granted to APEX DB user. Also the OS user rununing the python script should have access to this directory.

Place python script and enable REST service

Place python script in OS directory, and use following script to active REST service.

nohup gunicorn qcloud-cos-service:app --workers 4 --worker-class uvicorn.workers.UvicornWorker --bind localhost:8000 &

[2023-09-04 19:34:28 +0800] [3843961] [INFO] Starting gunicorn 21.2.0
[2023-09-04 19:34:28 +0800] [3843961] [INFO] Listening at: http://127.0.0.1:8000 (3843961)
[2023-09-04 19:34:28 +0800] [3843961] [INFO] Using worker: uvicorn.workers.UvicornWorker
[2023-09-04 19:34:28 +0800] [3843968] [INFO] Booting worker with pid: 3843968
[2023-09-04 19:34:28 +0800] [3843969] [INFO] Booting worker with pid: 3843969
[2023-09-04 19:34:28 +0800] [3843970] [INFO] Booting worker with pid: 3843970
[2023-09-04 19:34:28 +0800] [3843971] [INFO] Booting worker with pid: 3843971
[2023-09-04 19:34:34 +0800] [3843969] [INFO] Started server process [3843969]
[2023-09-04 19:34:34 +0800] [3843969] [INFO] Waiting for application startup.
[2023-09-04 19:34:34 +0800] [3843969] [INFO] Application startup complete.
[2023-09-04 19:34:34 +0800] [3843968] [INFO] Started server process [3843968]
[2023-09-04 19:34:34 +0800] [3843968] [INFO] Waiting for application startup.
[2023-09-04 19:34:34 +0800] [3843968] [INFO] Application startup complete.
[2023-09-04 19:34:34 +0800] [3843971] [INFO] Started server process [3843971]
[2023-09-04 19:34:34 +0800] [3843971] [INFO] Waiting for application startup.
[2023-09-04 19:34:34 +0800] [3843971] [INFO] Application startup complete.
[2023-09-04 19:34:34 +0800] [3843970] [INFO] Started server process [3843970]
[2023-09-04 19:34:34 +0800] [3843970] [INFO] Waiting for application startup.
[2023-09-04 19:34:34 +0800] [3843970] [INFO] Application startup complete.

Add PLSQL script in APEX upload/display page

To display the image, firstly in SQL of APEX form, we add column for the URL of all pictures by invoding python service.

select
    TASK_ID,
    TASK_NAME,
    ...
    func_get_oss_url(image_01_key) as image_01_url_hidden,
    func_get_oss_url(image_02_key) as image_02_url_hidden,
    func_get_oss_url(image_03_key) as image_03_url_hidden,
    func_get_oss_url(image_04_key) as image_04_url_hidden,
    func_get_oss_url(image_05_key) as image_05_url_hidden
from STDT_TASKS
where tenant_id = (SELECT tenant_id
             FROM   stdt_users su
             WHERE  su.user_name = :APP_USER)

This may automatically add additonal 5 page items. Then we make these 5 items as HIDDEN type, and also make them Query Only.

Finally, we can add new region to display the image based on the URL retrieved in these hidden columns.

All done, verify the result by uploading a file and re-openning to see the uploaded picture.

Future Enhancement

This is just the basic achiement of using Qcloud service to store picture objects. There are additional valuable enhancements may be included next step.

Manage the Qcloud SDK and local Python REST user credential in more secure way
Upload in trunk for large object
Support for video/audio
Batch archive process of OSS bucket for disaster prevention

Utilize Object Storage in Oracle APEX