How to configure and run SELECT AI in the Oracle Autonomous Database.
UPDATE : Video Demo of the usage of SELECT AI is at
--- first setup Network ACL for the Database to connect to the API
--- in this case, I have configured an API at anthropic.com
--- you could be using an OCI AI Service OR OpenAI or other provider
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.anthropic.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'HEMANT',
principal_type => xs_acl.ptype_db)
);
END;
/
--- grants to the account that will be configuring the credential
GRANT EXECUTE ON UTL_HTTP TO HEMANT;
GRANT EXECUTE ON DBMS_CLOUD_AI TO HEMANT;
--- login as HEMANT
BEGIN
DBMS_CLOUD.DROP_CREDENTIAL(
credential_name => 'ANTHROPIC_CRED'
);
END;
/
--- create the Credential to connect to the API
--- give it a name and label
--- specify the API Key as a password
--- verify with that the API Key is valid and has not expired
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'ANTHROPIC_CRED',
username => 'ANTHROPIC_USER', -- User-defined label
password => 'MYAPIKEY' -- example value "sk-ant-usr-15-ABcdefghijklmnopqrstuvwxYZ"
);
END;
/
--- create a profile to map to the API Credential
--- if you use different LLMs and different API Keys, specify a different profile for each !!
BEGIN
DBMS_CLOUD_AI.DROP_PROFILE(
profile_name => 'MY_ANTHROPIC_PROFILE'
);
END;
/
--- in the profile speciy which database schema and tables would be available to SELECT AI
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'MY_ANTHROPIC_PROFILE',
attributes => '{
"provider": "anthropic",
"credential_name": "ANTHROPIC_CRED",
"model": "claude-sonnet-4-6",
"object_list": [
{"owner": "HEMANT", "name": "DEPARTMENTS"},
{"owner": "HEMANT", "name": "EMPLOYEES"},
{"owner": "HEMANT", "name": "LOCATIONS"}
]
}',
description => 'AI profile for translating natural language to SQL'
);
END;
/
------------------------------------------------------------------------------------------------
--- Here we start the user session by specifying the profile to be used
EXEC DBMS_CLOUD_AI.SET_PROFILE('MY_ANTHROPIC_PROFILE');
--- this test returns output in JSON format
SELECT DBMS_CLOUD_AI.GENERATE(
prompt => 'Name all the Deparments and the number of Employees in each Department where a department has more than 5 employees',
profile_name => 'MY_ANTHROPIC_PROFILE',
action => 'runsql'
) FROM DUAL;
SELECT AI chat 'Name all the Deparments and the number of Employees in each Department where a department has more than 5 employees';
SELECT AI narrate 'Name all the Deparments and the number of Employees in each Department where a department has more than 5 employees';
SELECT AI chat 'Which location has the largest number of employees ?';
SELECT AI narrate 'Which location has the largest number of employees ?';
SELECT AI chat 'Which are the Top 3 Departments by Salary, list the Department names and total Salary in each';
SELECT AI narrate 'Which are the Top 3 Departments by Salary, list the Department names and total Salary in each';
SELECT AI chat 'how many employees are paid a commision ?' ;
SELECT AI narrate 'how many employees are paid a commision ?' ;
SELECT AI narrate 'what is the highest commission rate being paid ?' ;
The above shows how I configured my Autonomous Database to connect to an LLM and configured a profile for three specific tables to be used by SELECT AI