Exemplos de Uso do Select AI
Explore a integração do Select AI da Oracle com vários provedores de IA suportados para gerar, executar e explicar SQL de prompts de linguagem natural ou conversar com o LLM.
- Exemplo: Selecionar Ações do AI
Esses exemplos ilustram ações comuns do Select AI. - Exemplo: Selecionar IA com a OCI Generative AI
Esses exemplos mostram como você pode acessar a OCI Generative AI usando sua chave de API da OCI ou Controlador de Recursos, criar um perfil de IA e gerar, executar e explicar SQL de prompts de linguagem natural ou chat usando os LLMs da OCI Generative AI. - Exemplo: Selecione AI com OpenAI
Este exemplo mostra como você pode usar OpenAI para gerar instruções SQL de prompts de linguagem natural. - Exemplo: Selecionar AI com Cohere
Este exemplo mostra como você pode usar Cohere para gerar instruções SQL de prompts de linguagem natural. - Exemplo: Selecionar IA com o Serviço OpenAI do Azure
Os exemplos a seguir mostram como você pode ativar o acesso ao Serviço OpenAI do Azure usando sua chave de API ou usar o Controlador de Serviços OpenAI do Azure, criar um perfil de IA e gerar SQL com base em prompts de linguagem natural. - Exemplo: Selecionar IA com o Google
Este exemplo mostra como você pode usar o Google para gerar, executar e explicar SQL de prompts de linguagem natural ou chat usando o LLM do Google Gemini. - Exemplo: Selecionar IA com Antrópico
Este exemplo mostra como você pode usar Antrópico para gerar, executar e explicar SQL de prompts de linguagem natural ou chat usando o LLM Antrópico Claude. - Exemplo: Selecionar AI com Face de Abraço
Este exemplo mostra como você pode usar a Face de Abraço para gerar, executar e explicar SQL de prompts de linguagem natural ou chat usando o LLM de Face de Abraço. - Exemplo: Ativar Conversas em Selecionar IA
Este exemplo ilustra como ativar conversas em Selecionar IA. - Exemplo: Configurar e Usar Selecionar IA com RAG
Este exemplo orienta você na configuração de credenciais, na configuração do acesso à rede e na criação de um índice vetorial para integrar os serviços de nuvem de armazenamento de vetores do OCI Generative AI com OpenAI usando o Oracle Autonomous Database. - Exemplo: Selecionar AI com Modelos de Transformador no Banco de Dados
Este exemplo demonstra como você pode importar um modelo de transformador pré-treinado que é armazenado no armazenamento de objetos da Oracle para sua instância do Oracle Database 23ai e, em seguida, usar o modelo importado no banco de dados no perfil Selecionar AI para gerar incorporações de vetor para partes de documento e prompts do usuário. - Exemplo: Melhorar a Geração de Consultas SQL
Esses exemplos demonstram como comentários, anotações, chave estrangeira e restrições de integridade referencial em tabelas e colunas de banco de dados podem melhorar a geração de consultas SQL a partir de prompts de linguagem natural. - Exemplo: Gerar Dados Sintéticos
Este exemplo explora como você pode gerar dados sintéticos imitando as características e a distribuição de dados reais. - Exemplo: Ativar ou Desativar Acesso a Dados
Este exemplo ilustra como os administradores podem controlar o acesso a dados e impedir que o Select AI envie tabelas de esquema reais para o LLM. - Exemplo: Restringir o Acesso à Tabela no Perfil do AI
Este exemplo demonstra como restringir o acesso à tabela e instruir o LLM a usar somente as tabelas especificadas noobject_listdo perfil do AI. - Exemplo: Especificar Distinção entre Maiúsculas e Minúsculas para Colunas
Este exemplo mostra como você pode definir a distinção entre maiúsculas e minúsculas para colunas no perfil do AI.
Exemplo: Selecionar Ações do AI
Esses exemplos ilustram ações comuns do Select AI.
O exemplo a seguir ilustra ações como runsql (o padrão), showsql, narrate, chat e explainsql que você pode executar com SELECT AI. Esses exemplos usam o esquema sh com o provedor de IA e atributos de perfil definidos na função DBMS_CLOUD_AI.CREATE_PROFILE.
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai showsql how many customers exist;
RESPONSE
----------------------------------------------------
SELECT COUNT(*) AS total_customers
FROM SH.CUSTOMERS
SQL> select ai narrate how many customers exist;
RESPONSE
------------------------------------------------------
There are a total of 55,500 customers in the database.
SQL> select ai chat how many customers exist;
RESPONSE
--------------------------------------------------------------------------------
It is impossible to determine the exact number of customers that exist as it con
stantly changes due to various factors such as population growth, new businesses
, and customer turnover. Additionally, the term "customer" can refer to individu
als, businesses, or organizations, making it difficult to provide a specific num
ber.
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.Tópico principal: Exemplos de Uso do Select AI
Exemplo: Selecionar IA com a OCI Generative AI
Estes exemplos mostram como você pode acessar a OCI Generative AI usando sua chave de API ou Controlador de Recursos da OCI, criar um perfil de IA e gerar, executar e explicar SQL de prompts de linguagem natural ou chat usando os LLMs da OCI Generative AI.
Observação
Se você não especificar o parâmetro
Se você não especificar o parâmetro
model_name, o OCI Generative AI usará o modelo padrão de acordo com a tabela em Selecionar seu Provedor de IA e LLMs. Para saber mais sobre os parâmetros, consulte Atributos de Perfil.
-- Create Credential with OCI API key
--
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous Database;
RESPONSE
An Autonomous Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous Database is a popular example of an Autonomous Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous Database services as part of their cloud offerings.
In summary, Autonomous Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.Exemplo: Selecionar IA com o Controlador de Recursos do OCI Generative AI
Para usar o controlador de recursos com o OCI Generative AI, o administrador da tenancy do Oracle Cloud Infrastructure deve conceder acesso para recursos do Generative AI a um grupo dinâmico. Consulte Executar Pré-requisitos para Usar o Controlador de Recursos com o Autonomous Database para fornecer acesso a um grupo dinâmico.
Defina as políticas necessárias para obter acesso a todos os recursos do serviço Generative AI. Consulte Obtendo Acesso à IA Generativa para saber mais sobre políticas de IA Generativa.
-
Para obter acesso a todos os recursos do serviço Generative AI em toda a tenancy, use a seguinte política:
allow group <your-group-name> to manage generative-ai-family in tenancy -
Para obter acesso a todos os recursos do serviço Generative AI em seu compartimento, use a seguinte política:
allow group <your-group-name> to manage generative-ai-family in compartment <your-compartment-name>
Conecte-se como administrador e ative o controlador de recursos do OCI. Consulte ENABLE_PRINCIPAL_AUTH Procedure para configurar os parâmetros.
Observação
O OCI Generative AI usará
O OCI Generative AI usará
meta.llama-3-70b-instruct como o modelo padrão se você não especificar o model. Para saber mais sobre os parâmetros, consulte Atributos de Perfil.
-- Connect as Administrator user and enable OCI resource principal.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'OCI');
END;
/
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "OCI$RESOURCE_PRINCIPAL",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"GENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
Number of Customers
-------------------
55500
SQL> select ai how many customers in San Francisco are married;
COUNT(DISTINCTC."CUST_ID")
--------------------------
28
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT COUNT(DISTINCT c."CUST_ID")
FROM "SH"."CUSTOMERS" c
JOIN "SH"."COUNTRIES" co ON c."COUNTRY_ID" = co."COUNTRY_ID"
WHERE c."CUST_CITY" = 'San Francisco' AND c."CUST_MARITAL_STATUS" = 'married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
Here is the Oracle SQL query to find the number of customers in San Francisco who are married:
```
SELECT COUNT(*)
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco'
AND c."CUST_MARITAL_STATUS" = 'Married';
```
Explanation:
* We use the `COUNT(*)` aggregate function to count the number of rows that match the conditions.
* We specify the table alias `c` for the `"SH"."CUSTOMERS"` table to make the query more readable.
* We use the `WHERE` clause to filter the rows based on two conditions:
+ `c."CUST_CITY" = 'San Francisco'`: We filter the rows where the customer's city is San Francisco.
+ `c."CUST_MARITAL_STATUS" = 'Married'`: We filter the rows where the customer's marital status is Married.
* The double quotes around the table and column names are used to make the query case-sensitive, as required in Oracle SQL.
Note: The column names and table names are assumed to be case-sensitive, so we enclose them in double quotes. If the column names and table names are not case-sensitive, you can remove the double quotes.
Collapse
This snippet was truncated for display; see it in full
SQL> select ai narrate how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------
There are 28 customers in San Francisco who are married.
SQL> select ai chat what is Autonomous Database;
RESPONSE
An Autonomous Database is a type of database that uses artificial intelligence (AI) and machine learning (ML) to automate many of the administrative and maintenance tasks typically performed by a database administrator (DBA). This allows the database to manage itself, without human intervention, to a large extent.
Autonomous databases are designed to be self-driving, self-securing, and self-repairing, which means they can:
1. **Automate administrative tasks**: Such as provisioning, patching, upgrading, and tuning, which frees up DBAs to focus on higher-level tasks.
2. **Optimize performance**: By automatically adjusting parameters, indexing, and caching to ensure optimal performance and efficiency.
3. **Detect and respond to security threats**: By using AI-powered security tools to identify and respond to potential security threats in real-time.
4. **Heal itself**: By automatically detecting and repairing errors, corruption, or other issues that may arise.
5. **Scale up or down**: To match changing workload demands, without the need for manual intervention.
The benefits of Autonomous Databases include:
1. **Increased efficiency**: By automating routine tasks, DBAs can focus on more strategic activities.
2. **Improved performance**: Autonomous databases can optimize performance in real-time, leading to faster query response times and better overall system performance.
3. **Enhanced security**: AI-powered security tools can detect and respond to threats more quickly and effectively than human administrators.
4. **Reduced costs**: By minimizing the need for manual intervention, Autonomous Databases can help reduce labor costs and improve resource utilization.
5. **Improved reliability**: Autonomous Databases can detect and repair errors more quickly, reducing downtime and improving overall system reliability.
Oracle Autonomous Database is a popular example of an Autonomous Database, which was introduced in 2018. Other vendors, such as Amazon, Microsoft, and Google, also offer Autonomous Database services as part of their cloud offerings.
In summary, Autonomous Databases are designed to be self-managing, self-optimizing, and self-healing, which can lead to improved performance, security, and efficiency, while reducing costs and administrative burdens.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GENAI');
PL/SQL procedure successfully completed.Exemplo: Selecionar IA com o OCI Generative AI Usando o Modelo LLAMA
Este exemplo mostra o recurso chat do OCI Generative AI. Ele destaca os recursos do modelo por meio de dois prompts: analisar os comentários dos clientes para gaze seu sentimento e gerar um parágrafo introdutório sobre escalada.
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaa',
tenancy_ocid => 'ocid1.tenancy.oc1..aaa',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
PL/SQL procedure successfully completed.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'GENAI',
attributes =>'{"provider": "oci",
"object_list": [
{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}
]
"model": "meta.llama-3.3-70b-instruct",
"oci_apiformat":"GENERIC",
"credential_name": "GENAI_CRED",
"oci_compartment_id": "ocid1.compartment.oc1..."}');
END;
/
PL/SQL procedure successfully completed.
--
--Set profile
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('GENAI');
PL/SQL procedure successfully completed.
SQL> set linesize 150
SQL> SELECT AI chat what is the sentiment of this comment I am not going to waste my time filling up this three page form. Lousy idea;
SQL>
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
The sentiment of this comment is strongly negative. The user is expressing frustration and annoyance with the idea of filling out a three-page form, an
d is explicitly stating that they consider it a "lousy idea". The use of the phrase "waste my time" also implies that they feel the task is unnecessary
and unproductive. The tone is dismissive and critical.
SQL> SELECT AI chat Write an enthusiastic introductory paragraph on how to get started with rock climbing with Athletes as the target audience;
RESPONSE
------------------------------------------------------------------------------------------------------------------------------------------------------
Rock climbing is an exhilarating and challenging sport that's perfect for athletes looking to push their limits and test their strength, endurance,
and mental toughness. Whether you're a seasoned athlete or just starting out, rock climbing offers a unique and rewarding experience that will have
you hooked from the very first climb. With its combination of physical and mental challenges, rock climbing is a great way to build strength, improve
flexibility, and develop problem-solving skills. Plus, with the supportive community of climbers and the breathtaking views from the top of the climb,
you'll be hooked from the very first climb. So, if you're ready to take on a new challenge and experience the thrill of adventure, then it's time to
get started with rock climbing!Usando a OCI Generative AI com o Modelo Padrão
O exemplo a seguir usa o Modelo de Chat da OCI Generative AI padrão. Se você não especificar o parâmetro model_name, o OCI Generative AI usará o modelo padrão de acordo com a tabela em Selecionar seu Provedor de IA e LLMs.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_DEFAULT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}]
}');
END;
/Usando a OCI Generative AI com o Modelo de Chat
O exemplo a seguir usa cohere.command-r-plus-08-2024 como o Modelo de Chat do OCI Generative AI.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_COHERE_COMMAND_R_PLUS',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"model": "cohere.command-r-plus-08-2024"
}');
END;
/Usando o OCI Generative AI com o ID do Ponto Final do Modelo de Chat
O exemplo a seguir demonstra como especificar o ID do ponto final do Modelo de Chat do OCI Generative AI em vez de model. Se você estiver usando o ID do ponto final do Modelo de Chat Meta Llama, especifique oci_apiformat como GENERIC.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_ENDPOINT',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"oci_endpoint_id": "<endpoint_id>",
"oci_apiformat": "GENERIC"
}');
END;
/Usando o OCI Generative AI com o OCID do Modelo de Chat
Este exemplo demonstra como especificar o ID do ponto final do OCI Generative AI Cohere Chat Model em vez de model. Se você estiver usando o ID do ponto final do Modelo de Chat Meta Llama, especifique oci_apiformat como GENERIC.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_CHAT_OCID',
attributes => '{"provider": "oci",
"credential_name": "OCI_CRED",
"object_list": [{"owner": "ADB_USER"}],
"model": "<model_ocid>",
"oci_apiformat": "COHERE"
}');
END;
/Tópico principal: Exemplos de Uso do Select AI
Exemplo: Selecione AI com OpenAI
Este exemplo mostra como você pode usar OpenAI para gerar instruções SQL de prompts de linguagem natural.
Observação
Somente um DBA pode executar privilégios
Somente um DBA pode executar privilégios
EXECUTE e procedimento de ACL de rede.
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;
-- Grant Network ACL for OpenAI endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create Credential for AI provider
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'OPENAI_CRED',
username => 'OPENAI',
password => '<your_api_token>');
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"OPENAI"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for
transactional workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and
reliability, making it an ideal choice for modern cloud-based applications.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.O exemplo a seguir mostra como especificar um modelo diferente no perfil do AI:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"model": "gpt-3.5-turbo",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/ Tópico principal: Exemplos de Uso do Select AI
Exemplo: Selecionar IA com Cohere
Este exemplo mostra como você pode usar o Cohere para gerar instruções SQL de prompts de linguagem natural.
Observação
Somente um DBA pode executar privilégios
Somente um DBA pode executar privilégios
EXECUTE e procedimento de ACL de rede.
--Grants EXECUTE privilege to ADB_USER
--
SQL>GRANT execute on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'COHERE_CRED',
username => 'COHERE',
password => 'your_api_token');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Cohere endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.cohere.ai',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'COHERE',
attributes =>'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "sales"},
{"owner": "SH", "name": "products"},
{"owner": "SH", "name": "countries"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('COHERE');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"COHERE"
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('COHERE');
PL/SQL procedure successfully completed.O exemplo a seguir mostra como especificar um modelo e atributos personalizados diferentes no seu perfil do AI:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'COHERE',
attributes =>
'{"provider": "cohere",
"credential_name": "COHERE_CRED",
"model": "cohere.command-a-03-2025",
"object_list": [{"owner": "ADB_USER"}],
"max_tokens":512,
"stop_tokens": [";"],
"temperature": 0.5,
"comments": true
}');
END;
/Tópico principal: Exemplos de Uso do Select AI
Exemplo: Selecionar AI com o Serviço OpenAI do Azure
Os exemplos a seguir mostram como você pode ativar o acesso ao Serviço OpenAI do Azure usando sua chave de API ou usar o Controlador de Serviços OpenAI do Azure, criar um perfil de IA e gerar SQL com base em prompts de linguagem natural.
-- Create Credential for AI integration
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'AZURE_CRED',
username => 'AZUREAI',
password => 'your_api_token');
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for OpenAI endpoint
--
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '<azure_resource_name>.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=> 'AZUREAI',
attributes=> '{"provider": "azure",
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
"credential_name": "AZURE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.O exemplo a seguir mostra como especificar um modelo diferente no perfil do AI:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"provider": "azure",
"credential_name": "AZURE$PA",
"model": "gpt-3.5-turbo",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
}');
END;
/Exemplo: Selecionar IA com o Controlador de Serviços OpenAI do Azure
Conecte-se como administrador de banco de dados para fornecer acesso à autenticação do controlador de serviços do Azure e, em seguida, conceda as permissões de ACL de rede ao usuário (ADB_USER) que deseja usar Selecionar IA. Para fornecer acesso aos recursos do Azure, consulte Usar o Controlador de Serviços do Azure para acessar recursos do Azure.
Observação
Somente um usuário DBA pode executar privilégios
Somente um usuário DBA pode executar privilégios
EXECUTE e procedimento de ACL de rede.
-- Connect as ADMIN user and enable Azure service principal authentication.
BEGIN
DBMS_CLOUD_ADMIN.ENABLE_PRINCIPAL_AUTH(provider => 'AZURE',
params => JSON_OBJECT('azure_tenantid' value 'azure_tenantid'));
END;
/
-- Copy the consent url from cloud_integrations view and consents the ADB-S application.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_consent_url';
PARAM_VALUE
--------------------------------------------------------------------------------
https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize?client_id=<client_id>&response_type=code&scope=User.read
-- On the Azure OpenAI IAM console, search for the Azure application name and assign the permission to the application.
-- You can get the application name in the cloud_integrations view.
SQL> select param_value from CLOUD_INTEGRATIONS where param_name = 'azure_app_name';
PARAM_VALUE
--------------------------------------------------------------------------------
ADBS_APP_DATABASE_OCID
--
-- Grant Network ACL for Azure OpenAI endpoint
--SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'azure_resource_name.openai.azure.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name=>'AZUREAI',
attributes=>'{"provider": "azure",
"credential_name": "AZURE$PA",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"azure_resource_name": "<azure_resource_name>",
"azure_deployment_name": "<azure_deployment_name>"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
EXEC DBMS_CLOUD_AI.SET_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"AZUREAI"
--
-- Use AI
--
select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing us
ers to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
EXEC DBMS_CLOUD_AI.DROP_PROFILE('AZUREAI');
PL/SQL procedure successfully completed.Tópico principal: Exemplos de Uso do Select AI
Exemplo: Selecione AI com o Google
Este exemplo mostra como você pode usar o Google para gerar, executar e explicar SQL a partir de prompts de linguagem natural ou chat usando o Google Gemini LLM.
O exemplo a seguir demonstra como usar o Google como seu provedor de IA. O exemplo demonstra como usar sua chave de assinatura da API do Google para fornecer acesso à rede, criar um perfil de IA, usar ações Selecionar IA para gerar consultas SQL com base em prompts de linguagem natural e respostas de chat.
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL> BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GOOGLE_CRED',
username => 'GOOGLE',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Google endpoint
--
SQL>
SQL> BEGIN
DBMS_NETWORK_ACL_ADB_USER.APPEND_HOST_ACE(
host => 'generativelanguage.googleapis.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL> BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('GOOGLE');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('GOOGLE');
PL/SQL procedure successfully completed. O exemplo a seguir mostra como especificar um modelo diferente no perfil do AI:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GOOGLE',
attributes =>'{"provider": "google",
"credential_name": "GOOGLE_CRED",
"model": "gemini-1.5-pro",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/Tópico principal: Exemplos de Uso do Select AI
Exemplo: Selecionar IA com Antropical
Este exemplo mostra como você pode usar o Anthropic para gerar, executar e explicar SQL a partir de prompts de linguagem natural ou chat usando o LLM Anthropic Claude.
O exemplo a seguir demonstra como usar o Anthropic como seu provedor de IA. O exemplo demonstra como usar sua chave de assinatura de API Antrópica para fornecer acesso à rede, criar um perfil de IA e usar ações Selecionar IA para gerar consultas SQL de prompts de linguagem natural e bate-papo usando o LLM Antrópico Claude.
Consulte Atributos de Perfil para fornecer os atributos de perfil.
--Grant EXECUTE privilege to ADB_USER
SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL>BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'ANTHROPIC_CRED',
username => 'ANTHROPIC',
password => '<your api key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Anthropic endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADB_USER.APPEND_HOST_ACE(
host => 'api.anthropic.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'ANTHROPIC',
attributes =>'{"provider": "anthropic",
"credential_name": "ANTHROPIC_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
--
-- Use AI
--
SQL> select ai how many customers exist;
CUSTOMER_COUNT
--------------
55500
SQL> select ai how many customers in San Francisco are married;
MARRIED_CUSTOMERS
-----------------
18
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS married_customers_count
FROM SH.CUSTOMERS c
WHERE c.CUST_CITY = 'San Francisco'
AND c.CUST_MARITAL_STATUS = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
--------------------------------------------------------------------------------
SELECT COUNT(*) AS customer_count
FROM SH.CUSTOMERS AS c
WHERE c.CUST_STATE_PROVINCE = 'San Francisco' AND c.CUST_MARITAL_STATUS = 'Married';
Explanation:
- We use the 'SH' table alias for the 'CUSTOMERS' table for better readability.
- The query uses the 'COUNT(*)' function to count the number of rows that match the given conditions.
- The 'WHERE' clause is used to filter the results:
- 'c.CUST_STATE_PROVINCE = 'San Francisco'' filters customers who have 'San Francisco' as their state or province.
- 'c.CUST_MARITAL_STATUS = 'Married'' filters customers who have 'Married' as their marital status.
The result of this query will give you the count of customers in San Francisco who are married, using the column alias 'customer_count' for the result.
Remember to adjust the table and column names based on your actual schema if they differ from the example.
Feel free to ask if you have more questions related to SQL or database in general.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
--------------------------------------------------------------------------------
The top 3 customers in San Francisco are:
1. Hector Colven - Total amount sold: $52,025.99
2. Milburn Klemm - Total amount sold: $50,842.28
3. Gavin Xie - Total amount sold: $48,677.18
SQL> select ai chat what is Autonomous Database;
RESPONSE
--------------------------------------------------------------------------------
Autonomous Database is a cloud-based database service provided by Oracle. It is designed to automate many of the routine tasks involved in managing a database,
such as patching, tuning, and backups. Autonomous Database uses machine learning and automation to optimize performance, security, and availability, allowing
users to focus on their applications and data rather than database administration tasks. It offers both Autonomous Transaction Processing (ATP) for transactional
workloads and Autonomous Data Warehouse (ADW) for analytical workloads. Autonomous Database provides high performance, scalability, and reliability, making it
an ideal choice for modern cloud-based applications.
--
--Drop the profile
--
EXEC DBMS_CLOUD_AI.DROP_PROFILE('ANTHROPIC');
PL/SQL procedure successfully completed.
O exemplo a seguir mostra como especificar um modelo diferente no perfil do AI:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'ANTHROPIC',
attributes =>'{"provider": "anthropic",
"credential_name": "ANTHROPIC_CRED",
"model": "claude-3-opus-20240229",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}]
}');
END;
/Tópico principal: Exemplos de Uso do Select AI
Exemplo: Selecionar IA com Rosto de Abraço
Este exemplo mostra como você pode usar o Hugging Face para gerar, executar e explicar SQL de prompts de linguagem natural ou chat usando o LLM Hugging Face.
O exemplo a seguir demonstra como usar o Hugging Face como seu provedor de IA. O exemplo demonstra como usar sua chave de assinatura Hugging Face API para fornecer acesso à rede, criar um perfil de IA e usar ações Selecionar IA para gerar consultas SQL de prompts de linguagem natural e bate-papo usando o LLM Hugging Face.
--Grant EXECUTE privilege to ADB_USER
SQL>GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--
-- Create Credential for AI provider
--
SQL>BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'HF_CRED',
username => 'HF',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
--
-- Grant Network ACL for Hugging Face endpoint
--
SQL>BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api-inference.huggingface.co',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
SQL>BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'HF',
attributes =>'{"provider": "huggingface",
"credential_name": "HF_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"model" : "Qwen/Qwen2.5-72B-Instruct"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL>EXEC DBMS_CLOUD_AI.SET_PROFILE('HF');
PL/SQL procedure successfully completed.
--
-- Use AI
--SQL> select ai how many customers exist;
Customer_Count
--------------
55500
SQL> select ai how many customers in San Francisco are married;
Married_Customers
-----------------
46
SQL> select ai showsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------
SELECT COUNT("CUST_ID") AS "Married_Customers"
FROM "SH"."CUSTOMERS" "C"
WHERE "CUST_CITY" = 'San Francisco' AND "CUST_MARITAL_STATUS
" = 'Married'
SQL> select ai explainsql how many customers in San Francisco are married;
RESPONSE
------------------------------------------------------------
To answer the question "How many customers in San Francisco are married?", we need to query the "SH"."CUSTOMERS" table and filter the results based on the city and marital status.
We will use table aliases to make the query more readable and ensure that the string comparisons follow the specified rules.
Here is the Oracle SQL query:
```sql
SELECT COUNT("CUST_ID") AS "Married_Customers_in_San_Francisco"
FROM "SH"."CUSTOMERS" c
WHERE c."CUST_CITY" = 'San Francisco' -- Case insensitive comparison
AND UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married'); --
Case insensitive comparison
```
### Explanation:
1. **Table Alias**:
RESPONSE
------------------------------------------------------------
- We use the alias `c` for the "SH"."CUSTOMERS" table to make the query more readable.
2. **Column Selection**:
- We select the count of "CUST_ID" to get the number of customers who meet the criteria. We alias this count as "Married_Customers_in_San_Francisco" for clarity.
3. **WHERE Clause**:
- **City Filter**: `c."CUST_CITY" = 'San Francisco'`
- Since 'San Francisco' is not in double quotes, we use a case-insensitive comparison.
- **Marital Status Filter**: `UPPER(c."CUST_MARITAL_STATUS") = UPPER('Married')`
- Since 'Married' is not in double quotes, we use a case-insensitive comparison by applying the `UPPER` function to both sides of the comparison.
This query will return the number of customers in San Francisco who are married.
SQL> select ai narrate what are the top 3 customers in San Francisco;
RESPONSE
------------------------------------------------------------
The top 3 customers in San Francisco are:
- Hector Colven, with a customer ID of 8866.
- Milburn Klemm, with a customer ID of 1944.
- Gavin Xie, with a customer ID of 2579.
These customers have the highest total sales among all customers in San Francisco.
SQL> select ai chat what is Autonomous Database;
RESPONSE
------------------------------------------------------------
An Autonomous Database is a type of database management system that uses artificial intelligence (AI) and machine learning (ML) to automate many of the routine tasks typically performed by database administrators (DBAs). These tasks include provisioning, patching, backups, tuning, and security. The goal of an autonomous database is to reduce the need for human intervention, minimize errors, and optimize performance,thereby allowing organizations to focus on more strategic activities.
### Key Features of Autonomous Databases:
RESPONSE
------------------------------------------------------------
1. **Automated Provisioning**: The database can be set up and configured automatically, reducing the time and effort required to get it up and running.
2. **Self-Patching and Updates**: The database automatically applies security patches and updates without downtime, ensuring that the system remains secure and up-to-date.
3. **Self-Tuning**: The database continuously monitors its performance and adjusts settings to optimize query execution and resource utilization.
4. **Self-Backup and Recovery**: Automated backup and recovery processes ensure that data is protected and can be restored quickly in the event of a failure.
5. **Security**: Advanced security features, including threat detection and response, are built into the database to protect against cyber threats.
6. **Scalability**: The database can automatically scale resources up or down based on demand, ensuring optimal performance and cost efficiency.
7. **Monitoring and Diagnostics**: Real-time monitoring and diagnostics help identify and resolve issues before they impact performance.
RESPONSE
------------------------------------------------------------
### Benefits of Autonomous Databases:
- **Reduced Operational Costs**: By automating routine tasks, the need for dedicated DBAs is reduced, lowering operational costs.
- **Improved Reliability**: Automated processes reduce the risk of human error, leading to more reliable and consistent performance.
- **Enhanced Security**: Continuous monitoring and automated security measures help protect against threats.
- **Faster Time to Market**: Automated provisioning and tuning allow applications to be deployed more quickly.
RESPONSE
------------------------------------------------------------
- **Scalability and Flexibility**: The ability to scale resources automatically ensures that the database can handle varying workloads efficiently.
### Use Cases:
- **Cloud Applications**: Autonomous databases are particularly useful in cloud environments where scalability and reliability are critical.
- **Data Warehousing**: They can handle large volumes of data and complex queries, making them ideal for data warehousing and analytics.
RESPONSE
------------------------------------------------------------
- **IoT and Real-Time Data Processing**: They can process and analyze real-time data from IoT devices efficiently.
- **E-commerce**: They can handle high transaction volumes and ensure fast response times for online shopping platforms.
### Examples of Autonomous Databases:
- **Oracle Autonomous Database**: One of the first and most well-known autonomous databases, offering both transactional
and data warehousing capabilities.
- **Amazon Aurora**: A managed relational database service that includes automated scaling, patching, and backups.
- **Microsoft Azure SQL Database Managed Instance**: Provides a high level of automation and management for SQL Server databases in the cloud.
- **Google Cloud Spanner**: A globally distributed, horizontally scalable relational database that is highly available and consistent.
Autonomous databases represent a significant advancement in database technology, offering organizations a more efficient, secure, and cost-effective way to manage their data.
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('HF');
PL/SQL procedure successfully completed.Tópico principal: Exemplos de Uso do Select AI
Exemplo: Ativar Conversas no Select AI
Este exemplo ilustra como ativar conversas no Select AI.
Observação
Somente um DBA pode executar privilégios
Somente um DBA pode executar privilégios
EXECUTE e procedimento de ACL de rede.
Crie seu perfil de IA. Defina o atributo conversation como true no perfil. Essa ação inclui conteúdo de interações ou prompts anteriores, potencialmente incluindo metadados de esquema e defina seu perfil. Depois que o perfil estiver habilitado, você poderá começar a conversar com seus dados. Use a linguagem natural para fazer perguntas e acompanhar conforme necessário.
--Grants EXECUTE privilege to ADB_USER
--
SQL> grant execute on DBMS_CLOUD_AI to ADB_USER;
-- Grant Network ACL for OpenAI endpoint
--
SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
--
-- Create Credential for AI provider
--
EXEC
DBMS_CLOUD.CREATE_CREDENTIAL(
CREDENTIAL_NAME => 'OPENAI_CRED',
username => 'OPENAI',
password => '<your_api_token>');
PL/SQL procedure successfully completed.
--
-- Create AI profile
--
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OPENAI',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"object_list": [{"owner": "SH", "name": "customers"},
{"owner": "SH", "name": "countries"},
{"owner": "SH", "name": "supplementary_demographics"},
{"owner": "SH", "name": "profits"},
{"owner": "SH", "name": "promotions"},
{"owner": "SH", "name": "products"}],
"conversation": "true"
}');
END;
/
PL/SQL procedure successfully completed.
--
-- Enable AI profile in current session
--
SQL> EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI');
PL/SQL procedure successfully completed.
--
-- Get Profile in current session
--
SQL> SELECT DBMS_CLOUD_AI.get_profile() from dual;
DBMS_CLOUD_AI.GET_PROFILE()
--------------------------------------------------------------------------------
"OPENAI"
--
-- Use AI
--
what are the total number of customers;
CUSTOMER_COUNT
--------------
55500
break out count of customers by country;
RESPONSE
-----------------
COUNTRY_NAME CUSTOMER_COUNT
Italy 7780
Brazil 832
Japan 624
United Kingdom 7557
Germany 8173
United States of America 18520
France 3833
Canada 2010
Spain 2039
China 712
Singapore 597
New Zealand 244
Poland 708
Australia 831
Argentina 403
Denmark 383
South Africa 88
Saudi Arabia 75
Turkey 91
what age group is most common;
RESPONSE
--------------------------------------------------------------------------------
AGE_GROUP CUSTOMER_COUNT
65+ 28226
select ai keep the top 5 customers and their country by their purchases and include a rank in the result;
RESPONSE
--------------------------------------------------------------------------------
RANK CUSTOMER_NAME COUNTRY PURCHASES
1 Abigail Ruddy Japan 276
2 Abigail Ruddy Italy 168
3 Abigail Ruddy Japan 74
3 Abner Robbinette Germany 74
5 Abner Everett France 68
SQL> EXEC DBMS_CLOUD_AI.DROP_PROFILE('OPENAI');
PL/SQL procedure successfully completed.Tópico principal: Exemplos de Uso do Select AI
Exemplo: Configurar e Usar Selecionar AI com RAG
Este exemplo orienta você na configuração de credenciais, na configuração do acesso à rede e na criação de um índice de vetor para integrar os serviços de nuvem de armazenamento de vetores do OCI Generative AI ao OpenAI usando o Oracle Autonomous Database.
A configuração termina com a criação de um perfil de IA que use o índice vetorial para aprimorar as respostas de LLM. Por fim, este exemplo usa a ação Selecionar AI narrate, que retorna uma resposta que foi aprimorada usando informações do banco de dados vetorial especificado.
O exemplo a seguir demonstra como criar e consultar índice de vetores no Oracle 23ai.
--Grants EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Create the OpenAI credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OPENAI_CRED',
username => 'OPENAI_CRED',
password => '<your_api_key>'
);
END;
/
PL/SQL procedure successfully completed.
-- Append the OpenAI endpoint
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.openai.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'ADB_USER',
principal_type => xs_acl.ptype_db)
);
END;
/
PL/SQL procedure successfully completed.
-- Create the object store credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => '<your_username>',
password => '<OCI_profile_password>'
);
END;
/
PL/SQL procedure successfully completed.
-- Create the profile with the vector index.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'OPENAI_ORACLE',
attributes =>'{"provider": "openai",
"credential_name": "OPENAI_CRED",
"vector_index_name": "MY_INDEX",
"temperature": 0.2,
"max_tokens": 4096,
"model": "gpt-3.5-turbo-1106"
}');
END;
/
PL/SQL procedure successfully completed.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
-- create a vector index with the vector store name, object store location and
-- object store credential
BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'MY_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "OCI_CRED",
"profile_name": "OPENAI_ORACLE",
"vector_dimension": 1536,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
PL/SQL procedure successfully completed.
-- After the vector index is populated, we can now query the index.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OPENAI_ORACLE');
PL/SQL procedure successfully completed.
-- Select AI answers the question with the knowledge available in the vector database.
set pages 1000
set linesize 150
SELECT AI narrate how can I deploy an oracle machine learning model;
RESPONSE
To deploy an Oracle Machine Learning model, you would first build your model within the Oracle database. Once your in-database models are built, they become immediately available for use, for instance, through a SQL query using the prediction operators built into the SQL language.
The model scoring, like model building, occurs directly in the database, eliminating the need for a separate engine or environment within which the model and corresponding algorithm code operate. You can also use models from a different schema (user account) if the appropriate permissions are in place.
Sources:
- Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt (https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder/Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt)
- Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt (https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder/Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt)
Tópico principal: Exemplos de Uso do Select AI
Exemplo: Selecionar AI com Modelos de Transformador no Banco de Dados
Este exemplo demonstra como você pode importar um modelo de transformador pré-treinado que é armazenado no armazenamento de objetos da Oracle para sua instância do Oracle Database 23ai e, em seguida, usar o modelo importado no banco de dados no perfil Selecionar AI para gerar incorporações de vetor para blocos de documentos e prompts do usuário.
Para usar modelos de transformadores no banco de dados em seu perfil Select AI, certifique-se de ter:
-
seu modelo pré-treinado importado na instância do Oracle Database 23ai.
-
opcionalmente, acesso ao armazenamento de objetos Oracle.
Importar um Modelo de Transformador Pré-treinado do Oracle Object Storage para o Oracle Database 23ai
Revise as etapas em Importar Modelos Pré-treinados no Formato ONNX para Geração de Vetor no Banco de Dados e o blog Modelo de Geração de Incorporação Predefinido para o Oracle Database 23ai para importar um modelo de transformador pré-treinado para seu banco de dados.
O exemplo a seguir mostra como importar um modelo de transformador pré-mantido do armazenamento de objetos da Oracle para seu banco de dados e, em seguida, exibir o modelo importado.
- Create a Directory object, or use an existing directory object
CREATE OR REPLACE DIRECTORY ONNX_DIR AS 'onnx_model';
-- Object storage bucket
VAR location_uri VARCHAR2(4000);
EXEC :location_uri := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
-- Model file name
VAR file_name VARCHAR2(512);
EXEC :file_name := 'all_MiniLM_L12_v2.onnx';
-- Download ONNX model from object storage into the directory object
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'ONNX_DIR',
object_uri => :location_uri || :file_name);
END;
/
-- Load the ONNX model into the database
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'ONNX_DIR',
file_name => :file_name,
model_name => 'MY_ONNX_MODEL');
END;
/
-- Verify
SELECT model_name, algorithm, mining_function
FROM user_mining_models
WHERE model_name='MY_ONNX_MODEL';Usar Modelos de Transformador no Banco de Dados em Selecionar Perfis AI
Esses exemplos ilustram como usar modelos de transformador no banco de dados em um perfil Select AI. Um perfil é configurado apenas para gerar incorporações de vetores, enquanto o outro suporta ações Select AI e criação de índice de vetores.
Verifique Exemplos de Privilégios para Executar o Select AI para concluir os pré-requisitos.
Veja a seguir um exemplo para gerar incorporações de vetores apenas:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'EMBEDDING_PROFILE',
attributes => '{"provider" : "database",
"embedding_model": "MY_ONNX_MODEL"}'
);
END;
/Veja a seguir um exemplo de ações gerais do Select AI e geração de índice vetorial em que você pode especificar um provedor de IA suportado. Este exemplo usa o perfil e as credenciais do OCI Gen AI. Consulte Selecionar seu Provedor de IA e LLMs para obter uma lista de provedores suportados. No entanto, se você quiser usar o modelo de transformador no banco de dados para gerar incorporações de vetor, use "database: <MY_ONNX_MODEL>" no atributo embedding_model:
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"model": "meta.llama-3.3-70b-instruct",
"credential_name": "GENAI_CRED",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: MY_ONNX_MODEL"}'
);
END;
/Usar Selecionar AI com um Modelo de Transformador no Banco de Dados de Outro Esquema
Este exemplo demonstra como usar Selecionar IA com um modelo de transformador no banco de dados se outro proprietário do esquema possuir o modelo. Especifique schema_name.object_name como o nome totalmente qualificado do modelo no atributo embedding_model. Se o usuário atual for o proprietário do esquema ou for proprietário do modelo, você poderá omitir o nome do esquema.
Certifique-se de ter os seguintes privilégios se um proprietário de esquema diferente possuir o modelo:
- Privilégio de sistema
CREATE ANY MINING MODEL - Privilégio de sistema
SELECT ANY MINING MODEL - Privilégio de objeto
SELECT MINING MODELno modelo específico
Para conceder um privilégio de sistema, você deve ter recebido o privilégio de sistema com o ADMIN OPTION ou ter recebido o privilégio de sistema GRANT ANY PRIVILEGE.
Consulte Privilégios de Sistema do Oracle Machine Learning for SQL para revisar os privilégios.
As instruções a seguir permitem que ADB_USER1 pontue dados e exiba detalhes do modelo em qualquer esquema, desde que o acesso SELECT tenha sido concedido aos dados. No entanto, ADB_USER1 só pode criar modelos no esquema ADB_USER1.
GRANT CREATE MINING MODEL TO ADB_USER1;
GRANT SELECT ANY MINING MODEL TO ADB_USER1;
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"credential_name": "GENAI_CRED",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: ADB_USER1.MY_ONNX_MODEL"}'
);
END;
/O exemplo a seguir mostra como você pode especificar o nome do objeto do modelo com distinção entre maiúsculas e minúsculas:
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"credential_name": "GENAI_CRED",
"model": "meta.llama-3.3-70b-instruct",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: \"adb_user1\".\"my_model\""}'
);
END;
/Exemplos de ponta a ponta com diferentes provedores de IA
Esses exemplos demonstram etapas de ponta a ponta para usar o modelo de transformador no banco de dados com o Select AI RAG. Um perfil usa database como o provider criado exclusivamente para gerar vetores de incorporação, enquanto o outro usa oci como o provider criado para ações Selecionar IA, bem como índice vetorial.
Revise Executar Pré-requisitos para Selecionar AI para fornecer os privilégios necessários.
--Grant create any directory privilege to the user
GRANT CREATE ANY DIRECTORY to ADB_USER;
- Create a Directory object, or use an existing directory object
CREATE OR REPLACE DIRECTORY ONNX_DIR AS 'onnx_model';
-- Object storage bucket
VAR location_uri VARCHAR2(4000);
EXEC :location_uri := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
-- Model file name
VAR file_name VARCHAR2(512);
EXEC :file_name := 'all_MiniLM_L12_v2.onnx';
-- Download ONNX model from object storage into the directory object
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'ONNX_DIR',
object_uri => :location_uri || :file_name);
END;
/
-- Load the ONNX model into the database
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'ONNX_DIR',
file_name => :file_name,
model_name => 'MY_ONNX_MODEL');
END;
/
-- Verify
SELECT model_name, algorithm, mining_function
FROM user_mining_models
WHERE model_name='MY_ONNX_MODEL';
--Administrator grants EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Administrator grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Create the object store credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => '<your_username>',
password => '<OCI_profile_password>'
);
END;
/
PL/SQL procedure successfully completed.
-- Create the profile with Oracle Database.
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'EMBEDDING_PROFILE',
attributes =>'{"provider": "database",
"embedding_model": "MY_ONNX_MODEL"
}');
END;
/
PL/SQL procedure successfully completed.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('EMBEDDING_PROFILE');
PL/SQL procedure successfully completed.
Este exemplo usa oci como o provider.
--Grant create any directory privilege to the user
GRANT CREATE ANY DIRECTORY to ADB_USER;
- Create a Directory object, or use an existing directory object
CREATE OR REPLACE DIRECTORY ONNX_DIR AS 'onnx_model';
-- Object storage bucket
VAR location_uri VARCHAR2(4000);
EXEC :location_uri := 'https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/eLddQappgBJ7jNi6Guz9m9LOtYe2u8LWY19GfgU8flFK4N9YgP4kTlrE9Px3pE12/n/adwc4pm/b/OML-Resources/o/';
-- Model file name
VAR file_name VARCHAR2(512);
EXEC :file_name := 'all_MiniLM_L12_v2.onnx';
-- Download ONNX model from object storage into the directory object
BEGIN
DBMS_CLOUD.GET_OBJECT(
credential_name => NULL,
directory_name => 'ONNX_DIR',
object_uri => :location_uri || :file_name);
END;
/
-- Load the ONNX model into the database
BEGIN
DBMS_VECTOR.LOAD_ONNX_MODEL(
directory => 'ONNX_DIR',
file_name => :file_name,
model_name => 'MY_ONNX_MODEL');
END;
/
-- Verify
SELECT model_name, algorithm, mining_function
FROM user_mining_models
WHERE model_name='MY_ONNX_MODEL';
–-Administrator Grants EXECUTE privilege to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_AI to ADB_USER;
--Administrator Grants EXECUTE privilege DBMS_CLOUD_PIPELINE to ADB_USER
GRANT EXECUTE on DBMS_CLOUD_PIPELINE to ADB_USER;
-- Create the object store credential
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'OCI_CRED',
username => '<your_username>',
password => '<OCI_profile_password>'
);
END;
/
--Create GenAI credentials
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1..aaaa...',
tenancy_ocid => 'ocid1.tenancy.oc1..aaaa...',
private_key => '<your_api_key>',
fingerprint => '<your_fingerprint>'
);
END;
/
--Create OCI AI profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name => 'OCI_GENAI',
attributes => '{"provider": "oci",
"model": "meta.llama-3.3-70b-instruct",
"credential_name": "GENAI_CRED",
"vector_index_name": "MY_INDEX",
"embedding_model": "database: MY_ONNX_MODEL"}'
);
END;
/
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENAI');
PL/SQL procedure successfully completed.
-- create a vector index with the vector store name, object store location and
-- object store credential
BEGIN
DBMS_CLOUD_AI.CREATE_VECTOR_INDEX(
index_name => 'MY_INDEX',
attributes => '{"vector_db_provider": "oracle",
"location": "https://swiftobjectstorage.us-phoenix-1.oraclecloud.com/v1/my_namespace/my_bucket/my_data_folder",
"object_storage_credential_name": "OCI_CRED",
"profile_name": "OCI_GENAI",
"vector_dimension": 384,
"vector_distance_metric": "cosine",
"chunk_overlap":128,
"chunk_size":1024
}');
END;
/
PL/SQL procedure successfully completed.
-- Set profile
EXEC DBMS_CLOUD_AI.SET_PROFILE('OCI_GENAI');
PL/SQL procedure successfully completed.
-- Select AI answers the question with the knowledge available in the vector database.
set pages 1000
set linesize 150
SELECT AI narrate how can I deploy an oracle machine learning model;
RESPONSE
To deploy an Oracle Machine Learning model, you would first build your model within the Oracle database. Once your in-database models are
built, they become immediately available for use, for instance, through a SQL query using the prediction operators built into the SQL
language.
The model scoring, like model building, occurs directly in the database, eliminating the need for a separate engine or environment within
which the model and corresponding algorithm code operate. You can also use models from a different schema (user account) if the appropriate
permissions are in place.
Sources:
- Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt (https://objectstorage.../v1/my_namespace/my_bucket/
my_data_folder/Manage-your-models-with-Oracle-Machine-Learning-on-Autonomous-Database.txt)
- Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-Database-Machine-Learning-and-APEX.txt
(https://objectstorage.../v1/my_namespace/my_bucket/my_data_folder/Develop-and-deploy-machine-learning-models-using-Oracle-Autonomous-
Database-Machine-Learning-and-APEX.txt)Tópico principal: Exemplos de Uso do Select AI
Exemplo: Melhorar a Geração de Consulta SQL
Esses exemplos demonstram como comentários, anotações, constraints de integridade referencial e chave estrangeira em tabelas e colunas de banco de dados podem melhorar a geração de consultas SQL a partir de prompts de linguagem natural.
Exemplo: Melhorar a Geração de SQL com Comentários de Tabela e Coluna
Se você tiver comentários de tabela e coluna em suas tabelas de banco de dados, ative o parâmetro "comments":"true" na função DBMS_CLOUD_AI.CREATE_PROFILE para recuperar comentários de nível de tabela e de coluna. Os comentários são adicionados aos metadados do LLM para uma melhor geração de SQL.
-- 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 DEVICEExemplo: Melhorar a Geração de Consulta SQL com Anotações de Tabela e Coluna
Este exemplo demonstra a integração de anotações no Select AI, aplicável no Oracle Database 23ai. As anotações são adicionadas aos metadados que são enviados ao LLM.
Se você tiver uma tabela com anotações no seu esquema, ative "annotations":"true" na função DBMS_CLOUD_AI.CREATE_PROFILE para instruir o Select AI a adicionar anotações aos metadados.
--
-- 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.Exemplo: Melhorar a Geração de Consulta SQL com Restrições de Chave Estrangeira e Chave Referencial
Este exemplo demonstra a capacidade do LLM de gerar condições precisas do JOIN recuperando as restrições de chave estrangeira e chave referencial nos metadados do LLM. As restrições de chave estrangeira e chave referencial fornecem dados de relacionamento estruturado entre as tabelas para o LLM.
Ative "constraints":"true" na função DBMS_CLOUD_AI.CREATE_PROFILE para Selecionar AI a fim de recuperar a chave estrangeira e a chave referencial.
--
-- 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.Exemplo: Detectar Automaticamente Metadados de Tabela Relevantes
Estes exemplos mostram como o Select AI detecta automaticamente tabelas relevantes e envia metadados somente para as tabelas específicas relevantes para a consulta no Oracle Database 23ai. Para ativar esse recurso, defina object_list_mode como automated. Isso cria automaticamente um índice de vetor chamado <profile_name>_OBJECT_LIST_VECINDEX. O índice do vetor é inicializado com atributos e valores padrão, como refresh_rate, similarity_threshold e match_limit. Você pode modificar alguns dos atributos por meio de DBMS_CLOUD_AI.UPDATE_VECTOR_INDEX. Consulte Procedimento UPDATE_VECTOR_INDEX para obter mais informações.
Um perfil é configurado para usar object_list para especificar o esquema ou os objetos no esquema, enquanto o outro não especifica object_list. No entanto, é esperada a mesma construção SQL.
Revise Executar Pré-requisitos para Selecionar AI para fornecer acesso ao pacote DBMS_CLOUD_AI e fornecer acesso de rede ao provedor de AI.
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')O exemplo a seguir compara o mesmo cenário sem usar object_list. Quando você não especifica object_list, a opção Selecionar AI escolhe automaticamente todos os objetos disponíveis para o esquema atual.
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')Tópico principal: Exemplos de Uso do Select AI
Exemplo: Gerar Dados Sintéticos
Este exemplo explora como você pode gerar dados sintéticos imitando as características e a distribuição de dados reais.
O exemplo a seguir mostra como criar algumas tabelas no seu esquema, usar o OCI Generative AI como seu provedor de IA para criar um perfil de IA, sintetizar dados nessas tabelas usando a função DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA e consultar ou gerar respostas para prompts de linguagem natural com Selecionar IA.
--Create tables or use cloned tables
CREATE TABLE ADB_USER.Director (
director_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE ADB_USER.Movie (
movie_id INT PRIMARY KEY,
title VARCHAR(100),
release_date DATE,
genre VARCHAR(50),
director_id INT,
FOREIGN KEY (director_id) REFERENCES ADB_USER.Director(director_id)
);
CREATE TABLE ADB_USER.Actor (
actor_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE ADB_USER.Movie_Actor (
movie_id INT,
actor_id INT,
PRIMARY KEY (movie_id, actor_id),
FOREIGN KEY (movie_id) REFERENCES ADB_USER.Movie(movie_id),
FOREIGN KEY (actor_id) REFERENCES ADB_USER.Actor(actor_id)
);
-- Create the GenAI credential
BEGIN
DBMS_CLOUD.create_credential(
credential_name => 'GENAI_CRED',
user_ocid => 'ocid1.user.oc1....',
tenancy_ocid => 'ocid1.tenancy.oc1....',
private_key => 'vZ6cO...',
fingerprint => '86:7d:...'
);
END;
/
-- Create a profile
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"object_list": [{"owner": "ADB_USER",
"oci_compartment_id": "ocid1.compartment.oc1...."}]
}');
END;
/
EXEC DBMS_CLOUD_AI.set_profile('GENAI');
-- Run the API for single table
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Director',
owner_name => 'ADB_USER',
record_count => 5
);
END;
/
PL/SQL procedure successfully completed.
-- Query the table to see results
SQL> SELECT * FROM ADB_USER.Director;
DIRECTOR_ID NAME
----------- ----------------------------------------------------------------------------------------------------
1 John Smith
2 Emily Chen
3 Michael Brown
4 Sarah Taylor
5 David Lee
-- Or ask select ai to show the results
SQL> select ai how many directors are there;
NUMBER_OF_DIRECTORS
-------------------
5
Exemplo: Gerar Dados Sintéticos para Várias Tabelas
Depois de criar e definir seu perfil de provedor de IA, use o DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA para gerar dados para várias tabelas. Você pode consultar ou usar Selecionar IA para responder aos prompts de linguagem natural.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_list => '[{"owner": "ADB_USER", "name": "Director","record_count":5},
{"owner": "ADB_USER", "name": "Movie_Actor","record_count":5},
{"owner": "ADB_USER", "name": "Actor","record_count":10},
{"owner": "ADB_USER", "name": "Movie","record_count":5,"user_prompt":"all movies released in 2009"}]'
);
END;
/
PL/SQL procedure successfully completed.
-- Query the table to see results
SQL> select * from ADB_USER.Movie;
MOVIE_ID TITLE RELEASE_D GENRE DIRECTOR_ID
---------- -------------------------------------------------------- --------- --------------------------------------------------------------- -----------
1 The Dark Knight 15-JUL-09 Action 8
2 Inglourious Basterds 21-AUG-09 War 3
3 Up in the Air 04-SEP-09 Drama 6
4 The Hangover 05-JUN-09 Comedy 1
5 District 9 14-AUG-09 Science Fiction 10
-- Or ask select ai to show the results
SQL> select ai how many actors are there;
Number of Actors
----------------
10Exemplo: Orientar a Geração de Dados Sintéticos com Linhas de Amostra
Para orientar o serviço AI na geração de dados sintéticos, você pode selecionar aleatoriamente os registros existentes de uma tabela. Por exemplo, adicionando {"sample_rows": 5} ao argumento params, você pode enviar 5 linhas de amostra de uma tabela para o provedor AI. Este exemplo gera 10 linhas adicionais com base nas linhas de amostra da tabela Transactions.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Transactions',
owner_name => 'ADB_USER',
record_count => 10,
params => '{"sample_rows":5}'
);
END;
/Exemplo: Personalizar Geração de Dados Sintéticos com Prompts do Usuário
O argumento user_prompt permite especificar regras ou requisitos adicionais para geração de dados. Isso pode ser aplicado a uma única tabela ou como parte do argumento object_list para várias tabelas. Por exemplo, nas chamadas a seguir para DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA, o prompt instrui a IA a gerar dados sintéticos em filmes lançados em 2009.
-- Definition for the Movie table CREATE TABLE Movie
CREATE TABLE Movie (
movie_id INT PRIMARY KEY,
title VARCHAR(100),
release_date DATE,
genre VARCHAR(50),
director_id INT,
FOREIGN KEY (director_id) REFERENCES Director(director_id)
);
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Movie',
owner_name => 'ADB_USER',
record_count => 10,
user_prompt => 'all movies are released in 2009',
params => '{"sample_rows":5}'
);
END;
/
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_list => '[{"owner": "ADB_USER", "name": "Director","record_count":5},
{"owner": "ADB_USER", "name": "Movie_Actor","record_count":5},
{"owner": "ADB_USER", "name": "Actor","record_count":10},
{"owner": "ADB_USER", "name": "Movie","record_count":5,"user_prompt":"all movies are released in 2009"}]'
);
END;
/Exemplo: Melhorar a Qualidade de Dados Sintéticos Usando Estatísticas de Tabela
Se uma tabela tiver estatísticas de coluna ou for clonada de um banco de dados que inclua metadados, o Select AI poderá usar essas estatísticas para gerar dados muito semelhantes ou consistentes com os dados originais.
Para colunas NUMBER, os valores alto e baixo das estatísticas orientam o intervalo de valores. Por exemplo, se a coluna SALARY na tabela EMPLOYEES original variar de 1000 a 10000, os dados sintéticos dessa coluna também estarão dentro desse intervalo.
Para colunas com valores distintos, como uma coluna STATE com os valores CA, WA e TX, os dados sintéticos usarão esses valores específicos. Você pode gerenciar esse recurso usando o parâmetro {"table_statistics": true/false}. Por padrão, as estatísticas da tabela são ativadas.
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'Movie',
owner_name => 'ADB_USER',
record_count => 10,
user_prompt => 'all movies released in 2009',
params => '{"sample_rows":5,"table_statistics":true}'
);
END;
/Exemplo: Usar Comentários da Coluna para Orientar a Geração de Dados
Se existirem comentários de coluna, a opção Selecionar AI os incluirá automaticamente para fornecer informações adicionais para o LLM durante a geração de dados. Por exemplo, um comentário na coluna Status em uma tabela de Transações pode listar valores permitidos, como successful, failed, pending, canceled e need manual check. Você também pode adicionar comentários para explicar melhor a coluna, dando aos serviços de IA instruções ou dicas mais precisas para gerar dados precisos. Por padrão, os comentários estão desativados. Consulte Parâmetros Opcionais para obter mais detalhes.
-- Use comment on column
COMMENT ON COLUMN Transaction.status IS 'the value for state should either be ''successful'', ''failed'', ''pending'' or ''canceled''';
/
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'employees',
owner_name => 'ADB_USER',
record_count => 10
params => '{"comments":true}'
);
END;
/Exemplo: Definir Valores Exclusivos na Geração de Dados Sintéticos
Ao gerar grandes quantidades de dados sintéticos com LLMs, é provável que ocorram valores duplicados. Para evitar isso, configure uma restrição exclusiva na coluna relevante. Isso garante que Select AI ignore linhas com valores duplicados na resposta LLM. Além disso, para restringir os valores de determinadas colunas, você pode usar user_prompt ou adicionar comentários para especificar os valores permitidos, como limitar uma coluna STATE a CA, WA e TX.
-- Use 'user_prompt'
BEGIN
DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA(
profile_name => 'GENAI',
object_name => 'employees',
owner_name => 'ADB_USER',
user_prompt => 'the value for state should either be CA, WA, or TX',
record_count => 10
);
END;
/
-- Use comment on column
COMMENT ON COLUMN EMPLOYEES.state IS 'the value for state should either be CA, WA, or TX'
/Exemplo: Melhorar a Geração de Dados Sintéticos por Processamento Paralelo
Para reduzir o tempo de execução, o Select AI divide as tarefas de geração de dados sintéticos em partes menores para tabelas sem chaves primárias ou com chaves primárias numéricas. Essas tarefas são executadas em paralelo, interagindo com o provedor de IA para gerar dados com mais eficiência. O Grau de Paralelismo (DOP) no banco de dados, influenciado pelo nível de serviço do Autonomous Database e pelas definições de ECPU ou OCPU, determina o número de registros que cada bloco processa. A execução de tarefas em paralelo geralmente melhora o desempenho, especialmente ao gerar grandes volumes de dados em muitas tabelas. Para gerenciar o processamento paralelo da geração de dados sintéticos, defina priority como um parâmetro opcional. Consulte Parâmetros Opcionais.
Tópico principal: Exemplos de Uso do Select AI
Exemplo: Ativar ou Desativar o Acesso a Dados
Este exemplo ilustra como os administradores podem controlar o acesso aos dados e impedir que o Select AI envie tabelas de esquema reais para o LLM.
Desativando o Acesso a Dados
Para restringir o acesso a tabelas de esquema, faça log-in como administrador e execute o procedimento a seguir.
EXEC DBMS_CLOUD_AI.DISABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.
Desativar limites de acesso a dados Selecione a ação narrate do AI e a Geração de Dados Sintéticos. A ação narrate e a geração de dados sintéticos geram um erro.
Faça log-in como usuário do banco de dados, crie e configure seu perfil do AI. Verifique Executar Pré-requisitos para Selecionar AI para configurar seu perfil do AI.
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: 6O exemplo a seguir mostra os erros que são acionados quando você tenta gerar dados sintéticos.
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 2Ativando o Acesso a Dados
O exemplo a seguir mostra como ativar o acesso a dados. Efetue log-in como administrador e execute o seguinte procedimento:
EXEC DBMS_CLOUD_AI.ENABLE_DATA_ACCESS;
PL/SQL procedure successfully completed.Faça log-in como usuário do banco de dados, crie e configure seu perfil do AI. Verifique Executar Pré-requisitos para Selecionar AI para configurar seu perfil do AI. Execute a ação narrate e gere dados sintéticos separadamente.
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"
O exemplo a seguir mostra a geração bem-sucedida de dados sintéticos após ativar o acesso aos dados.
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.Tópico principal: Exemplos de Uso do Select AI
Exemplo: Restringir o Acesso à Tabela no Perfil AI
Este exemplo demonstra como restringir o acesso à tabela e instruir o LLM a usar somente as tabelas especificadas no object_list do perfil do AI.
Defina enforce_object_list como true para restringir o acesso à tabela ao LLM.
Como usuário do banco de dados, crie e configure seu perfil do AI. Verifique Executar Pré-requisitos para Selecionar AI para configurar seu perfil do AI.
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
--A definição de enforce_object_list como false instrui o LLM a usar outras tabelas e views com base em seu conhecimento anterior.
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
Tópico principal: Exemplos de Uso do Select AI
Exemplo: Especificar Diferenciação de Maiúsculas/Minúsculas para Colunas
Este exemplo mostra como você pode definir a distinção entre maiúsculas e minúsculas para colunas no perfil AI.
Defina case_sensitive_values como false para recuperar consultas que não fazem distinção entre maiúsculas e minúsculas.
Como usuário do banco de dados, crie e configure seu perfil do AI. Verifique Executar Pré-requisitos para Selecionar AI para configurar seu perfil do AI.
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')Você pode especificar uma consulta que faça distinção entre maiúsculas e minúsculas usando aspas duplas, mesmo que case_sensitive_values esteja definido como 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'
Tópico principal: Exemplos de Uso do Select AI