22 November, 2026

SELECT AI in the Oracle Autonomous Database

 How to configure and run SELECT AI in the Oracle Autonomous Database.



--- 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';

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

No comments: