Build AI-Powered Oracle APEX Applications using OCI AI Services
Professional Step-by-Step Guide for Oracle APEX + OCI AI Integration
Introduction
Explain:
- Evolution of Oracle APEX
- AI adoption in enterprise applications
- Why OCI AI Services matter
Example Introduction:
When I started working with Oracle APEX in 2009, applications were primarily focused on forms, reports, and workflow automation.
Today, with Oracle Cloud Infrastructure (OCI) AI Services, Oracle APEX applications can now see, read, understand, and analyze data intelligently using AI-powered capabilities such as:
- Document Understanding
- OCR & Text Extraction
- Image Classification
- Object Detection
- Key-Value Extraction
- Language AI
In this blog, we will build an end-to-end AI-powered Oracle APEX application using OCI AI Services step-by-step.
Architecture Overview
Configure OCI API Key and Bucket
Generate API Keys using OCI Console
- Login into your OCI Account.
-
Navigate to:
Identity & Security → Domains → User Management →
Click on Username
- Under the User section, open the API Keys tab and click Add API Key .
- In the Add API Key dialog, select Generate API Key Pair .
-
Click
Download Private Key
.
A
.pemfile will be downloaded to your system. You do not need to download the public key. Click Add . - Once the key is added, the Configuration File Preview will appear. Copy and save the configuration snippet into a notepad. This information will later be used while creating Oracle APEX Web Credentials.
Create a Bucket in OCI Object Storage
- Click the navigation menu icon (☰) located at the top-left corner.
- Under the Storage section, select Buckets .
-
Select the compartment where you want to create the bucket.
You can either:
- Choose an existing compartment
- Create a new compartment
-
Provide the Bucket Name and save the following details:
- Bucket Name
- Namespace
·
Create Web
Credentials in Oracle APEX
1.
Workspace Utilities > Web Credentials >
Create new Credentials
a.
Authentication Type = OCI Native
Authentication
b.
OCI User ID = Provide OCI ID (Go to My Profile
in OCI and copy "OCID")
c.
OCI Tenancy ID = Provide Tenancy ID (Go to
tenancy and copy Tenancy ID)
d.
OCI Public Fingerprint = Go to My Profile >
Scroll down and find "API Key
Create Rest Data Source:
·
Invoke OCI Doc Understanding using REST Data
Source
1.
Navigate to
the application home page and click
Shared Components
.
2.
Under
Data Sources, click
REST Data Sources
.
3.
Click
Create
>
From
scratch
and click
Next
.
4.
Under Create
REST Data Source, enter the following attributes and click
Next
.
o
Rest Data Source Type
: Oracle
Cloud Infrastructure (OCI)
o
Name
: OCI
Document Understanding API
o
URL Endpoint:
https://document.aiservice.ap-mumbai-1.oci.oraclecloud.com
/20221109/actions/analyzeDocument
Note
: URL Endpoint may differ based on your OCI tenancy.
5.
Under create
REST Data Source - Remote Server, click
Next
.
6.
Under
Authentication,
o
Authentication Required
: Toggle the
button to
ON
o
Credentials
:
apex_ai_cred
7.
Click
Create REST
Source Manually
. The REST data source is created successfully.
The next step is to configure the POST operation parameters for this REST Data
Source.
8.
On the REST
Data Sources page, click
Document Understanding API
.
9. Under the Operations , click Edit icon for the POST operation and enter the following:
Request Body Template: Copy and paste the JSON given below.
{"compartmentId" : "#COMPARTMENT_ID#",
"document" :
{ "namespaceName" : "#NAMESPACE_NAME#",
"bucketName" : "#BUCKET_NAME#",
"objectName" : "#OBJECT_NAME#",
"source" : "OBJECT_STORAGE"
},"features" :
[ { "featureType" : "#FEATURE_TYPE#"
} ] }
10. Click Synchronize with Body and then click OK .
11.
Under
Operation
Parameters
, click
Add Parameter
.
12.
In the
Edit REST Data Source Parameter
dialog, add the following two parameters one after the other:
|
Type
|
Name
|
Direction
|
Default Value
|
Static
|
|
HTTP
Header
|
Content-Type
|
In
|
application/json
|
ON
|
|
Request
or Response Body
|
RESPONSE
|
Out
|
13.
Click
Apply Changes
.
14.
Create an
application with following page items.
|
Identification > Name
|
Identification > Type
|
Default > Type
|
Default > Static
|
Session State > Datatype
|
|
P1_FILE_BLOB
|
File Upload
|
|||
|
P1_COMPARTMENT_ID
|
Hidden
|
Static
|
Enter
your OCI account Compartment ID
|
|
|
P1_CRED_STATIC_ID
|
Hidden
|
Static
|
oci_oacupx_document_reading
|
|
|
P1_NAMESPACE_NAME
|
Hidden
|
Static
|
Enter
Namespace which you copied while creating a Bucket
|
|
|
P1_BUCKET_NAME
|
Hidden
|
Static
|
Enter
the Bucket Name which you copied while creating a Bucket
|
|
|
P1_FEATURE_TYPE
|
Hidden
|
Static
|
TEXT_EXTRACTION
|
|
|
P1_REGION
|
Hidden
|
Static
|
ap-mumbai-1
|
|
|
P1_RESPONSE
|
Hidden
|
CLOB
|
15.
In the
property editor, Enter/select the following details:
o
Under Identification:
§
Button Name:
Process_Document
§
Label:
Process
Document
o
Layout > Slot:
Create
o Behavior > Database Action: SQL INSERT action
Create Processes to upload Invoices to
Object:
1.
Right-click
Process Invoice
and select
Add Child
Process
.
2.
In the Property Editor, Enter/select the following details:
o
Under Identification:
§
Name:
Upload to
Object Storage
§
Type:
Invoke API
o
Under Settings:
§
Type:
PL/SQL
Procedure or Function
§
Procedure or
Function:
UPLOAD_FILE
Procedure:
======== =================UPLOAD_FILE============================
CREATE OR REPLACE PROCEDURE UPLOAD_FILE (
P_FILE_CONTENT IN VARCHAR2,
P_STATIC_ID IN VARCHAR2,
P_NAMESPACE IN VARCHAR2,
P_BUCKET IN VARCHAR2,
P_REGION IN VARCHAR2,
P_OBJECT_STORAGE_URL OUT VARCHAR2,
P_FILE_NAME OUT VARCHAR2,
P_MIME_TYPE OUT VARCHAR2
) IS
L_OBJECT BLOB;
L_RESPONSE CLOB;
BEGIN
SELECT
BLOB_CONTENT, FILENAME, MIME_TYPEINTO
L_OBJECT, P_FILE_NAME, P_MIME_TYPEFROM
APEX_APPLICATION_TEMP_FILESWHERE
NAME = P_FILE_CONTENT;
P_OBJECT_STORAGE_URL := 'https://objectstorage.'
|| P_REGION
|| '.oraclecloud.com/n/'
|| P_NAMESPACE
|| '/b/'
|| P_BUCKET
|| '/o/'
|| P_FILE_NAME;
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Content-Type';
APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := P_MIME_TYPE;
L_RESPONSE := APEX_WEB_SERVICE.MAKE_REST_REQUEST
(P_URL => P_OBJECT_STORAGE_URL, P_HTTP_METHOD => 'PUT',
P_BODY_BLOB => L_OBJECT, P_CREDENTIAL_STATIC_ID => P_STATIC_ID
); IF APEX_WEB_SERVICE.G_STATUS_CODE != 200 THEN
RAISE_APPLICATION_ERROR(-20000, 'Upload failed - ' ||
APEX_WEB_SERVICE.G_STATUS_CODE);
END IF;
END UPLOAD_FILE;
3.
Under
Upload to Object Storage
process,
expand Parameters and enter the following details:
|
Parameters
|
Value > Type
|
Value > Item
|
|
p_file_content
|
item
|
P1_FILE_BLOB
|
|
p_static_id
|
item
|
P1_CRED_STATIC_ID
|
|
p_namespace
|
item
|
P1_NAMESPACE_NAME
|
|
p_bucket
|
item
|
P1_BUCKET_NAME
|
|
p_region
|
item
|
P1_REGION
|
4.
Right-click
Process Invoice
and select
Add Child
Process
.
5.
In the Property Editor, Enter/select the following details:
o
Under Identification:
§
Name:
Automatic DML
§
Type:
Form -
Automatic Row Processing (DML)
§
Form Region:
Upload Your
Invoice
6.
Right-click
Process Invoice
and select
Add Child
Process
.
7.
In the Property Editor, Enter/select the following details:
o
Under Identification:
§
Name:
Integrate
Document Understanding API
§
Type:
Invoke API
o
Under Settings:
§
Type:
REST Source
§
REST Source:
Document
Understanding API
§
Operation:
POST
8.
Under
Integrate Document Understanding API
process,
expand Parameters and select
OBJECT_NAME
, enter the following:
9.
Under Value:
o
Type:
Item
o
Item:
P1_FILE_NAME
10.
Select
RESPONSE
, Enter/select the following:
o
Parameter > Ignore Output:
Toggle off
o
Value > Item:
P1_RESPONSE
o
Session State
> Datatype : CLOB
11.
Create one Classic Report Region for displaying received Response
with below query:
SELECT jt.text FROM
JSON_TABLE(
:P2_RESPONSE,
'$.pages[*].words[*]'
COLUMNS (text
VARCHAR2(4000) PATH '$.text')
) jt
12. Run the application. Upload a file and check for output in report.
KEY VALUE
EXTRACTION
1.
Follow all
the above steps.
2.
Set the
F1_FEATURE_TYPE page item with below mentioned value:
|
Identification > Name
|
Identification > Type
|
Default > Type
|
Default > Static
|
Session State > Datatype
|
|
P1_FEATURE_TYPE
|
Hidden
|
Static
|
KEY_VALUE_EXTRACTION
|
3.
Create a
table
DOCAI_RESPONSE
as shown below:
4.
Create one
Child Process:
‘Parse Response’
in Process Execution Chain to parse the
response and store in a DOCAI_RESPONSE table.
5.
Create a
Classic Report Region with below query to display parsed data in proper format.
6.
Run the
application and upload any invoice or receipt and observe the output with key
value fields.
Input Image:
OCI Vision
Follow
Configure OCI API Keys and a Bucket
Step from Pg 1
Create Rest Data Source:
·
Invoke OCI Doc Understanding using REST Data
Source
1.
Navigate to
the application home page and click
Shared Components
.
2.
2.
Under Data Sources, click
REST Data
Sources
.
3.
3.
Click
Create
>
From scratch
and click
Next
.
4.
Under Create
REST Data Source, enter the following attributes and click
Next
.
o
Rest Data Source Type
: Oracle
Cloud Infrastructure (OCI)
o
Name
: OCI Vision
o
URL Endpoint
https://vision.aiservice.ap-mumbai-1.oci.oraclecloud.com
/20221109/actions/analyzeImage
o
Note
: URL
Endpoint may differ based on your OCI tenancy.
5.
Under create
REST Data Source - Remote Server, click
Next
.
6.
Under
Authentication,
a.
Authentication
Required
: Toggle the button to
ON
b.
Credentials
:
apex_ai_cred
7.
Click
Create REST
Source Manually
. The REST data source is created successfully.
The next step is to configure the POST operation parameters for this REST Data
Source.
8.
On the REST
Data Sources page, click
Document Understanding API
.
9.
Under the
Operations
,
click
Edit icon
for the
POST
operation and enter the following:
-
Request
Body Template: Copy and paste the JSON given below.
{"compartmentId" : "#COMPARTMENT_ID#",
"image" :
{ "namespaceName" : "#NAMESPACE_NAME#",
"bucketName" : "#BUCKET_NAME#",
"objectName" : "#OBJECT_NAME#",
"source" : "OBJECT_STORAGE"
},"features" :
[ { "featureType" : "#FEATURE_TYPE#"
} ] }
10.
Click
Synchronize
with Body
and then click
OK
.
11.
Under
Operation
Parameters , click
Add Parameter
.
2.
In the
Edit REST Data Source Parameter
dialog, add the following two parameters one after the other:
|
Type
|
Name
|
Direction
|
Default Value
|
Static
|
|
HTTP
Header
|
Content-Type
|
In
|
application/json
|
ON
|
|
Request
or Response Body
|
RESPONSE
|
Out
|
13.
Click
Apply Changes
.
14.
Follow from
Step 14 of (Create REST Source from Document Understanding) to create
application and its processes.
15.
Replace Rest
Source with the one created above for OCI Vision.
16.
Create a
Classic Report Region with below query:
17.
Run the
application and upload the image.
Input Image:
Output:
OBJECT
DETECTION
1.
Follow all
the above steps of OCI Vision Image Classification.
2.
Set the
F1_FEATURE_TYPE page item with below mentioned value:
|
Identification > Name
|
Identification > Type
|
Default > Type
|
Default > Static
|
Session State > Datatype
|
|
P1_FEATURE_TYPE
|
Hidden
|
Static
|
OBJECT_DETECTION
|
3.
Create a
Classic Report Region with below query:
4.
Run the
application and upload the image and observe the output.
Input Image
Output
OCI LANGUAGE
·
Create a Page with following items:
P10_CHOOSE_MODEL: Select List with different language models
P1_INPUT_TEXT, P10_OUTPUT_TEXT: Text field
SUBMIT button
·
Create a Page Process with below code:
DECLARE
l_request_body CLOB;
l_response CLOB;
l_url CLOB;
l_input_text CLOB;
BEGIN
-- Escape JSON special characters
l_input_text := apex_escape.json(:P10_INPUT_TEXT);
--JSON request body
l_request_body := '
{
"documents":
[
{
"key": "doc1",
"text": "'||l_input_text||'"
}
]
}';
--'||:P10_INPUT_TEXT||'
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
apex_debug.info('Request body---' || l_request_body);
-- Call the OCI Language REST Web Service.
l_response := apex_web_service.make_rest_request
(p_url => 'https://language.aiservice.ap-mumbai-1.oci.oraclecloud.com/20210101/actions/'||:P10_CHOOSE_MODEL,
p_http_method => 'POST',
p_body => l_request_body,
p_credential_static_id => 'oci_doc_ai_credentials');
--Assign l_response to P10_OUTPUT page item
:P10_OUTPUT := l_response;
END;
· Create different Page Process for PII Model:
DECLARE
l_request_body CLOB;
l_response CLOB;
l_url CLOB;
l_input_text CLOB;
BEGIN
-- Escape JSON special characters
l_input_text := apex_escape.json(:P10_INPUT_TEXT);
--JSON request body
l_request_body := '{
"documents": [
{
"key": "doc1",
"text": "'||l_input_text||'",
"languageCode": "en"
}
],
"masking": {
"ALL": {
"mode": "MASK",
"isUnmaskedFromEnd": true,
"leaveCharactersUnmasked": 4
}
}
}';
apex_web_service.g_request_headers(1).name := 'Content-Type';
apex_web_service.g_request_headers(1).value := 'application/json';
-- Call the OCI Language REST Web Service.
l_response := apex_web_service.make_rest_request
(p_url => 'https://language.aiservice.ap-mumbai-1.oci.oraclecloud.com/20221001/actions/batchDetectLanguagePiiEntities',
p_http_method => 'POST',
p_body => l_request_body,
p_credential_static_id => 'oci_doc_ai_credentials');
--Assign l_response to P10_OUTPUT page item
:P10_OUTPUT := l_response;
END;
·
Create Reports for different models:
Language Detection:
Key
Value Extraction:
Named
Entity:
Text
Classification:
Sentiment Analysis:
PII:
Run the application and choose the Language Model, provide Input Text and click on Submit Button and observe the output of different models in their respective report.
