{"id":969,"date":"2023-09-04T21:42:32","date_gmt":"2023-09-04T13:42:32","guid":{"rendered":"https:\/\/www.linyinghao.cn\/wordpress\/?p=969"},"modified":"2023-09-05T17:27:14","modified_gmt":"2023-09-05T09:27:14","slug":"utilize-object-storage-in-oracle-apex","status":"publish","type":"post","link":"https:\/\/www.linyinghao.cn\/wordpress\/?p=969","title":{"rendered":"Utilize Object Storage in Oracle APEX"},"content":{"rendered":"\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_74 ez-toc-wrap-left counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Overall_Solution\" >Overall Solution<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Install_and_Setup_Environment\" >Install and Setup Environment<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Install_Python_Components\" >Install Python Components<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Setup_Oracle_Database\" >Setup Oracle Database<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Development\" >Development<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Python_script_as_REST_service_to_handle_file_upload\" >Python script as REST service to handle file upload<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Python_script_as_REST_service_to_generate_download_URL\" >Python script as REST service to generate download URL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#PLSQL_procedure_to_call_Python_REST_service_to_upload_files\" >PLSQL procedure to call Python REST service to upload files<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#PLSQL_function_to_call_Python_REST_service_to_get_OSS_download_URL\" >PLSQL function to call Python REST service to get OSS download URL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#PLSQL_script_to_tentatively_store_upload_file_and_invoke_python_REST_service\" >PLSQL script to tentatively store upload file and invoke python REST service<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Deployment\" >Deployment<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Specify_temp_directory_for_upload_file\" >Specify temp directory for upload file<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Place_python_script_and_enable_REST_service\" >Place python script and enable REST service<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Add_PLSQL_script_in_APEX_uploaddisplay_page\" >Add PLSQL script in APEX upload\/display page<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/www.linyinghao.cn\/wordpress\/?p=969\/#Future_Enhancement\" >Future Enhancement<\/a><\/li><\/ul><\/nav><\/div>\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Overall_Solution\"><\/span>Overall Solution<span class=\"ez-toc-section-end\"><\/span><\/h1>\n\n\n\n<p>Utilizing object storage generally includes two sections\uff1a<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Upload objects to object storage cloud service<\/li>\n\n\n\n<li>Download \/ display objects from object storage cloud service<\/li>\n<\/ul>\n\n\n\n<p>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 <a href=\"https:\/\/cloud.tencent.com\/document\/product\/436\">here<\/a>.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230901141420861.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1163\" height=\"835\" src=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230901141420861.png\" alt=\"\" class=\"wp-image-981\"\/><\/a><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Install_and_Setup_Environment\"><\/span>Install and Setup Environment<span class=\"ez-toc-section-end\"><\/span><\/h1>\n\n\n\n<p>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:<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Install_Python_Components\"><\/span>Install Python Components<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>python 3.11 and pip<\/li>\n\n\n\n<li>venv<\/li>\n\n\n\n<li>cos-python-sdk-v5<\/li>\n\n\n\n<li>FastAPI<\/li>\n\n\n\n<li>uvicorn and gunecorn<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Use yum or dnf to install python 3.11<\/h3>\n\n\n\n<p>Use following script to install Python 3.11 in Oracle Linux.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">dnf install python3.11<\/mark><\/strong><\/code><\/pre>\n\n\n\n<p>If there are multiple python3 version installed, use following script to switch to use specified version.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code><strong><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">update-alternatives --config pythton3<\/mark><\/strong><\/code><\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Enable venv to isolate python context<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;oracle@prod apex]$<strong> <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">python -m venv venv<\/mark><\/strong>\n&#91;oracle@prod apex]$ ll\ntotal 0\ndrwxr-xr-x. 5 oracle oinstall 74 Sep 3 20:21 venv\n&#91;oracle@prod apex]$ source venv\/bin\/activate\n(venv) &#91;oracle@prod apex]$<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Use yum or dnf to install pip<\/h3>\n\n\n\n<p>It is good practise to install pip inside venv. After initiated venv in above step, use following script to install pip.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>(venv) &#91;oracle@prod apex]$ <strong><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">python -m pip install --upgrade pip<\/mark><\/strong>\nRequirement already satisfied: pip in .\/venv\/lib64\/python3.11\/site-packages (22.3.1)\nCollecting pip\n  Downloading pip-23.2.1-py3-none-any.whl (2.1 MB)\n     \u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501 2.1\/2.1 MB 82.8 kB\/s eta 0:00:00\nInstalling collected packages: pip\n  Attempting uninstall: pip\n    Found existing installation: pip 22.3.1\n    Uninstalling pip-22.3.1:\n      Successfully uninstalled pip-22.3.1\nSuccessfully installed pip-23.2.1\n(venv) &#91;oracle@prod apex]$ pip -V\npip 23.2.1 from \/home\/oracle\/python\/apex\/venv\/lib64\/python3.11\/site-packages\/pip (python 3.11)\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Install Qcloud service SDK<\/h3>\n\n\n\n<p>As guided by Qcloud, use following script to install Qcloud service Python SDK.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">pip install -U cos-python-sdk-v5<\/mark><\/strong><\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Install FastAPI<\/h3>\n\n\n\n<p>Use following script to install FastAPI<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\"><strong>pip install FastAPI<\/strong><\/mark><\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Install uvicorn and gunicorn<\/h3>\n\n\n\n<p>Use following scripts to install uvicorn and gunicorn<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><strong><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">pip install uvicorn<\/mark><\/strong><\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code><strong><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">pip install gunicorn<\/mark><\/strong><\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Setup_Oracle_Database\"><\/span>Setup Oracle Database<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Oracle database with APEX has natively support REST service invocation. It has provided API APEX_WEB_SERVICE and document can be found <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/apex\/23.1\/aeapi\/APEX_WEB_SERVICE.html#GUID-B95EA4B6-950D-419D-A2B0-CDB254E1E9F0\">here<\/a>. However, to use it in our solution, we have to handle 3 more steps.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Add ACL rule to allow http call to REST service host<\/li>\n\n\n\n<li>Grant read\/write access from PLSQL to OS directory<\/li>\n\n\n\n<li>Add additional columns to store OSS information<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Add ACL rule to allow outbound http call<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>declare\n  l_username varchar2(30) := 'APEX_230100';\nbegin\n  dbms_network_acl_admin.append_host_ace(\n    host =&gt; 'localhost',\n    lower_port =&gt; 8000,\n    ace  =&gt;  xs$ace_type(privilege_list =&gt; xs$name_list('connect'),\n                        principal_name =&gt; l_username,\n                        principal_type =&gt; xs_acl.ptype_db));\n\n  commit;\nend;\n\/<\/code><\/pre>\n\n\n\n<p>Ensure it completes without error.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>PL\/SQL procedure successfully completed.<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Grant read\/write access from PLSQL to OS directory<\/h3>\n\n\n\n<p>Oracle PLSQL has another built-in package UTL_FILE for file read\/write. It is validation user&#8217;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.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE DIRECTORY APEX_COS AS '\/home\/oracle\/python\/apex\/tmp';<\/code><\/pre>\n\n\n\n<p>Then it is necessary to grant WRITE and READ access to DB user.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT READ,WRITE ON DIRECTORY APEX_COS TO STUDENT;<\/code><\/pre>\n\n\n\n<p>Finally, verify whether the access is granted successfully.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM   all_tab_privs\nWHERE  table_name = 'APEX_COS';<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Add additional columns in DB table to store OSS information<\/h3>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>We use APEX object browser to add these 2 additional columns in DB.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230903135940412.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1229\" height=\"757\" src=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230903135940412.png\" alt=\"\" class=\"wp-image-995\"\/><\/a><\/figure>\n\n\n\n<p>Or simply add these 2 columns using SQL<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE \"STUDENT\".\"STDT_TASKS\" ADD (\"IMAGE_03_KEY\" VARCHAR2(4000))<br>\/<br>ALTER TABLE \"STUDENT\".\"STDT_TASKS\" ADD (\"IMAGE_03_ETAG\" VARCHAR2(4000))<br>\/<\/code><\/pre>\n\n\n\n<p>Repeat above statements for all attachment field.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Development\"><\/span>Development<span class=\"ez-toc-section-end\"><\/span><\/h1>\n\n\n\n<p>We will develop following object in order to articulate the whole solution.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Python_script_as_REST_service_to_handle_file_upload\"><\/span>Python script as REST service to handle file upload<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Full source code can be found <a href=\"https:\/\/linyinghao.cn\/gitlab\/clark\/study-performance-tracking-app\/-\/blob\/development\/python\/qcloud-cos-service.py\">here<\/a>.<\/p>\n\n\n\n<p>There general processes include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Define REST service request and response payload. See definition of <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">class File_Upload_Req(BaseModel)<\/mark> and <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">class File_Upload_Res(BaseModel)<\/mark>.<\/li>\n\n\n\n<li>Get object storage secret id and secret key. This is confidential, should be handled dynamically and not hardcoded anywhere in the source.<\/li>\n\n\n\n<li>Use python to instantiate <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">CosS3Client<\/mark>.<\/li>\n\n\n\n<li>Upload file to Qcloud object storage bucket, using<mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\"> CosS3Client.put_object<\/mark>.<\/li>\n\n\n\n<li>Delete the file from local server after successful upload.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Python_script_as_REST_service_to_generate_download_URL\"><\/span>Python script as REST service to generate download URL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Full source code can be found <a href=\"https:\/\/linyinghao.cn\/gitlab\/clark\/study-performance-tracking-app\/-\/blob\/development\/python\/qcloud-cos-service.py\">here<\/a>.<\/p>\n\n\n\n<p>The general processes include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Define REST service request and response payload. See definition of <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">class File_Download_Req(BaseModel)<\/mark> and <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">class File_Download_Res(BaseModel)<\/mark>.<\/li>\n\n\n\n<li>Similarly, Get object storage secret id and secret key.<\/li>\n\n\n\n<li>Get Qcloud object URL by file&#8217;s object key, using <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">CosS3Client.get_presigned_url<\/mark>.<\/li>\n<\/ul>\n\n\n\n<p>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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"PLSQL_procedure_to_call_Python_REST_service_to_upload_files\"><\/span>PLSQL procedure to call Python REST service to upload files<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Use <a href=\"https:\/\/linyinghao.cn\/gitlab\/clark\/study-performance-tracking-app\/-\/blob\/development\/db\/procedures\/proc_blob_to_file.sql\">this<\/a> PLSQL wrapper procedure to call Python REST service to upload files to OSS.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"PLSQL_function_to_call_Python_REST_service_to_get_OSS_download_URL\"><\/span>PLSQL function to call Python REST service to get OSS download URL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Use <a href=\"https:\/\/linyinghao.cn\/gitlab\/clark\/study-performance-tracking-app\/-\/blob\/development\/db\/functions\/func_get_oss_url.sql\">this<\/a> PLSQL wrapper function to call Python REST service to get pre-signed download URL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"PLSQL_script_to_tentatively_store_upload_file_and_invoke_python_REST_service\"><\/span>PLSQL script to tentatively store upload file and invoke python REST service<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In processing section, add one more process to handle to upload upload to cloud<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230904131922123.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1506\" height=\"777\" src=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230904131922123.png\" alt=\"\" class=\"wp-image-1003\"\/><\/a><\/figure>\n\n\n\n<p>Detail process can be found user the definition of <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">Upload Files to COS<\/mark> <a href=\"https:\/\/linyinghao.cn\/gitlab\/clark\/study-performance-tracking-app\/-\/blob\/development\/app\/f10001\/application\/pages\/page_00008.sql\">here<\/a>.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Deployment\"><\/span>Deployment<span class=\"ez-toc-section-end\"><\/span><\/h1>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Specify_temp_directory_for_upload_file\"><\/span>Specify temp directory for upload file<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Place_python_script_and_enable_REST_service\"><\/span>Place python script and enable REST service<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Place python script in OS directory, and use following script to active REST service.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>nohup gunicorn qcloud-cos-service:app --workers 4 --worker-class uvicorn.workers.UvicornWorker --bind localhost:8000 &amp;<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>&#91;2023-09-04 19:34:28 +0800] &#91;3843961] &#91;INFO] Starting gunicorn 21.2.0<br>&#91;2023-09-04 19:34:28 +0800] &#91;3843961] &#91;INFO] Listening at: http:\/\/127.0.0.1:8000 (3843961)<br>&#91;2023-09-04 19:34:28 +0800] &#91;3843961] &#91;INFO] Using worker: uvicorn.workers.UvicornWorker<br>&#91;2023-09-04 19:34:28 +0800] &#91;3843968] &#91;INFO] Booting worker with pid: 3843968<br>&#91;2023-09-04 19:34:28 +0800] &#91;3843969] &#91;INFO] Booting worker with pid: 3843969<br>&#91;2023-09-04 19:34:28 +0800] &#91;3843970] &#91;INFO] Booting worker with pid: 3843970<br>&#91;2023-09-04 19:34:28 +0800] &#91;3843971] &#91;INFO] Booting worker with pid: 3843971<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843969] &#91;INFO] Started server process &#91;3843969]<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843969] &#91;INFO] Waiting for application startup.<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843969] &#91;INFO] Application startup complete.<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843968] &#91;INFO] Started server process &#91;3843968]<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843968] &#91;INFO] Waiting for application startup.<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843968] &#91;INFO] Application startup complete.<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843971] &#91;INFO] Started server process &#91;3843971]<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843971] &#91;INFO] Waiting for application startup.<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843971] &#91;INFO] Application startup complete.<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843970] &#91;INFO] Started server process &#91;3843970]<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843970] &#91;INFO] Waiting for application startup.<br>&#91;2023-09-04 19:34:34 +0800] &#91;3843970] &#91;INFO] Application startup complete.<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Add_PLSQL_script_in_APEX_uploaddisplay_page\"><\/span>Add PLSQL script in APEX upload\/display page<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>To display the image, firstly in SQL of APEX form, we add column for the URL of all pictures by invoding python service.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>select\n    TASK_ID,\n    TASK_NAME,\n    ...\n    <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">func_get_oss_url(image_01_key)<\/mark> as image_01_url_hidden,\n    <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">func_get_oss_url(image_02_key)<\/mark> as image_02_url_hidden,\n    <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">func_get_oss_url(image_03_key)<\/mark> as image_03_url_hidden,\n    <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">func_get_oss_url(image_04_key)<\/mark> as image_04_url_hidden,\n    <mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-red-color\">func_get_oss_url(image_05_key)<\/mark> as image_05_url_hidden\nfrom STDT_TASKS\nwhere tenant_id = (SELECT tenant_id\n             FROM   stdt_users su\n             WHERE  su.user_name = :APP_USER)<\/code><\/pre>\n\n\n\n<p>This may automatically add additonal 5 page items. Then we make these 5 items as HIDDEN type, and also make them Query Only.<\/p>\n\n\n\n<p>Finally, we can add new region to display the image based on the URL retrieved in these hidden columns.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230904133415839.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1496\" height=\"596\" src=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230904133415839.png\" alt=\"\" class=\"wp-image-1006\"\/><\/a><\/figure>\n\n\n\n<p>All done, verify the result by uploading a file and re-openning to see the uploaded picture.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230904133724656.png\"><img loading=\"lazy\" decoding=\"async\" width=\"713\" height=\"843\" src=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230904133724656.png\" alt=\"\" class=\"wp-image-1008\"\/><\/a><\/figure>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230904133931948.png\"><img loading=\"lazy\" decoding=\"async\" width=\"708\" height=\"568\" src=\"https:\/\/www.linyinghao.cn\/wordpress\/wp-content\/uploads\/2023\/09\/20230904133931948.png\" alt=\"\" class=\"wp-image-1009\"\/><\/a><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Future_Enhancement\"><\/span>Future Enhancement<span class=\"ez-toc-section-end\"><\/span><\/h1>\n\n\n\n<p>This is just the basic achiement of using Qcloud service to store picture objects. There are additional valuable enhancements may be included next step.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Manage the Qcloud SDK and local Python REST user credential in more secure way<\/li>\n\n\n\n<li>Upload in trunk for large object<\/li>\n\n\n\n<li>Support for video\/audio<\/li>\n\n\n\n<li>Batch archive process of OSS bucket for disaster prevention<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>This article describes the overall steps to utilize Qcloud object storage for any file upload\/display in Oracle APEX page.<\/p>\n","protected":false},"author":3,"featured_media":887,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[17,23],"class_list":["post-969","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-2","tag-apex","tag-object-storage"],"_links":{"self":[{"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/969","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=969"}],"version-history":[{"count":36,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/969\/revisions"}],"predecessor-version":[{"id":1013,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/969\/revisions\/1013"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/887"}],"wp:attachment":[{"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=969"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=969"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.linyinghao.cn\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=969"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}