Running E-Business Suite "Select AI" (NLQ) on-premises

To setup an internal demo environment for PROMATIS, I recently ran into the problem, that I didn't have an Autonomous Transaction Processing / 23ai database at hand (available and reachable with a VPN). Instead of setting up a VPN (or going for public internet traffic) and an ATP in OCI I decided (for the sake of trying it) to build something on-premises that serves as the system where DBMS Select AI does its magic. Let's have a look.

Notice: Do not try this at home for real workloads. This is probably not a supported scenario.

Architecture of E-Business Suite Natural Language Query (NLQ) feature

In case you missed my previous blog post on the EBS NLQ feature you might want to have a look here.

Basically, here is the overall architecture again:

High level architecture of the solution; source: Oracle, see 3059877.1

The blog post here handles the "ADB on OCI" part in the top, where the metadata schema is handed over to the OCI (or OpenAI or other) Large Language Model. Usually that is running in a 23ai database on OCI; but for this blog post I replaced it with a simple docker container.

Starting an on-premises Autonomous Transaction Processing DB

The official Oracle Container Registry (which is always my first point to look for Oracle docker images - there are great containers from Gerald Venzl at https://github.com/gvenzl/oci-oracle-free as well though) reveals a "Oracle Autonomous Database Free" database here: https://container-registry.oracle.com/ords/ocr/ba/database/adb-free

From a license perspective the container states: "The container image you have selected and all of the software that it contains is licensed under the Oracle Free Use Terms and Conditions which is provided in the container image. Your use of the container is subject to the terms of Oracle Free Use Terms and Conditions license.". This should be good enough for some internal testing. 🙂

So, let's get the database up and running which is - if you already have a docker engine running and a reasonable fast internet connection a matter of 5-10 minutes and of just a single command:

docker run -d \
-p 1521:1521 \
-p 8443:8443 \
-e WORKLOAD_TYPE='ATP' \
-e WALLET_PASSWORD=FancyPwd2025forNLQ \
-e ADMIN_PASSWORD=FancyPwd2025forNLQ \
-e ENABLE_ARCHIVE_LOG=False \
--cap-add SYS_ADMIN \
--device /dev/fuse \
--name adb-nlq-ebs01 \
container-registry.oracle.com/database/adb-free:latest-23ai

After a few minutes you should be able to access apex under https://localhost:8443.

Internal/ADMIN/FancyPwd2025forNLQ should work to sign in to both APEX as well as SQL Developer web.

Special considerations during the NLQ for EBS setup

Large parts of the setup work in the usual way as greatly described in MOS Note 3059877.1.

Here are the "special things" I had to consider. Note: I am still using OCI and the LLama (in this case 3.3) model to run the interference. Should you be very sensitive regarding data privacy you might replace the backend with some local Llama model. Or you could go for the OpenAI GPT-4o model instead as well. The supported providers are:

• OpenAI
• Cohere
• Azure
• OCI
• Google
• Anthropic
• Hugging Face

So here are the special things to consider when running the ATP in a docker container:

• When using Meta Llama as the LLM (by the way I was using LLama 3.3-70b for this test) you eventually must adjust the nlq_util_ai_profiles.sql. I'll do some more testing comparing version 3.1 and 3.3 of the model in a later blog post. The modification itself is simple though:

l_llm_model VARCHAR2(100) := 'meta.llama-3.3-70b-instruct';

• The authentication with OCI cannot use the usual resource principals. To overcome this, I've created a regular user and an API key. Put that user into an appropriate group and grant the group (instead of the instance principal) permission to use Generative AI services.
• Then as the XX_NLQ_UTIL user of the (local) ATP create a credential using DBMS_CLOUD as follows (all in one line):

exec DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'adb_nlq_ebs01',user_ocid =>'ocid1.user.oc1..userid',tenancy_ocid =>'ocid1.tenancy.oc1..tenandid',private_key => 'allInOneLinePrivateKey',fingerprint => '12:34:56...');

• Finally, again in the nlq_util_ai_profiles exchange the llm credentials:

 l_llm_cred VARCHAR2(30) := 'adb_nlq_ebs01';

Testing OCI LLM Access

To verify if everything is working in the ATP I used the following commands:

begin
DBMS_CLOUD_AI.DROP_PROFILE(
profile_name => 'TESTGENAI'
);
commit;
end;
/

BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'TESTGENAI',
attributes => '{"provider": "oci",
"credential_name": "adb_nlq_ebs01",
"object_list": [{"owner": "XX_NLQ_UTIL", "name": "DUAL"}],
"model": "meta.llama-3.3-70b-instruct",
"temperature":"0.4",
"oci_apiformat": "GENERIC",
"region": "eu-frankfurt-1",
"oci_compartment_id":"ocid1.compartment.oc1..12345"
}'
);
commit;
END;
/

SELECT DBMS_CLOUD_AI.GENERATE(prompt => 'What is Oracle DB',
profile_name => 'TESTGENAI',
action => 'chat')
FROM DUAL;

This should give you something as follows:

Test accessing DBMS_CLOUD_AI with the LLM running in OCI

Further setup

The remaining setup was "as usual" and without any problems. Keep in mind that it will probably be necessary to set the application setting "ADB_HTTPS_HOST" to the name of your container host - since this is the CN that ords uses for the self-signed certificate.

Summary

The above procedure allows to quickly set up the EBS for NLQ feature with a local Autonomous Transaction Processing database running in a docker container on-premises. In the real world obviously I'd discourage of doing this and using a (supported) Oracle ATP instance in OCI instead. And hopefully, your E-Business Suite instance is running in OCI as well anyways. 😉