Oracle Autonomous Database Select AI simplifies and automates the use of generative AI enabling generating, running, and explaining SQL from a natural language prompt and chatting with your LLM. Select AI also automates vector index creation, simplifies the retrieval-augmented generation (RAG) workflow, and enables generating synthetic data. Select AI enables you to take your application and AppDev to the next level with generative AI, conveniently through SQL.

We’re announcing general availability for several Select AI enhancements on Autonomous Database for natural language to SQL query generation, also known as NL2SQL or text-to-SQL. These new capabilities can help improve query and result relevancy while simplifying and automating your AppDev experience with Select AI:  

  • Additional sources of prompt metadata for the Select AI-generated augmented prompt
  • Case-insensitive row filtering
  • Control over the data Select AI can send to the LLM
  • Help avoid table name hallucinations in generated SQL
  • Automation of table metadata selection for large schemas 
  • Use of ‘runsql’ action in the GENERATE procedure, where the structured query results are provided in JSON format. 

Augmented prompt metadata

You can now enable additional sources of prompt metadata for the Select AI-generated augmented prompt. The list includes table comments (in addition to column comments), foreign key and referential integrity constraints, and annotations (Oracle Database 23ai instances only). You can enable these capabilities in your AI profile using the following attributes:

  • “comments”: true – adds table and column comments
  • “constraints”: true – adds foreign key and referential integrity constraints
  • “annotations”: true – adds annotations, a feature available in 23ai databases

Here is an AI profile example that enables each of these sources using the tables in the object list:

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE (
    profile_name => 'my_profile',
    attributes =>       
        '{"provider": "azure",
          "azure_resource_name":   "my-resource",                    
          "azure_deployment_name": "my-deployment",
          "credential_name":       "AI_CREDENTIAL",
          "comments":              "true", 
          "constraints":           "true", 
          "annotations":           "true", 
          "conversation":          "true",
          "object_list": [
            {"owner": "moviestream", "name": "GENRE"},
            {"owner": "moviestream", "name": "CUSTOMER"},
            {"owner": "moviestream", "name": "STREAMS"},            
            {"owner": "moviestream", "name": "MOVIES"},
            {"owner": "moviestream", "name": "ACTORS"}              
          ]          
          }'
    );
END;

Case-insensitive row filtering

To help you get the responses you want from your schema involving string values, the Select AI-generated WHERE clause now performs case-insensitive row filtering by default. This eliminates the need for having to explicitly mention this in your prompt to the LLM. If needed, you have the option to enforce case sensitivity using the following profile attribute:

  • “case_sensitive_values”: true

Control over the data Select AI can send to the LLM

By default, Select AI can send data to your LLM for certain features like the ‘narrate’ action for NL2SQL and RAG, as well as synthetic data generation. To enable greater control over your data and its security, a user with administrator privileges can now disable for all users any Select AI operations that would result in database data (beyond metadata) being sent in the prompt or for data generation. The following two procedures disable and enable such data access:

  • DBMS_CLOUD_AI.disable_data_access
  • DBMS_CLOUD_AI.enable_data_access

Avoid table name hallucinations in generated SQL

Since LLMs are known to hallucinate, even in the face of a well-augmented prompt, you may want to enforce that generated SQL queries reference only tables explicitly covered by your AI profile “object_list” attribute. This can be important, for example, for chat agents or web users leveraging Select AI via applications – for example, built with Oracle APEX. Use the following profile attribute to enable this capability:

  • “enforce_object_list”: true

Support for Large Schemas

Application schemas often contain many tables and views – hundreds or even thousands. To optimize the LLM’s ability to generate SQL and avoid sending more metadata than necessary to the LLM – and possibly avoid exceeding LLM token limits – you can select subsets of these tables by defining multiple AI profiles for specific tasks, applications, or domains.  

By leveraging Oracle Database 23ai AI Vector Search, Select AI automates selecting the relevant tables and views from your schema(s) that are included in the augmented prompt metadata. Select AI automatically builds and maintains a vector index in the background using the metadata for all the applicable objects in your schema. Using semantic similarity search, Select AI is designed to choose the most relevant objects based on the user prompt. 

You can enable this using the following profile attributes, where the object list can specify one or more schemas as well as specific tables.

  • “object_list_mode”: “automated”
  • “object_list”: [{“owner”: “SELECT_AI_USER”}]

Use of ‘runsql’ action in the GENERATE procedure

When using the SQL command line via “select ai runsql <prompt>”, the Select AI ‘runsql’ action returns a structured result – a rowset. Since GENERATE cannot return a rowset, we now generate a JSON-formatted string of the equivalent information and return that. This makes ‘runsql’ a valid option for GENERATE. 

For example, the following invocation returns a JSON string. 

DECLARE
  v_response VARCHAR2(4000);
BEGIN
  v_response := DBMS_CLOUD_AI.GENERATE(
    prompt => 'find the top 3 baby boomer big spenders','OCI_PROFILE',
    profile_name => 'OCI-PROFILE',
    action => 'runsql');
  DBMS_OUTPUT.PUT_LINE(v_response);

END;

The corresponding result in JSON format is:

[ 
  {"CUST_FIRST_NAME":"Marvel","CUST_LAST_NAME":"Bakerman","Total_Spent":145358.48}, 
  {"CUST_FIRST_NAME":"Harold","CUST_LAST_NAME":"Allis","Total_Spent":143700.7}, 
  {"CUST_FIRST_NAME":"Ona","CUST_LAST_NAME":"Cattlett","Total_Spent":122683.52} 
]

For more information…

To get started on your generative AI journey with Select AI on Autonomous Database, see these resources: