Generate and Improve SQL Queries

10.3 Generate and Improve SQL Queries

Provides examples of how to generate and improve SQL queries.

Topics:

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: Select AI Feedback
These examples demonstrate how you can use the DBMS_CLOUD_AI.FEEDBACK procedure and the different scenarios for providing feedback to improve subsequent SQL query generation.
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.
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.
Example: Specify Case Sensitivity for Columns
This example shows how you can set case sensitivity for columns in AI profile.

Parent topic: Examples 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.

Example: Automatically Detect Relevant Table Metadata

These examples shows how Select AI automatically detects relevant tables and sends metadata only for those specific tables relevant to the query in Oracle AI Database 26ai. To enable this feature, set object_list_mode to automated. This automatically creates a vector index named <profile_name>_OBJECT_LIST_VECINDEX. The vector index is initialized with default attributes and values such as refresh_rate, similarity_threshold, and match_limit. You can modify some of the attributes through DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX. See UPDATE_VECTOR_INDEX Procedure for more information.

One profile is configured to use object_list to specify the schema or the objects in the schema while the other does not specify object_list. However, the same SQL construct is expected.

Review Perform Prerequisites for Select AI to provide access to the DBMS_CLOUD_AI package and provide network access to the AI provider.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name=>'OCI_AUTO',
    attributes=>'{"provider": "oci",                                                                 
      "credential_name": "GENAI_CRED",
      "object_list": [{"owner": "SH"}],
      "oci_compartment_id": "ocid1.compartment.oc1..aaaa...",
      "model" : "meta.llama-3.3-70b-instruct"
      }');
END;
/  

PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_AUTO');

PL/SQL procedure successfully completed.

select ai showsql how many customers in San Francisco are married;

RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID") AS "NUMBER_OF_CUSTOMERS"
FROM "SH"."CUSTOMERS" c
WHERE UPPER(c."CUST_CITY") = UPPER('San Francisco')
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('married')

The following example compares the same scenario without using object_list. When you don't specify object_list, Select AI automatically chooses all objects available to the current schema.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name=>'OCI_AUTO1',
    attributes=>'{"provider": "oci",                                                                 
      "credential_name": "GENAI_CRED",
      "oci_compartment_id": "ocid1.compartment.oc1..aaaa...",
      "object_list_mode": "automated",
      "model" : "meta.llama-3.3-70b-instruct"
      }');
END;
/  
PL/SQL procedure successfully completed.

EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_AUTO1');

PL/SQL procedure successfully completed.

select ai showsql how many customers in San Francisco are married?;

RESPONSE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(c."CUST_ID") AS "Number_of_Customers" 
FROM "SH"."CUSTOMERS" c 
WHERE UPPER(c."CUST_CITY") = UPPER('San Francisco') 
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married')

Parent topic: Generate and Improve SQL Queries

10.3.2 Example: Select AI Feedback

These examples demonstrate how you can use the DBMS_CLOUD_AI.FEEDBACK procedure and the different scenarios for providing feedback to improve subsequent SQL query generation.

Before You Begin

Review Perform Prerequisites for Select AI.

Note:

You can provide feedback for Select AI SQL prompts even if the prompt has not been run previously. Select AI does not require the SQL prompt to be used in a query before submitting feedback; you may supply feedback for any valid prompt at any time.

Example: Provide Negative Feedback

The following example demonstrates providing corrections to the generated SQL as feedback (negative feedback) using feedback_type as negative and providing your SQL query.

You add your feedback to the AI profile named OCI_FEEDBACK1 by calling the DBMS_CLOUD_AI.FEEDBACK procedure with the sql_text parameter containing the prompt. See FEEDBACK Procedure to learn about the attributes. Then, you retrieve the content and attributes columns from the <profile_name>_FEEDBACK_VECINDEX$VECTAB table, which is linked to that specific SQL query. Select AI automatically creates this vector table when you first use the feedback feature. See Vector Index for FEEDBACK for more information.

SQL> select ai showsql how many movies;
 
RESPONSE
------------------------------------------------------------------------
SELECT COUNT(m."MOVIE_ID") AS "Number of Movies" FROM "ADB_USER"."MOVIES" m
 
SQL> exec DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1', sql_text=> 'select ai showsql how many movies', feedback_type=> 'negative', response=>'SELECT SUM(1) FROM "ADB_USER"."MOVIES"');
 
PL/SQL procedure successfully completed.
SQL> select CONTENT, ATTRIBUTES from OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB where JSON_VALUE(attributes, '$.sql_text') = 'select ai showsql how many movies';
 
CONTENT                                                
----------------------------------------------------------------------------------------------------
how many movies                                             
ATTRIBUTES
----------------------------------------------------------------------------------------------------
 
{"response":"SELECT SUM(1) FROM \"ADB_USER\".\"MOVIES\"","feedback_type":"negative","sql_id":null,"sql_text":"select ai showsql how many movies","feedback_content":null}

Example: Provide Positive Feedback

The following example demonstrates providing your approval that you agree and confirm the generated SQL (positive feedback) using feedback_type as positive.

In this example, the query retrieves the sql_id from the v$mapped_sql view for the given prompt. See V_MAPPED_SQL for more information.

You add your feedback to the AI profile named OCI_FEEDBACK1 by calling the DBMS_CLOUD_AI.FEEDBACK procedure with the sql_id parameter. Then, you retrieve the content and attributes columns from the <profile_name>_FEEDBACK_VECINDEX$VECTAB table, which is linked to that specific SQL query. Select AI automatically creates this vector table when you first use the feedback feature. See Vector Index for FEEDBACK for more information.

SQL> select ai showsql how many distinct movie genres?;
 
RESPONSE
-----------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT g."GENRE_NAME") AS "Number of Movie Genres" FROM "ADB_USER"."GENRES" g
 
SQL> SELECT sql_id FROM v$mapped_sql WHERE sql_text = 'select ai showsql how many distinct movie genres?';
 
SQL_ID
-------------
852w8u83gktc1
 
SQL> exec DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1', sql_id=> '852w8u83gktc1', feedback_type=>'positive', operation=>'add');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT content, attributes FROM OCI_FEEDBACK1_FEEDBACK_VECINDEX$VECTAB WHERE JSON_VALUE(attributes, '$.sql_id') ='852w8u83gktc1';
 
 CONTENT                                               
---------------------------------------------------------------------------------------------------- 
how many distinct movie genres?
ATTRIBUTES
----------------------------------------------------------------------------------------------------
{"response":"SELECT COUNT(DISTINCT g.\"GENRE_NAME\") AS \"Number of Movie Genres\" FROM \"ADB_USER\".\"GENRES\" g","feedback_type":"positive","sql_id":"852w8u83gktc1","sql_text":"select ai showsql how many distinct movie genres?","feedback_content":null}

Example: Provide Feedback Without Prior Usage

You may provide feedback for SQL prompts even if the prompt has not been used previously. For example:

BEGIN
  DBMS_CLOUD_AI.FEEDBACK(
    profile_name=>'AI_PROFILE',
    sql_text=>'select ai runsql how many products named PAD', -- Prior usage not required
    feedback_type=>'negative',
    response=>'SELECT COUNT(*) AS "Num" FROM "PRODUCTS"."CATG" o WHERE UPPER(o."NAME") LIKE ''%PAD%''',
    feedback_content=>'Use LIKE instead of ='
  );
END;
/

In this case, feedback for the prompt select ai runsql how many products named PAD is submitted without needing to use the prompt beforehand.

Example: Add or Delete Your Feedback for the Generated SQL

The following example demonstrates adding or deleting your feedback for the generated SQL by specifying the DBMS_CLOUD_AI.FEEDBACK procedure parameters. This example demonstrates using sql_id and sql_text along with other parameters.

Note:

Select AI allows only a single feedback entry for each sql_id. If you provide additional feedback for the same sql_id, Select AI replaces the previous entry with the new one.

See FEEDBACK Procedure for more details on the parameters.

EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
                                   sql_id=> '852w8u83gktc1',
                                   feedback_type=>'positive',
                                   operation=>'add');
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
                                   sql_text=> 'select ai showsql how many movies',
                                   feedback_type=> 'negative',
                                   response=>'SELECT SUM(1) FROM "ADB_USER"."MOVIES"',
                                   feedback_content=>'Use SUM instead of COUNT');
EXEC DBMS_CLOUD_AI.FEEDBACK(profile_name=>'OCI_FEEDBACK1',
                                   sql_id=> '852w8u83gktc1',
                                   operation=>'delete');

Example: Use Feedback Action with the Last AI SQL to Provide Negative Feedback

This example demonstrates using feedback action to improve the generated SQL by suggesting the modifications using natural language.

BEGIN
  DBMS_CLOUD_AI.CREATE_PROFILE(
    profile_name=>'OCI_FEEDBACK1',
    attributes=>'{"provider": "oci",                                                                 
      "credential_name": "GENAI_CRED",
      "oci_compartment_id": "ocid1.compartment.oc1..aaaa...",
      "object_list": [{"owner": "ADB_USER", "name": "users"},
                      {"owner": "ADB_USER", "name": "movies"},
                      {"owner": "ADB_USER", "name": "genres"},
                      {"owner": "ADB_USER", "name": "watch_history"},
                      {"owner": "ADB_USER", "name": "movie_genres"},
                      {"owner": "ADB_USER", "name": "employees1"},
                      {"owner": "ADB_USER", "name": "employees2"}
                        ]
      }');
END;
/
 
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_FEEDBACK1');

 
PL/SQL procedure successfully completed.
 

select ai showsql rank movie duration;
 
RESPONSE
-------------------------------------------------------------------------------
SELECT "DURATION" AS "Movie Duration" FROM "ADB_USER"."MOVIES" ORDER BY "DURATION"
 
select ai feedback use ascending sorting;
 
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "rank movie duration" is successfully refined. The refined SQL query as following:
SELECT m."DURATION" AS "Movie Duration" FROM "ADB_USER."MOVIES" m ORDER BY m."DURATION" ASC
 
 
select ai showsql rank the movie duration;
 
RESPONSE
-----------------------------------------------------------------------------------------
SELECT m."DURATION" AS "Movie Duration" FROM "ADB_USER."MOVIES" m ORDER BY m."DURATION" ASC

Example: Use Feedback Action with the Last AI SQL to Provide Positive Feedback

This example demonstrates using the feedback action to accept the generated SQL using natural language.

--Positive feedback

select ai showsql which movies are comedy?;
 
RESPONSE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT DISTINCT m."TITLE" AS "Movie Title" FROM "ADB_USER"."MOVIES" m INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID" INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID" WHERE g."GENRE_NAME" = 'comedy'
 
select ai feedback this is correct;
 
RESPONSE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Thank you for your positive feedback. The SQL query for prompt "which movies are comedy?" is correctly implemented and delivering the expected results. It will be referenced for future optimizations and improvements.


Select AI Feedback Action Referring SQL_ID

Example: Use Feedback Action with SQL_ID to Provide Feedback

This example demonstrates using SQL_ID with the feedback action to provide feedback for a particular generated SQL query. You can obtain the SQL_ID by querying the v$MAPPED_SQL table.

-- Query mentioned with SQL_ID

select ai showsql how many movies are in each genre;
 
RESPONSE
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT g."GENRE_NAME" AS "Genre Name", COUNT(m."MOVIE_ID") AS "Number of Movies" FROM "ADB_USER"."MOVIES" m INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID" INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID" GROUP BY g."GENRE_NAME"
 
select sql_id from v$cloud_ai_sql where sql_text = 'select ai showsql how many movies are in each genre';
 
SQL_ID
-------------
8azkwc0hr87ga
 
select ai feedback for query with sql_id = '8azkwc0hr87ga', rank in descending sorting;
 
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "how many movies are in each genre" is successfully refined. The refined SQL query as following:
SELECT g."GENRE_NAME" AS "Genre Name", COUNT(m."MOVIE_ID") AS "Number of Movies"
FROM "ADB_USER"."MOVIES" m
INNER JOIN "ADB_USER"."MOVIE_GENRES" mg ON m."MOVIE_ID" = mg."MOVIE_ID"
INNER JOIN "ADB_USER"."GENRES" g ON mg."GENRE_ID" = g."GENRE_ID"
GROUP BY g."GENRE_NAME"
ORDER BY COUNT(m."MOVIE_ID") DESC

Example:Use Feedback Action with Query Text

This example shows the feedback action for a specific Select AI query by including the Select AI prompt in quotes followed by your feedback.

-Query mentioned with SQL_TEXT

select ai showsql how many watch history in total;
 
RESPONSE
----------------------------------------------------------------------------------
SELECT COUNT(w."WATCH_ID") AS "Total Watch History" FROM "ADB_USER"."WATCH_HISTORY" w
 
select ai feedback for query "select ai showsql how many watch history in total", name the column as total_watch;
 
RESPONSE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Based on your feedback, the SQL query for prompt "how many watch history in total" is successfully refined. The refined SQL query as following:
SELECT COUNT(w."WATCH_ID") AS "total_watch" FROM "ADB_USER"."WATCH_HISTORY" w

Parent topic: Generate and Improve SQL Queries

10.3.3 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.4 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.5 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