In today’s data-driven world, organizations are inundated with vast amounts of data, and the challenge often lies in extracting meaningful insights from that material. Enter Oracle “Select AI,” a comprehensive suite of AI-driven features integrated into Oracle Autonomous Database (ADB), which leverages advanced AI capabilities to enhance the data analytics capabilities.

This article examines the benefits, features, and applications of Oracle's ”Select AI” and demonstrates how organizations can use its features to drive productivity and innovation.

What is Oracle Select AI, and how does it work?

Select AI augments Oracle Autonomous Database with generative AI features and combines the ADB metadata with the potentials of Large Language Models (LLMs) to convert user input text into Oracle SQL. Select AI processes the natural language prompt, supplements the prompt with metadata, and then builds and executes the AI-generated SQL query.

Image 1

• In the above example, Select AI uses Large Language Models (LLMs) to understand the semantics of the user prompts. It breaks them into different parts of the query, such as a count of the purchase orders and records created in the current month grouped by the supplier’s name.
• It then combines this information with the ADB metadata, identifies the right tables and columns, and joins them to transform into an Oracle SQL query.

Benefits of using Oracle Select AI

• Get rapid new insights from all your data via natural language conversations without performing complex manual processes.
• Augment existing apps or develop new ones with built-in natural language processing (NLP) capabilities using Large Language Models (LLMs).
• As part of Oracle Autonomous Database, Select AI inherits all security and authentication features of the database.
• Select AI keeps your data safe, as it’s never shared with LLM providers.
• Since LLMs, such as Cohere and OpenAI, are trained on vast volumes of text data, they can understand the nuances and even intended meanings in most natural language queries.
• Using natural language, deriving information from your database that involves writing queries with multiple joins, nested subqueries, and other SQL constructs becomes much easier and faster.
• By learning effective query patterns from curated training data, LLMs can produce more efficient queries, enabling them to perform better.

Let’s dive into the process of configuring Select AI and its applications.

Prerequisites

To configure Autonomous Database, Select AI, we need:

- Oracle Autonomous Database (ADB)

- LLM API Key from OpenAI or a Cohere account

- Access to ADB ADMIN user

- ADB tables that serve as metadata for AI

- Access to tools such as SQL Developer Web/Desktop, APEX, and OML notebooks to utilize Select AI

Configuring Select AI on ADB

1. Obtain LLM API Key

The first step is to obtain the API key from one of the Large Language Model (LLM) providers. We will use Cohere for our use case. You can create an account on the Cohere portal and obtain either a paid production key, or even use a free trial key for testing out the Select AI features. The same can be done using the OpenAI service as well.

Image 2

2. Create ACL

Now, we need to log in as an administrator on our ADB environment and add the ADB user to the Access Control List (ACL).

Navigate to the ADB instance page and open Database Actions -> SQL

Image 3

As an ADMIN user, we need to grant network Access Control List (ACL) access to the database user or schema who wants to use Select AI.

For Cohere, the host is api.cohere.ai and for OpenAI, the host would be api.openai.com.

    
        BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'api.cohere.ai',
         ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => '',
                             principal_type => xs_acl.ptype_db)
   );
END;
                             

3. DBMS_CLOUD_AI Package

The DBMS_CLOUD_AI package in ADB enables integration with a user-specified LLM for generating SQLs using natural language prompts. This package performs the tasks of connecting LLMs with ADB metadata, as explained in the first section (Image 1). It works with AI providers such as Cohere, OpenAI, and Oracle Cloud Infrastructure Generative AI.

While logged in as ADB ADMIN, we need to grant EXECUTE privileges on this package to the database user who will utilize the Select AI features.

    
        grant execute on DBMS_CLOUD_AI to ;
        

4. Create Credential

The next step is to log in as the ADB user mentioned above and create the credentials to authenticate with the LLM provider, Cohere, in our case.

    
        BEGIN
    DBMS_CLOUD.create_credential('COHERE_PROD', 'COHERE', '');
END;

In this example, COHERE_PROD is the user-defined name of our credential, and the key is the API key obtained from the Cohere portal.

The same can be done using the OpenAI API key if one wishes to use that service instead of Cohere.

5. Create Profile

Now, we will create the Select AI profile using the DBMS_CLOUD_AI package. The profile lets us provide the ADB tables as the object list. The AI engine will use these tables as metadata to build the SQL queries based on our natural language prompts.

    
        BEGIN
        DBMS_CLOUD_AI.create_profile(
            'COHEREPROF',
            '{"provider": "cohere",
              "credential_name": "COHERE_PROD",
              "object_list": 
           [{"owner": "SCHEMA", "name": "ORG_EMPLOYEES"},
                  {"owner": "SCHEMA", "name": "ORG_DEPARTMENTS"},
                 {"owner": "SCHEMA", "name": "ORG_SALARIES"}
           ]
             }');
      END;
    

In the above example, COHEREPROF is our profile name, which was created using the COHERE_PROD credential.

The items in the object_list parameter denote all the ADB table names along with their associated schema names. In our case, based on the natural language prompts, Select AI will try to build SQL queries by joining the ORG_EMPLOYEES, ORG_DEPARTMENTS, and ORG_SALARIES tables.

At this stage, we have performed all the necessary configurations for Select AI to be functional in our ADB environment.

6. Testing the Select AI

Select AI is integrated with Oracle SQL so we can use it via any SQL IDE such as SQL Developer Web or Desktop, APEX SQL Workshop, APEX application development, or even OML notebooks.

Let’s use APEX SQL Workshop to perform our first Select AI test. We need to use DBMS_CLOUD_AI.GENERATE function to access Select AI features.

    
        BEGIN
        DBMS_OUTPUT.PUT_LINE(
            DBMS_CLOUD_AI.GENERATE(
    prompt => 'show all employees with their departments',
               action => 'showsql',
               profile_name => 'COHEREPROF'
                                   )
                         );
    END;

As we can see, the Cohere LLM has decoded our natural language prompt and correctly identified and joined the ORG_EMPLOYEES and ORG_DEPARTMENTS tables to form the SQL query for us. We used the profile COHEREPROF to make this call.

Image 4

The action parameter of DBMS_CLOUD_AI.GENERATE function accepts the following:

showsql: Returns the SQL query generated by Select AI

runsql: Runs the SQL query created from the natural language prompt and returns the result of the query

chat: AI generated conversational responses

narrate: Returns a natural language explanation pertaining to the AI generated SQL query

Image 5

7. Tweaking the metadata for better AI results:

Based on the configurations done so far, the Select AI should work in most cases, provided the user presents relevant natural language prompts that are aligned to the ADB tables configured in the Select AI profile.

In the above example, Select AI could identify and join employee as well as department tables because the table and column names were intuitive.

So what would happen if our table names and/or column names are not intuitive? Well, Select AI won’t be able to give us the correct answers.

Let’s see an example.

Let’s say we have created a new profile, COHEREPROF2, that incorporates a table named TABLE1 with Select AI.

    
        BEGIN
        DBMS_CLOUD_AI.create_profile(
            'COHEREPROF2',
            '{"provider": "cohere",
              "credential_name": "COHERE_PROD",
              "object_list": 
           [{"owner": "SCHEMA", "name": "TABLE1"}
           ]
             }');
      END;
    

TABLE1 stores invoice specific information in ADB but the table and column names are not intuitive.

Image 6

If we invoke the DBMS_CLOUD_AI.GENERATE asking for information about invoices, the Select AI doesn’t understand how to build an SQL query since there’s no way for it to relate TABLE1 with invoice-related information.

Image 7

So, how to fix this problem?

Enter the ‘COMMENT’ feature in the Oracle database. The COMMENT statement lets us add a comment about a table or column to the data dictionary, which helps Select AI relate the non-intuitive metadata to the relevant information. Let’s see how it works.

Let’s add comments on our table TABLE1 and its columns so that Select AI can find the relevant information about invoices and identify this table based on the data dictionary.

    
        COMMENT ON TABLE TABLE1 IS 'This table contains details on invoices';

COMMENT ON COLUMN TABLE1.INV_NUM IS 'This column contains invoice number details';

COMMENT ON COLUMN TABLE1.INV_AMOUNT IS 'This column contains invoice amount matrices';

COMMENT ON COLUMN TABLE1.INV_DATE IS 'This column contains invoice date';

COMMENT ON COLUMN TABLE1.INV_STATUS IS 'This column contains invoice status details';

Once comments are added, we need to drop and recreate the profile for the comments to take effect.

Drop Profile:

    
        BEGIN
    DBMS_CLOUD_AI.DROP_PROFILE('COHEREPROF2');
END;

Recreate profile:

    
        BEGIN
  DBMS_CLOUD_AI.create_profile(
      'COHEREPROF2',
      '{"provider": "cohere",
        "credential_name": "COHERE_PROD",
        "object_list": 
 	[{"owner": "SCHEMA", "name": "TABLE1"}
 	]
       }');
END;

Let’s invoke DBMS_CLOUD_AI.GENERATE again and see if Select AI can identify the correct table and build a SQL query.

Image 8

As we can see, Select AI has now correctly identified TABLE1 for retrieving the invoice details and successfully built the SQL query for us.

The COMMENT statement is very useful for tweaking/refining the metadata so that we can help Select AI to identify the right ADB tables related to the correct information. This helps improve the results and reduce AI hallucinations.

8. Uses of Select AI

In the above examples, we used SQL Workshop to test the Select AI features. Many other tools, such as SQL Developer Web, APEX, SQL Developer Desktop, and OML notebooks, can leverage the Select AI functionalities. Let’s see how these various tools use Select AI features.

Use in SQL Developer Web

As seen in the above examples, we can utilize Select AI capabilities from SQL Developer Web in a similar way to SQL Workshop. This means we need to use DBMS_CLOUD_AI.GENERATE to invoke the AI call and retrieve results.

If we run the code below from SQL Developer Web, we’ll get results similar to those of SQL Workshop.

    
        BEGIN
    DBMS_OUTPUT.PUT_LINE (
    DBMS_CLOUD_AI.GENERATE(
    prompt => 'show all employees with department and salaries',
    action => 'showsql',
    profile_name => 'COHEREPROF')  			        
    );
END;

Image 9

Use in Oracle APEX application

There are many ways to augment an APEX application with Select AI features, but let’s consider a simple example.

- Create a new application.

- Create a page item P1_PROMPT and enable "Submit when Enter pressed" option.

Image 10

- Create a Classic report with type "Function Body returning SQL Query."

Image 11

Add the code below in the PL/SQL function:

    
        BEGIN
        IF :P1_PROMPT IS NOT NULL THEN
            RETURN DBMS_CLOUD_AI.GENERATE(:P1_PROMPT,profile_name => 'COHEREPROF');
        END IF;
    EXCEPTION
    WHEN OTHERS THEN
        NULL;
    END;

Image 12

- Run the application, enter the prompt as “show all employees with departments and salaries,” and press enter.

- As we can see, Select AI has successfully decoded our natural language prompt and built the SQL query, with the classic report executed the same, to present the results.

Image 13

Use in SQL Developer Desktop

In all the above use cases, we were using stateless environments such as SQL Developer Web or SQL Workshop. In all such cases, we can only use DBMS_CLOUD_AI.GENERATE to access the Select AI features.

In the case of IDEs such as SQL Developer Desktop, in addition to invoking DBMS_CLOUD_AI.GENERATE, we can also issue direct Select AI commands to retrieve the results. The Select AI commands cannot be used in any stateless environments.

To do so, we must set the AI profile at the beginning of each database session.

Let’s run below code from the SQL Developer desktop session:

    
        BEGIN
    DBMS_CLOUD_AI.set_profile('COHEREPROF');
END;

Image 14

Now we can issue Select AI commands to the ADB and get results similar to the ones returned by the DBMS_CLOUD_AI package.

Select AI shows all employees with departments and salaries.

Image 15

Select AI shows employees in the Receivables department.

Image 16

The Select AI commands can also be issued from OML notebooks to produce similar results to those seen on the SQL Developer desktop.

Conclusion

Oracle Autonomous Database, Select AI feature, empowers organizations to harness the power of artificial intelligence. By enabling natural language processing, automating complex tasks, and providing powerful analytical tools, Select AI helps businesses unlock new insights, improve operational efficiency, and drive innovation.