10.3 Generate and Improve SQL Queries

Provides examples of how to generate and improve SQL queries.

Topics:

Parent topic: Examples of Using Select AI

10.3.1 Example: Improve SQL Query Generation

These examples demonstrate how comments, annotations, foreign key, and referential integrity constraints in database tables and columns can improve the generation of SQL queries from natural language prompts.

Example: Improve SQL Generation with Table and Column Comments

If you have table and column comments in your database tables, enable "comments":"true" parameter in DBMS_CLOUD_AI.CREATE_PROFILE function to retrieve table level and column level comments. The comments are added to the metadata of the LLM for a better SQL generation. 

-- Adding comments to table 1, table 2, and table 3. Table 1 has 3 columns, table 2 has 7 columns, table 3 has 2 columns.

-- TABLE1
COMMENT ON TABLE table1 IS 'Contains movies, movie titles and the year it was released';
COMMENT ON COLUMN table1.c1 IS 'movie ids. Use this column to join to other tables';
COMMENT ON COLUMN table1.c2 IS 'movie titles';
COMMENT ON COLUMN table1.c3 IS 'year the movie was released';
-- TABLE2
COMMENT ON TABLE table2 IS 'transactions for movie views - also known as streams';
COMMENT ON COLUMN table2.c1 IS 'day the movie was streamed';
COMMENT ON COLUMN table2.c2 IS 'genre ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c3 IS 'movie ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c4 IS 'customer ids. Use this column to join to other tables';
COMMENT ON COLUMN table2.c5 IS 'device used to stream, watch or view the movie';
COMMENT ON COLUMN table2.c6 IS 'sales from the movie';
COMMENT ON COLUMN table2.c7 IS 'number of views, watched, streamed';

-- TABLE3
COMMENT ON TABLE table3 IS 'Contains the genres';
COMMENT ON COLUMN table3.c1 IS 'genre id. use this column to join to other tables';
COMMENT ON COLUMN table3.c2 IS 'name of the genre';


BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name => 'myprofile',
    attributes =>       
        '{"provider": "azure",
          "azure_resource_name": "my_resource",                    
          "azure_deployment_name": "my_deployment",
          "credential_name": "my_credential",
          "comments":"true", 
          "object_list": [
            {"owner": "moviestream", "name": "table1"},
            {"owner": "moviestream", "name": "table2"},
            {"owner": " moviestream", "name": "table3"}             
          ]          
          }'
    );

    DBMS_CLOUD_AI.SET_PROFILE(
        profile_name => 'myprofile'
    );

END;
/

--Prompts
select ai what are our total views;
RESPONSE
-------------------------------------------------
TOTAL_VIEWS
-----------
   97890562

select ai showsql what are our total views;

RESPONSE                                                                 
-------------------------------------------------------------------------
SELECT SUM(QUANTITY_SOLD) AS total_views
FROM "moviestream"."table"

select ai what are our total views broken out by device;

DEVICE                     TOTAL_VIEWS
-------------------------- -----------
mac                           14719238
iphone                        20793516
ipad                          15890590
pc                            14715169
galaxy                        10587343
pixel                         10593551
lenovo                         5294239
fire                           5296916

8 rows selected. 

select ai showsql what are our total views broken out by device;

RESPONSE                                                                               
---------------------------------------------------------------------------------------
SELECT DEVICE, COUNT(*) AS TOTAL_VIEWS
FROM "moviestream"."table"
GROUP BY DEVICE
Example: Improve SQL Query Generation with Table and Column Annotations

This example demonstrates the integration of annotations in Select AI, applicable in Oracle AI Database 26ai. The annotations are added to the metadata that is sent to the LLM.

If you have a table with annotations in your schema, enable "annotations":"true" in the DBMS_CLOUD_AI.CREATE_PROFILE function to instruct Select AI to add annotations to the metadata. 

--
-- Annotations
--
 
CREATE TABLE emp2 (
    empno NUMBER,
    ename VARCHAR2(50) ANNOTATIONS (display 'lastname'),
    salary NUMBER ANNOTATIONS ("person_salary", "column_hidden"),
    deptno NUMBER ANNOTATIONS (display 'department')
)ANNOTATIONS (requires_audit 'yes', version '1.0', ówner 'HR Organization'); 
 
Table created.
 
 BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
   profile_name => 'GOOGLE_ANNOTATIONS',
   attributes   => '{"provider": "google",
      "credential_name": "GOOGLE_CRED",
      "object_list": [{"owner": "ADB_USER", "name": "emp2"}],
      "annotations" : "true"
      }');
END;
/ 
 
PL/SQL procedure successfully completed.
 
EXEC DBMS_CLOUD_AI.SET_PROFILE('GOOGLE_ANNOTATIONS');
 
PL/SQL procedure successfully completed.
Example: Improve SQL Query Generation with Foreign Key and Referential Key Constraints

This example demonstrates the ability of the LLM to generate accurate JOIN conditions by retrieving the foreign key and referential key constraints into the metadata of the LLM. The foreign key and referential key constraints provide structured relationship data between the tables to the LLM.

Enable "constraints":"true" in the DBMS_CLOUD_AI.CREATE_PROFILE function for Select AI to retrieve foreign key and referential key. 

--
-- Referential Constraints
--
CREATE TABLE dept_test (
    deptno NUMBER PRIMARY KEY,
    dname VARCHAR2(50)
); 
 
Table created.
 
CREATE TABLE emp3 (
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(50),
    salary NUMBER,
    deptno NUMBER,
    CONSTRAINT emp_dept_fk FOREIGN KEY (deptno) REFERENCES dept_test(deptno)
); 
 
Table created.
 
 
BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name=>'GOOGLE_CONSTRAINTS',
    attribues   =>'{"provider": "google",
      "credential_name": "GOOGLE_CRED",
      "object_list": [{"owner": "ADB_USER", "name": "dept_test"},
                      {"owner": "ADB_USER", "name": "emp3"}],
      "constraints" : "true"
      }');
END;
/
 
PL/SQL procedure successfully completed.
 
EXEC DBMS_CLOUD_AI.SET_PROFILE('GOOGLE_CONSTRAINTS');
 
PL/SQL procedure successfully completed.

Parent topic: Generate and Improve SQL Queries

10.3.2 Example: Enable or Disable Data Access

This example illustrates how administrators can control data access and prevent Select AI from sending actual schema tables to the LLM.

Disabling Data Access

To restrict access to schema tables, log in as an administrator and run the following procedure.

EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;
 
PL/SQL procedure successfully completed.

Disabling data access limits Select AI's narrate action and Synthetic Data Generation. The narrate action and synthetic data generation raise an error.

Log in as database user, create and configure your AI profile. Review Perform Prerequisites for Select AI to configure your AI profile. 

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
          profile_name =>'DATA_ACCESS',
          attributes   =>'{"provider": "openai",
            "credential_name": "OPENAI_CRED",
            "object_list": [{"owner":"SH"}]
          }');
END;
/

EXEC DBMS_CLOUD_AI.SET_PROFILE('DATA_ACCESS');
 

select ai how many customers;

NUM_CUSTOMERS
55500

select ai narrate what are the top 3 customers in San Francisco;

ORA-20000: Data access is disabled for SELECT AI.
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 2228
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 13157
ORA-06512: at line 1 https://docs.oracle.com/error-help/db/ora-20000/
The stored procedure 'raise_application_error' was called which causes this error to be generated
Error at Line: 1 Column: 6

The following example shows the errors that are triggered when you try to generate synthetic data.

BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'DATA_ACCESS_SDG',
object_name => 'CUSTOMERS_NEW',
owner_name => 'ADB_USER,
record_count => 5
);
END;
/ 

ERROR at line 1:

ORA-20000: Data access is disabled for SELECT AI.
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 2228
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_AI", line 13401

ORA-06512: at line 2
Enabling Data Access

The following example shows enabling data access. Log in as an administrator and run the following procedure:

EXEC DBMS_CLOUD_AI.ENABLE_DATA_ACCESS;
 
PL/SQL procedure successfully completed.

Log in as database user, create and configure your AI profile. Review Perform Prerequisites for Select AI to configure your AI profile. Run narrate action and separately generate synthetic data. 

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
          profile_name =>'DATA_ACCESS_NEW',
          attributes   =>'{"provider": "openai",
            "credential_name": "OPENAI_CRED",
            "object_list": [{"owner":"SH"}]
          }');
   END;
   /

PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.SET_PROFILE('DATA_ACCESS_NEW');

PL/SQL procedure successfully completed.


select ai how many customers;

NUM_CUSTOMERS
55500

select ai narrate what are the top 3 customers in San Francisco;

"RESPONSE"
"The top 3 customers in San Francisco are Cody Seto, Lauren Yaskovich, and Ian Mc"

The following example shows successful synthetic data generation after enabling data access.

BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'DATA_ACCESS_SDG',
object_name => 'CUSTOMERS_NEW',
owner_name => 'ADB_USER',
record_count => 5
);
END;
/ 

PL/SQL procedure successfully completed.

Parent topic: Generate and Improve SQL Queries

10.3.3 Example: Restrict Table Access in AI Profile

This example demonstrates how to restrict table access and instruct the LLM to use only the tables specified in the object_list of the AI profile.

Set enforce_object_list to true to restrict table access to the LLM.

As a database user, create and configure your AI profile. Review Perform Prerequisites for Select AI to configure your AI profile. 

BEGIN
      DBMS_CLOUD_AI.CREATE_PROFILE(
        profile_name =>'GOOGLE_ENFORCED',
        attributes   =>'{"provider": "google",
          "credential_name": "GOOGLE_CRED",
          "object_list": [{"owner": "ADB_USER", "name": "GENRE"},
                  {"owner": "ADB_USER", "name": "CUSTOMER"},
                  {"owner": "ADB_USER", "name": "PIZZA_SHOP"},
                  {"owner": "ADB_USER", "name": "STREAMS"},
                  {"owner": "ADB_USER", "name": "MOVIES"},
                  {"owner": "ADB_USER", "name": "ACTIONS"}],
          "enforce_object_list" : "true"
          }');
END;
/
 
PL/SQL procedure successfully completed.
 

EXEC DBMS_CLOUD_AI.set_profile('GOOGLE_ENFORCED');
 
PL/SQL procedure successfully completed.
 
select ai showsql please list the user tables;
 
RESPONSE
--------------------------------------------------------------------------------------------
SELECT 'ADB_USER.GENRE' AS TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.CUSTOMER' AS 
TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.PIZZA_SHOP' AS TABLE_NAME FROM DUAL UNION
 ALL SELECT 'ADB_USER.STREAMS' AS TABLE_NAME FROM DUAL UNION ALL SELECT 'ADB_USER.MOVIES' 
AS TABLE_NAME FROM DUAL 
--

Setting enforce_object_list to false instructs the LLM to use other tables and views based on its prior knowledge. 

BEGIN
      DBMS_CLOUD_AI.CREATE_PROFILE(
        profile_name =>'GOOGLE_ENFORCED1',
        attributes   =>'{"provider": "google",
          "credential_name": "GOOGLE_CRED",
          "object_list": [{"owner": "ADB_USER", "name": "GENRE"},
                  {"owner": "ADB_USER", "name": "CUSTOMER"},
                  {"owner": "ADB_USER", "name": "PIZZA_SHOP"},
                  {"owner": "ADB_USER", "name": "STREAMS"},
                  {"owner": "ADB_USER", "name": "MOVIES"},
                  {"owner": "ADB_USER", "name": "ACTIONS"}],
          "enforce_object_list" : "false"
          }');
END;
/
 
PL/SQL procedure successfully completed.
 

EXEC DBMS_CLOUD_AI.set_profile('GOOGLE_ENFORCED1');
 
PL/SQL procedure successfully completed.
 
select ai showsql please list the user tables;
RESPONSE
----------------------------------
SELECT TABLE_NAME FROM USER_TABLES

Parent topic: Generate and Improve SQL Queries

10.3.4 Example: Specify Case Sensitivity for Columns

This example shows how you can set case sensitivity for columns in AI profile.

Set case_sensitive_values to false to retrieve queries that are not case sensitive.

As a database user, create and configure your AI profile. Review Perform Prerequisites for Select AI to configure your AI profile. 

BEGIN
      DBMS_CLOUD_AI.create_profile(
        profile_name   =>'GOOGLE',
        attributes     =>'{"provider": "google",
          "credential_name": "GOOGLE_CRED",
          "object_list": [{"owner": "ADB_USER", "name": "GENRE"},
                  {"owner": "ADB_USER", "name": "CUSTOMER"},
                  {"owner": "ADB_USER", "name": "PIZZA_SHOP"},
                  {"owner": "ADB_USER", "name": "STREAMS"},
                 {"owner": "ADB_USER", "name": "MOVIES"},
                 {"owner": "ADB_USER", "name": "ACTIONS"}],
         "case_sensitive_values" : "false"
         }');
 END;
 /
 
PL/SQL procedure successfully completed.
 
-- With "case_sensitive_values" set to "false", LLM will give back case insensitive query.

select ai showsql how many people watch Inception;
 
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c.CUSTOMER_ID) AS "COUNT"
FROM "ADB_USER"."CUSTOMER" c
JOIN "ADB_USER"."STREAMS" s ON c.CUSTOMER_ID = s.CUSTOMER_ID
JOIN "ADB_USER"."MOVIES" m ON s.MOVIE_ID = m.MOVIE_ID
WHERE UPPER(m.TITLE) = UPPER('Inception')

You can specify case sensitive query using double quotes even though the case_sensitive_values is set to false. 

select ai showsql how many people watch "Inception";
 
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c.CUSTOMER_ID) AS "COUNT" 
FROM "ADB_USER"."CUSTOMER" c JOIN "ADB_USER"."STREAMS" s ON 
c.CUSTOMER_ID = s.CUSTOMER_ID JOIN "ADB_USER"."MOVIES" m ON 
s.MOVIE_ID = m.MOVIE_ID WHERE m.TITLE = 'Inception'

Parent topic: Generate and Improve SQL Queries