Select AI uses natural-language prompts to generate, run, inspect, and explain your data. Actions such as showsql, runsql, explainsql, and narrate allow you to review a generated query before execution, understand how it was formed, or turn results into a readable response. Select AI runs in the database, close to your data, making it uniquely suited for natural language to SQL workflows.
In this article, we’ll configure Select AI for repeatable, local integration tests using Oracle AI Database Free and Testcontainers. The pattern is great for tests and local development without the use of a cloud-managed database. We’ll use OCI GenAI as the LLM provider, but you could swap this out for a local model provider, like Ollama.
Select AI integrates with third-party models, self-hosted models, and even database-resident models through the ONNX runtime.
New to Select AI? Start here, or try the free Live Lab.
You can download the sample from GitHub here. If you like the repo, give it a star!
Local testing with Select AI
The SelectAILocalTest sample starts Oracle AI Database Free in Testcontainers, initializes bits for Select AI, creates a DBMS_CLOUD_AI profile, and calls the GENERATE function over JDBC.
This is an integration test you can run during development workflows with a disposable, personal database container.
To integrate with Select AI over JDBC, we can write a simple method:
private String selectai(DataSource ds, String prompt, String action) {
try (Connection conn = ds.getConnection()) {
String sql = """
BEGIN
? := DBMS_CLOUD_AI.GENERATE(
prompt => ?,
action => ?,
profile_name => ?);
END;
""";
try (CallableStatement statement = conn.prepareCall(sql)) {
statement.registerOutParameter(1, Types.CLOB);
statement.setString(2, prompt);
statement.setString(3, action);
statement.setString(4, "MY_PROFILE");
statement.execute();
return statement.getString(1);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}And then invoke it like so:
var generatedSQL = selectai(ds, "what are the available courses and where are they held?", "showsql"));This is analogous to running select ai showsql what are the available courses and where are they held? from a SQLcl client, but through a clean Java interface.
Run the example
Ensure you have the sample repository cloned, Java 21+, Maven, and an OCI account configuration (for GenAI LLM calls).
To run the sample, download the sample repository from GitHub, then invoke the test with Maven:
# set compartment ID for OCI Gen AI
export OCI_COMPARTMENT_ID=<your OCI compartment ID>
mvn test -pl testcontainers/pom.xml -Dtest=SelectAILocalTestIf all goes well, you should see the following test output where the local container is configured for Select AI, and a SQL statement is generated for the UNI schema with select ai showsql:
Installing certificates and DBMS_CLOUD family of PL/SQL packages...
Configuring Oracle AI Database for outbound HTTPS connections (ACEs)...
Loading University (UNI) schema and sample data...
Creating DBMS_CLOUD profile for Select AI...
Generating SQL using 'select ai showsql'...
Generated SQL on UNI schema:
SELECT
c."NAME" AS "Course Name",
lh."NAME" AS "Lecture Hall Name"
FROM
"UNI"."COURSES" c
JOIN "UNI"."LECTURE_HALLS" lh ON c."LECTURE_HALL_ID" = lh."ID"
Grants and test data
The test applies the grants script, creates a dedicated selectai user, execute grants to DBMS_CLOUD packages, creates a a few tables in the UNI schema with pre-loaded data:
-- add grants for DMBS_CLOUD family packages
create user selectai identified by Welcome12345 quota unlimited on users;
grant connect, resource to selectai;
grant execute on dbms_cloud to selectai;
grant execute on dbms_cloud_ai to selectai;
grant select on uni.students to selectai;
grant select on uni.courses to selectai;
grant select on uni.enrollments to selectai;
grant select on uni.lecture_halls to selectai;The university schema is loaded into the database container on startup (students.sql):
@Container
static OracleContainer oracleContainer = new OracleContainer("gvenzl/oracle-free:23.26.2-full-faststart")
.withStartupTimeout(Duration.ofMinutes(5))
.withUsername("UNI")
.withPassword("StudentsSchemaPassword12345")
.withInitScript("students.sql")
.withEnv(Map.of("ORACLE_PASSWORD", SYS_PASSWORD,
"WALLET_PASSWORD", WALLET_PASSWORD,
"CERTS_FILE", CERTS_FILE));
Database AI profile
When Select AI is used, it needs a DBMS_CLOUD_AI profile. In the Testcontainers example, we create a credential and profile for OCI GenAI:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => ?,
tenancy_ocid => ?,
private_key => ?,
fingerprint => ?
);
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'MY_PROFILE',
attributes => '{
"provider": "oci",
"credential_name": "GENAI_CRED",
"region": "us-chicago-1",
"oci_compartment_id": "<YOUR OCI COMPARTMENT ID>",
"object_list": [
{ "owner": "UNI", "name": "STUDENTS" },
{ "owner": "UNI", "name": "COURSES" },
{ "owner": "UNI", "name": "LECTURE_HALLS" },
{ "owner": "UNI", "name": "ENROLLMENTS" }
],
"enforce_object_list": true
}'
);
END;Keep in mind that other AI providers may be used, like OpenAI, ONNX models, and more.
Steps to configure Oracle AI Database Free for Select AI
We load the base container with a script to download OCI GenAI certificates, create a database wallet, and install the DBMS_CLOUD family of PL/SQL packages.
The ACE setup script is part of the local wallet and HTTPS connection setup. it permits the database-side HTTPS connection and points the database at the custom wallet. If you’re using Autonomous AI Database, this is handled for you.
For specific instructions on configuring your database, see Installing DBMS_CLOUD.
The specific configuration layers look like this:
- Start database container with SYS and wallet passwords
- Database container initializes UNI schema and populates test data.
- Download certificates (init script)
- Create wallet (init script)
- Database is configured with an ACE for external HTTPS access
- Wallet location is set as a database property
- User created and grants applied to DBMS_CLOUD and UNI schema
- cloud credential and AI profile created on the UNI schema.
Leave a Reply