SQL Tool Guidelines for Generative AI Agents

The SQL tool in Generative AI Agents supports Oracle Database (Base Database and Autonomous Database).

Create the tables and load data in your preferred database. If you're using the SQL tool to generate query statements, and then self correct or run the queries, you must set up a database tools connection to the database that contains data for the query and execution. For help with creating a connection, see Create Database Tools Connection (Guidelines).

You might not have to create tables in the database if you're only using the SQL tool to generate query statements and you're not enabling the tool to self correct or execute SQL queries. However, you'd still need to provide the database table schema when you create the SQL tool in an agent.

A valid database schema must be provided when you add a SQL tool to an agent in Generative AI Agents. SQL tool creation fails at the validation step if the schema is invalid.

A valid schema is a well-structured database blueprint that includes the following:

• Tables: Define entities (for example, Customers, Orders)

• Columns: Specify attributes (for example, CustomerID, OrderDate)

• Primary Keys: Uniquely identify rows (for example, CustomerID)

• Foreign Keys: Link tables (for example, CustomerID in Orders references Customers).

• Constraints: Enforce rules (for example, NOT NULL, UNIQUE).

Example of a valid schema:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    DepartmentID INT,
    HireDate DATE NOT NULL,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

A well-structured schema ensures data integrity and efficiency.

Example of an invalid schema:

CREATE TABLE Employees (
    EmployeeID,
    Name VARCHAR(100),
    DepartmentID INT,
    HireDate DATE
);

An invalid schema that lacks structure might lead to potential data inconsistencies and inefficiencies. In the example, the schema is invalid because:

• The schema is missing primary keys (EmployeeID, DepartmentID).
• A data type for EmployeeID is missing.

When creating a SQL tool, you can select between using a small or large model.

A small model might provide faster response times with lower compute cost. You'd select a small mode for simple queries such as "Get total sales for January 2025."

A large model might provide more accuracy but at the cost of higher latency and compute cost. You'd select a large model for more complex queries such as "Show the average sales of the top 5 performing products in Q1 2023 grouped by region."

The SQL tool supports the dialects Oracle SQL and SQLite.

Based on your preferred dialect, the agent can generate queries that align with Oracle SQL's or SQLite's syntactical rules.

Examples of syntax differences between SQLite and Oracle SQL:

Oracle: SELECT * FROM users FETCH FIRST 5 ROWS ONLY;
SQLite: SELECT * FROM users LIMIT 5;

Oracle: SELECT 'Hello' || ' World' FROM dual;
SQLite: SELECT 'Hello' || ' World';

Ensure that you have the following requirements set up if you're enabling and using the SQL tool to run the SQL queries after generating the SQL statements:

• For the ingress rule to configure on a database's private subnet, see Networking Requirements (without cross-region and cross-tenancy support).

• A vault in OCI Vault is required to store the database password secret. Create the vault in the same compartment as the database.

• If the autonomous database is enabled for Mutual TLS (mTLS) authentication, you need to download the wallet and extract a file. See Wallet Security Requirements.

You can enable a SQL tool to self correct and run a query after generating the SQL statement. When either SQL execution or self correction is enabled, a Database Tools connection to the database that contains data for the query and execution is required.

For help with creating a connection, see Create Database Tools Connection (Guidelines).

Object Storage buckets can be used to contain input files to the SQL tool. If SQL execution is enabled on a SQL tool, output files from running SQL queries can be stored in Object Storage.

Ensure that you give users access to all Generative AI Agents resources as described in Adding Policies Before You Can Use the Service.

Review also the following sections and complete the tasks that you need to use a specific function in an agent with a SQL tool (for example, enable SQL execution).

• Dynamic Group
• Object Storage
• SQL Execution, Self Correction, and Output Storage

ALL {resource.type='genaiagent'}

Allow dynamic-group <dynamic-group-name> to inspect buckets in compartment <compartment-name> where target.bucket.name = '<bucket-name>'

Allow dynamic-group <dynamic-group-name> to read objects in compartment <compartment-name> where target.bucket.name = '<bucket-name>'

Allow dynamic-group <dynamic-group-name> to use database-tools-connections in compartment <compartment-name>
Allow dynamic-group <dynamic-group-name> to read database-tools-family in compartment <compartment-name>
Allow dynamic-group <dynamic-group-name> to read secret-family in compartment <compartment-name>

allow dynamic-group <dynamic-group-name> to {BUCKET_INSPECT, BUCKET_READ, OBJECT_INSPECT, OBJECT_READ, OBJECT_CREATE, OBJECT_OVERWRITE, PAR_MANAGE} in compartment <compartment-name> where target.bucket.name = 
'<bucket-name>'

You can provide context about the database schema in the form of example questions that users might ask, and the expected SQL queries as example answers. In-context learning examples are helpful in answering similar user queries.

Examples:

Question: Show all employees who were born in CA.
Oracle SQL: SELECT * FROM Employees WHERE BIRTHSTATE = 'CA';

Question: Get the employeeid of employees who joined in 2020.
Oracle SQL: SELECT employeeID FROM Employees WHERE hireDate = '2020';

You can provide one or more prompts to modify the behavior of the agent.

Example:

Always use aggregators such as COUNT, SUM, AVG, MIN, and MAX in Oracle SQL queries that contain GROUP BY.
If the Oracle SQL query contains ORDER BY, then show the ORDER BY metric in the SELECT clause as well.
If all columns must be returned, use the (*) notation.
Ensure to include all relevant WHERE filtering conditions even when the number of conditions is larger than those of in-context learning examples.

Adding table and column descriptions can improve query accuracy, handle ambiguity, and help the model better understand complex queries and specific terms. For example, describing the status column in Orders as Order status: pending, shipped, or delivered helps the model to interpret it correctly in user queries.

Example:

Description of the important tables in the schema:

Employees         Employee names and other information
Departments       Department names and other information 

Description of the important columns of the tables in the schema:

EMPLOYEES TABLE

employees.employeeID     A unique code assigned to each employee. employeeID links the data in this file with records in the other files.
employees.departmentID   A unique code that identifies the department an employee belongs to
employees.birthYear      Year employee was born
employees.birthMonth     Month employee was born
employees.birthState     State where employee was born
employees.birthCity      City where employee was born
employees.nameFirst      Employee's first name
employees.nameLast       Employee's last name
employees.nameMiddle     Employee's middle name
employees.hireDate       Year employee joined the company

---------------------------------------------------------------------
DEPARTMENTS TABLE

department.departmentID    A unique code assigned to each department
department.departmentName  Name of the department
department.locationID      Unique code that identifies the location of a department
department.managerID       Unique code that identifies the department's manager