10.6 Generate and Transform Content
Provides examples of how to use Select AI to generate and transform content.
Topics:
- Example: Generate Synthetic Data
This example explores how you can generate synthetic data mimicking the characteristics and distribution of real data. - Example: Select AI Summarize
These examples show how to use thesummarizeaction andDBMS_CLOUD_AI.SUMMARIZEfunction. Also, customize the summary generation for your content using the function. - Example: Select AI Translate
These examples demonstrate how you can use thetranslatecapability.
Parent topic: Examples Using Select AI
10.6.1 Example: Generate Synthetic Data
The following example shows how to create a few tables in your schema,
use OCI Generative AI as your AI provider to create an AI profile, synthesize data
into those tables using the DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA
function, and query or generate responses to natural language prompts with Select
AI.
--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
After you create and set your AI provider
profile, use the DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA to generate
data for multiple tables. You can query or use Select AI to respond to the natural
language prompts.
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
----------------
10When you start generating large synthetic data sets, the
system splits the task into smaller subtasks and runs them in parallel. If you
interrupt the session (for example, using Ctrl + C),
the process may continue in the background because the subtasks do not end
automatically.
If you want to end a running task, take the following steps:
-- Find the operation ID of the running Synthetic Data Generation (SDG) process
SELECT * FROM user_load_operations WHERE type = 'SYNTHETIC_DATA';
-- Delete a specific SDG operation
EXEC dbms_cloud.delete_operation(<operation_id>);
-- Delete all SDG operations
EXEC dbms_cloud.delete_all_operations('SYNTHETIC_DATA');
If the commands above do not stop the background processes, end the session manually:
SELECT sid, serial# FROM v$session WHERE audsid = userenv('sessionid');
ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;
To guide AI service in generating
synthetic data, you can randomly select existing records from a table. For instance,
by adding {"sample_rows": 5} to the params
argument, you can send 5 sample rows from a table to the AI provider. This example
generates 10 additional rows based on the sample rows from the
Transactions
table.
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;
/The user_prompt
argument enables you to specify additional rules or requirements for data
generation. This can be applied to a single table or as part of the
object_list argument for multiple tables. For example, in the
following calls to DBMS_CLOUD_AI.GENERATE_SYNTHETIC_DATA, the prompt
instructs the AI to generate synthetic data on movies released in
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;
/If a table has column statistics or is cloned from a database that includes metadata, Select AI can use these statistics to generate data that closely resembles or is consistent with the original data.
For NUMBER columns, the high and
low values from the statistics guide the value range. For instance, if the
SALARY column in the original EMPLOYEES table
ranges from 1000 to 10000, the synthetic data for this column will also fall within
this range.
For columns with distinct values, such as a
STATE column with values CA,
WA, and TX, the
synthetic data will use these specific values. You can manage this feature using the
{"table_statistics": true/false} parameter. By default, the
table statistics are
enabled.
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;
/If
column comments exist, Select AI automatically includes them to provide additional
information for the LLM during data generation. For example, a comment on the
Status column in a Transaction table might list allowed values
such as successful, failed, pending, canceled, and need manual check. You
can also add comments to further explain the column, giving AI services more precise
instructions or hints for generating accurate data. By default, comments are
disabled. See Optional Parameters for more
details.
-- 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;
/When generating large amounts of
synthetic data with LLMs, duplicate values are likely to occur. To prevent this, set
up a unique constraint on the relevant column. This ensures that Select AI ignores
rows with duplicate values in the LLM response. Additionally, to restrict values for
certain columns, you can use the user_prompt or add comments to
specify the allowed values, such as limiting a STATE column to
CA, WA, and
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'
/To reduce runtime, Select AI splits synthetic
data generation tasks into smaller chunks for tables without primary keys or with
numeric primary keys. These tasks run in parallel, interacting with the AI provider
to generate data more efficiently. The Degree of Parallelism (DOP) in your database,
influenced by your Autonomous AI
Database service level and ECPU or OCPU settings, determines the number of
records each chunk processes. Running tasks in parallel generally improves
performance, especially when generating large amounts of data across many tables. To
manage the parallel processing of synthetic data generation, set
priority as an optional parameter. See Optional Parameters.
Parent topic: Generate and Transform Content
10.6.2 Example: Select AI Summarize
These examples show how to use
the summarize action and DBMS_CLOUD_AI.SUMMARIZE function. Also, customize the summary
generation for your content using the function.
Example: Use Summarize Action on SQL Command Line
SUMMARIZE as a Select AI action.
Use SELECT AI SUMMARIZE <TEXT> in the SQL command line to generate a
summary of input text.
SELECT AI SUMMARIZE
Like countless other people around the globe, I stream music, and like more
than six hundred million of them I mainly use Spotify. Streaming currently
accounts for about eighty per cent of the American recording industry’s
revenue, and in recent years Spotify’s health is often consulted as a measure
for the health of the music business over all. Last spring, the International
Federation of the Phonographic Industry reported global revenues of $28.6
billion, making for the ninth straight year of growth. All of this was
unimaginable in the two-thousands, when the major record labels appeared
poorly equipped to deal with piracy and the so-called death of physical media.
On the consumer side, the story looks even rosier. Adjusted for inflation, a
... (skipped 1000 rows in the middle)
Pelly writes of some artists, in search of viral fame, who surreptitiously use
social media to effectively beta test melodies and motifs, basically putting
together songs via crowdsourcing. Artists have always fretted about the
pressure to conform, but the data-driven, music-as-content era feels
different. “You are a Spotify employee at that point,” Daniel Lopatin, who
makes abstract electronic music as Oneohtrix Point Never, told Pelly. “If your
art practice is so ingrained in the brutal reality that Spotify has outlined
for all of us, then what is the music that you’re not making? What does the
music you’re not making sound like?” Listeners might wonder something similar.
What does the music we’re not hearing sound like?;
RESPONSE
------------------------------------------------------------------------------
The music streaming industry, led by Spotify, has revolutionized the way
people consume music, with streaming accounting for 80% of the American
recording industry's revenue. However, this shift has also complicated the
lives of artists, who struggle to survive in a hyper-abundant present where
music is often valued for its convenience rather than its artistic merit.
Spotify's algorithms prioritize popularity and profitability over artistic
diversity, leading to a homogenization of music and a devaluation of the
labor that goes into creating it. Meanwhile, the company's executives reap
enormous profits, with CEO Daniel Ek's net worth rivaling that of the
wealthiest musicians. As music critic Liz Pelly argues, the streaming economy
raises important questions about autonomy, creativity, and the value of art
in a world where everything is readily available and easily accessible.Tip:
') is treated as a
string delimiter. If your text contains single quotes,
either escape the quote by doubling it
(' to ''), or enclose the text using the
q'[]' quoting mechanism. If your text contains empty double quotes
(""), enclose the text using q'[]' mechanism. For
example:SELECT AI SUMMARIZE q'[this's a text]';Example: Use DBMS_CLOUD_AI.SUMMARIZE Procedure to Generate a Summary
These examples demonstrate generating a summary by using different parameters
from the DBMS_CLOUD_AI.SUMMARIZE
procedure.
location_uri parameter
and your cloud account credentials as credential_name using the DBMS_CLOUD_AI.SUMMARIZESELECT DBMS_CLOUD_AI.SUMMARIZE(
location_uri => 'https://objectstorage.ca-toronto-1.oraclecloud.com/n/' ||
'namespace-string/b/bucketname/o/data_folder/' ||
'summary/test_4000_words.txt',
credential_name => 'STORE_CRED',
profile_name => 'GENAI')
from DUAL;content parameter to call the
DBMS_CLOUD.GET_OBJECT procedure.
SELECT DBMS_CLOUD_AI.SUMMARIZE(
content => TO_CLOB(
DBMS_CLOUD.GET_OBJECT(
credential_name => 'STORE_CRED',
location_uri => 'https://objectstorage.ca-toronto-1.oraclecloud.com/n/' ||
'namespace-string/b/bucketname/o/data_folder/' ||
'summary/test_4000_words.txt')),
profile_name => 'GENAI'>)
from DUAL;user_prompt: The summary should start with 'The summary of the article is: 'min_words: 50max_words: 100
SELECT DBMS_CLOUD_AI.SUMMARIZE(
content => TO_CLOB(
DBMS_CLOUD.GET_OBJECT(
credential_name =>'STORE_CRED',
location_uri =>'https://objectstorage.ca-toronto-1.oraclecloud.com/n/' ||
'namespace-string/b/bucketname/o/data_folder/' ||
'summary/test_4000_words.txt')),
profile_name => 'GENAI',
user_prompt => 'The summary should start with ''The summary of ' ||
'the article is: ''',
params => '{"min_words":50,"max_words":100}')
As response FROM dual;
RESPONSE
--------------------------------------------------------------------------------
The summary of the article is: The music streaming industry, led by Spotify, has
revolutionized the way people consume music, with streaming accounting for abou
t eighty per cent of the American recording industry's revenue. However, this sh
ift has also raised concerns about the impact on artists, with many struggling t
o make a living due to low royalty rates and the dominance of playlists. The art
icle explores the history of music streaming, from the early days of Napster to
the current landscape, and how it has changed the way people listen to music. It
also delves into the issues of autonomy and creativity in the music industry, w
ith some artists feeling pressured to conform to certain styles or formulas to s
ucceed on platforms like Spotify. The article cites examples of artists who have
spoken out against the streaming economy, including Taylor Swift and Neil Young
, and discusses the rise of alternative platforms like Bandcamp and Nina. Ultima
tely, the article suggests that the streaming economy has created a perverse vis
ion for art, where music is valued for its ability to be ignored rather than app
reciated, and that this has significant implications for the future of music and
creativity. With the rise of AI-generated music and the increasing importance o
f data-driven decision making in the music industry, the article asks what the m
usic we're not hearing sounds like, and what the consequences of this shift will
be for artists and listeners alike. The article concludes by highlighting the n
eed for a more nuanced understanding of the music industry and the impact of str
eaming on artists and listeners, and for alternative models that prioritize crea
tivity and autonomy over profit and convenience.The following example demonstrates generating a summary of a 12000+ word text by specifying the following parameters:
user_prompt: The summary should start with 'The summary of the article is: 'max_words: 100summary_style: list
SELECT DBMS_CLOUD_AI.SUMMARIZE(
location_uri => 'https://objectstorage.ca-toronto-1.' ||
'oraclecloud.com/n/namespace-string/b/' ||
'/bucketname/o/data_folder/' ||
'summary/dreams.txt',
credential_name => 'STORE_CRED',
profile_name => 'GENAI',
user_prompt => 'The summary should start with ''The summary of ' ||
'the article is: ''',
params => '{"max_words":100, "summary_style":"list"}')
As response FROM dual;
RESPONSE
--------------------------------------------------------------------------------
The summary of the article is:
- The book "Dreams" by Henri Bergson explores the concept of dreams and their si
gnificance in understanding human consciousness.
- Bergson argues that dreams are not just random thoughts, but rather a way for
our unconscious mind to process and consolidate memories.
- He suggests that dreams are a result of the relaxation of our mental faculties
, which allows our unconscious mind to freely associate and create new connectio
ns between memories.
- The book also discusses the role of sensations, such as visual and auditory im
pressions, in shaping our dreams.
- Bergson's theory of dreams is compared to other theories, including those of F
reud and Jung, and is seen as a unique and insightful contribution to the field
of psychology.
- The book concludes by highlighting the importance of studying dreams in order
to gain a deeper understanding of human consciousness and the workings of the mi
nd.This example demonstrates passing a 35.66 MiB file as an input to
generate a summary. The DBMS_CLOUD_AI.SUMMARIZE function uses iterative refinement method
to process the chunks. See Iterative Refinement for more
information.
SELECT DBMS_CLOUD_AI.SUMMARIZE(
location_uri => 'https://objectstorage.ca-toronto-1.oraclecloud.com/n/namespace-string/b/' ||
'bucketname/o/data_folder/summary/Descartes_An_Intellectual_Biography.pdf',
credential_name => 'STORE_CRED',
profile_name => 'GENAI',
params => '{"chunk_processing_method":"iterative_refinement"}')
AS response FROM dual;
RESPONSE
--------------------------------------------------------------------------------
Stephen Gaukroger's intellectual biography of Rene Descartes provides a detailed
examination of the philosopher's crucial role in shaping modern thought, placin
g him within the cultural, religious, and scientific context of the early sevent
eenth century. It traces Descartes' intellectual journey from his education at L
a Fleche, where he rejected Aristotelian logic, to his influential interactions
with figures like Isaac Beeckman, which shaped his mechanistic worldview evident
in works like his hydrostatics manuscript and *Compendium Musicae*. The biograp
hy underscores Descartes' dual commitment to philosophy and science, highlightin
g his social status among the gentry, mathematical innovations such as solving t
he Pappus problem through algebraic geometry, and his epistemology based on clea
r and distinct ideas. It explores his mechanistic explanations of bodily functio
ns, challenging traditional soul-body distinctions, and his extensive natural ph
ilosophy in texts like *Le Monde* and *L'Homme*. Gaukroger also delves into Desc
artes' cosmological theories, including the vortex theory and laws of motion lin
ked to divine immutability, as well as his nuanced perspectives on animal cognit
ion versus human consciousness. Central to the narrative is Descartes' use of hy
perbolic doubt to combat skepticism and establish metaphysical foundations throu
gh the *cogito*, alongside his classification of ideas and theological proofs of
God's existence. The complex relationship between his natural philosophy and me
taphysics, especially in defining motion as a mode, and his innovative approach
to the passions in *Passions of the Soul*, rejecting Stoic views for a mind-body
union, are key themes. This portrayal captures Descartes' struggle with traditi
onal paradigms during a transformative era, emphasizing his enduring impact on p
hilosophy and science.Parent topic: Generate and Transform Content
10.6.3 Example: Select AI Translate
These examples
demonstrate how you can use the translate capability.
Review:
OCI
To use the Select AI translation feature, you must have the appropriate IAM policy permissions to access Oracle Cloud Infrastructure Language services.
Grant the permission to use ai-service-language-family
resource in your IAM policy. An example policy statement to grant permission to a
user group in a specific compartment is:
allow group <your group name> to use ai-service-language-family in compartment <your_compartment>
-
If using Resource Principal credential, assign the permission to the Dynamic Group.
-
If using Private Key credential, assign the permission to the User Group.
A Dynamic Group identifies resources such as databases or functions by matching their OCIDs or tags, while a User Group contains individual IAM users.
Use a dynamic group when the policy applies to OCI resources, and use a user group when the policy applies to human users. For detailed steps to create dynamic and user groups, see Managing Dymanic Groups.
See Language Policies for more information.
The following example shows using the translate action
on the SQL command line.
Note:
Your AI profile must specify the target language. This example has OCI as the AI provider.--Create an AI profile with language parameters
BEGIN
DBMS_CLOUD_AI.CREATE_PROFILE(
profile_name =>'GENAI_NEW',
attributes =>'{"provider": "oci",
"credential_name": "GENAI_CRED",
"target_language": "french",
"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.
SQL> exec DBMS_CLOUD_AI.SET_PROFILE('GENAI_NEW');
PL/SQL procedure successfully completed.
SQL> select ai translate I need to translate this;
RESPONSE
---------------------
Je dois traduire ceciThe following examples show using
translate as a Select AI action within the DBMS_CLOUD_AI.GENERATE function. See GENERATE Function for more information.
Note:
The AI profile can skip specifying the target language parameter if it is passed as an attribute inDBMS_CLOUD_AI.GENERATE.
The translate action is supplied in the DBMS_CLOUD_AI.GENERATE function
along with target_language and source_language.
This example uses generative AI translation. The input text this is a
document in English (source_language: "en") is
translated into French (target_language:
"fr").
SELECT DBMS_CLOUD_AI.GENERATE('select ai translate text to be translated')
FROM dual;
DECLARE
l_attributes clob := '{"target_language": "fr", "source_language": "en"}';
output clob;
BEGIN
output := DBMS_CLOUD_AI.GENERATE(
prompt => 'this is a document',
profile_name => 'oci_translate',
action => 'translate',
attributes => l_attributes
);
This example
calls the DBMS_CLOUD_AI.TRANSLATE function to use generative AI
translation, converting the input text from English
(source_language) into French
(target_language) using the specified AI profile.
See TRANSLATE Function for more details.
BEGIN
output_text := DBMS_CLOUD_AI.TRANSLATE(
profile_name => 'GENAI_NEW'
text => 'text to be translated',
source_language => 'English',
target_language => 'French');
END;
/Example: Display Supported Languages for a Provider
Query the AI_TRANSLATION_LANGUAGES view to see a list of
languages that your AI provider supports. See AI_TRANSLATION_LANGUAGES View for
details.
SELECT * FROM AI_TRANSLATION_LANGUAGES;
LANGUAGE_NAME LANGUAGE_CODE PROVIDER
-------------------- --------------- ---------------
ARABIC ar OCI
ARABIC ar GOOGLE
ARABIC ar AZURE
ARABIC ar AWS
CROATIAN hr OCI
CROATIAN hr GOOGLE
CROATIAN hr AZURE
CROATIAN hr AWS
CZECH cs OCI
CZECH cs GOOGLE
CZECH cs AZURE
--Query for all languages a certain provider supports
SELECT * FROM AI_TRANSLATION_LANGUAGES WHERE provider = 'GOOGLE';
LANGUAGE_NAME LANGUAGE_CODE PROVIDER
-------------------- --------------- ---------------
ARABIC ar GOOGLE
CROATIAN hr GOOGLE
CZECH cs GOOGLE
DANISH da GOOGLE
GERMAN de GOOGLE
GREEK el GOOGLE
ENGLISH en GOOGLE
SPANISH es GOOGLE
FINNISH fi GOOGLE
FRENCH fr GOOGLE
FRENCH CANADA fr-CA GOOGLEParent topic: Generate and Transform Content