- Oracle AI Vector Search User's Guide
- Generate Vector Embeddings
- Vector Generation Examples
- Generate Text for a Prompt: PL/SQL Example
Generate Text for a Prompt: PL/SQL Example
In this example, you can see how to generate text for a given prompt by accessing third-party text generation models.
A prompt can be an input string (such as a question that you ask an LLM or a command), and can include results from a search.
You can use the described functions either from the DBMS_VECTOR
or DBMS_VECTOR_CHAIN
package, depending on your use case.
To generate text using "What is Oracle Text?
" as the prompt:
- Start SQL*Plus and connect to Oracle Database as a local test user.
- Log in to SQL*Plus as the
sys
user, connecting assysdba
, to a pluggable database (PDB) within your multitenant container database (CDB):conn sys/password@CDB_PDB as sysdba
CREATE TABLESPACE tbs1 DATAFILE 'tbs5.dbf' SIZE 20G AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
- Create a local test user (
docuser
) and grant necessary privileges:DROP USER docuser cascade;
CREATE USER docuser identified by docuser DEFAULT TABLESPACE tbs1 quota unlimited on tbs1;
GRANT DB_DEVELOPER_ROLE, create credential to docuser;
- Connect to Oracle Database as the test user and alter the environment settings for your session:
CONN docuser/password@CDB_PDB
SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 10000 SET LONG 10000
- Set the HTTP proxy server, if configured:
EXEC UTL_HTTP.SET_PROXY('<proxy-hostname>:<proxy-port>');
- Grant connect privilege for a host using the
DBMS_NETWORK_ACL_ADMIN
procedure. This example uses*
to allow any host. However, you can explicitly specify each host that you want to connect to.BEGIN DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE( host => '*', ace => xs$ace_type(privilege_list => xs$name_list('connect'), principal_name => 'docuser', principal_type => xs_acl.ptype_db)); END; /
- Log in to SQL*Plus as the
- Set up your credentials for the REST provider that you want to access and then call
UTL_TO_GENERATE_TEXT
:-
Using Cohere, Google AI, Hugging Face, OpenAI, and Vertex AI:
-
Call
CREATE_CREDENTIAL
to create and store a credential.Cohere, Google AI, Hugging Face, OpenAI, and Vertex AI require the following authentication parameter:
{ "access_token": "<access token>" }
You will later refer to this credential name when declaring JSON parameters for the
UTL_TO_GENERATE_TEXT
call.exec dbms_vector_chain.drop_credential('<credential name>');
declare jo json_object_t; begin jo := json_object_t(); jo.put('access_token', '<access token>'); dbms_vector_chain.create_credential( credential_name => '<credential name>', params => json(jo.to_string)); end; /
Replace the
access_token
andcredential_name
values. For example:declare jo json_object_t; begin jo := json_object_t(); jo.put('access_token', 'AbabA1B123aBc123AbabAb123a1a2ab'); dbms_vector_chain.create_credential( credential_name => 'HF_CRED', params => json(jo.to_string)); end; /
-
Call
UTL_TO_GENERATE_TEXT
:-- select example var params clob; exec :params := ' { "provider": "<REST provider>", "credential_name": "<credential name>", "url": "<REST endpoint URL for text generation service>", "model": "<REST provider text generation model name>" }'; select dbms_vector_chain.utl_to_generate_text( 'What is Oracle Text?',json(:params)) from dual; -- PL/SQL example declare input clob; params clob; output clob; begin input := 'What is Oracle Text?'; params := ' { "provider": "<REST provider>", "credential_name": "<credential name>", "url": "<REST endpoint URL for text generation service>", "model": "<REST provider text generation model name>" }'; output := dbms_vector_chain.utl_to_generate_text(input, json(params)); dbms_output.put_line(output); if output is not null then dbms_lob.freetemporary(output); end if; exception when OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); DBMS_OUTPUT.PUT_LINE (SQLCODE); end; /
Replace the
provider
,credential_name
,url
, andmodel
values. Optionally, you can specify additional REST provider parameters.Cohere example:{ "provider": "Cohere", "credential_name": "COHERE_CRED", "url": "https://api.cohere.example.com/generateText", "model": "generate-text-model" }
Google AI example:{ "provider": "googleai", "credential_name": "GOOGLEAI_CRED", "url": "https://googleapis.example.com/models/", "model": "generate-text-model" }
Hugging Face example:{ "provider": "huggingface", "credential_name": "HF_CRED", "url": "https://api.huggingface.example.com/models/", "model": "generate-text-model", "wait_for_model": "true" }
OpenAI example:{ "provider": "openai", "credential_name": "OPENAI_CRED", "url": "https://api.openai.example.com", "model": "generate-text-model", "max_tokens": 60, "temperature": 1.0 }
Vertex AI example:{ "provider": "vertexai", "credential_name":"VERTEXAI_CRED", "url": "https://googleapis.example.com/models/", "model": "generate-text-model", "generation_config": { "temperature": 0.9, "topP": 1, "candidateCount": 1, "maxOutputTokens": 256 } }
-
-
Using Generative AI:
-
Call
CREATE_CREDENTIAL
to create and store an OCI credential (OCI_CRED
).Generative AI requires the following authentication parameters:{ "user_ocid": "<user ocid>", "tenancy_ocid": "<tenancy ocid>", "compartment_ocid": "<compartment ocid>", "private_key": "<private key>", "fingerprint": "<fingerprint>" }
You will later refer to this credential name when declaring JSON parameters for the
UTL_TO_GENERATE_TEXT
call.Note:
The generated private key may appear as:
You pass the-----BEGIN RSA PRIVATE KEY----- <private key string> -----END RSA PRIVATE KEY-----
<private key string>
value (excluding theBEGIN
andEND
lines), either as a single line or as multiple lines.exec dbms_vector_chain.drop_credential('OCI_CRED');
declare jo json_object_t; begin jo := json_object_t(); jo.put('user_ocid','<user ocid>'); jo.put('tenancy_ocid','<tenancy ocid>'); jo.put('compartment_ocid','<compartment ocid>'); jo.put('private_key','<private key>'); jo.put('fingerprint','<fingerprint>'); dbms_output.put_line(jo.to_string); dbms_vector_chain.create_credential( credential_name => 'OCI_CRED', params => json(jo.to_string)); end; /
Replace all the authentication parameter values. For example:
declare jo json_object_t; begin -- create an OCI credential jo := json_object_t(); jo.put('user_ocid','ocid1.user.oc1..aabbalbbaa1112233aabbaabb1111222aa1111bb'); jo.put('tenancy_ocid','ocid1.tenancy.oc1..aaaaalbbbb1112233aaaabbaa1111222aaa111a'); jo.put('compartment_ocid','ocid1.compartment.oc1..ababalabab1112233abababab1111222aba11ab'); jo.put('private_key','AAAaaaBBB11112222333...AAA111AAABBB222aaa1a/+'); jo.put('fingerprint','01:1a:a1:aa:12:a1:12:1a:ab:12:01:ab:a1:12:ab:1a'); dbms_output.put_line(jo.to_string); dbms_vector_chain.create_credential( credential_name => 'OCI_CRED', params => json(jo.to_string)); end; /
-
Call
UTL_TO_GENERATE_TEXT
:-- select example var params clob; exec :params := ' { "provider": "ocigenai", "credential_name": "OCI_CRED", "url": "<REST endpoint URL for text generation service>", "model": "<REST provider text generation model name>" }'; select dbms_vector_chain.utl_to_generate_text( 'What is Oracle Text?',json(:params)) from dual; -- PL/SQL example declare input clob; params clob; output clob; begin input := 'What is Oracle Text?'; params := ' { "provider": "ocigenai", "credential_name": "OCI_CRED", "url": "<REST endpoint URL for text generation service>", "model": "<REST provider text generation model name>" }'; output := dbms_vector_chain.utl_to_generate_text(input, json(params)); dbms_output.put_line(output); if output is not null then dbms_lob.freetemporary(output); end if; exception when OTHERS THEN DBMS_OUTPUT.PUT_LINE (SQLERRM); DBMS_OUTPUT.PUT_LINE (SQLCODE); end; /
Replace the
url
andmodel
values. Optionally, you can specify additional REST provider-specific parameters.For example:
{ "provider": "OCIGenAI", "credential_name": "GENAI_CRED", "url": "https://generativeai.oci.example.com/generateText", "model": "generate-text-model", "inferenceRequest": { "maxTokens": 300, "temperature": 1 } }
-
The generated text appears as follows:
BMS_VECTOR_CHAIN.UTL_TO_GENERATE_TEXT(:INPUT,JSON(:PARAMS)) -------------------------------------------------------------------------------- Oracle Text is a powerful tool that enhances Oracle Database with integrated text mining and text analytics capabilities. It enables users to extract valuable insights and make informed decisions by analyzing unstructured text data stored within the database. Here are some enhanced capabilities offered by Oracle Text: 1. Full-Text Search: Enables powerful and rapid full-text searches across large collections of documents. This helps users find relevant information quickly and effectively, even within massive datasets. 2. Natural Language Processing: Implements advanced language processing techn iques to analyze text and extract meaningful information. This includes capabilities like tokenization, stemming, lemmatization, and part-of-speech tagging, which collectively facilitate efficient text processing and understanding. 3. Sentiment Analysis: Provides a deeper understanding of sentiment expressed in text. It enables businesses to automatically analyze customer opinions, feed back, and reviews, helping them gain valuable insights into customer sentiment, satisfaction levels, and potential trends. 4. Entity Recognition: Automatically identifies and categorizes entities with in text, such as names of people, organizations, locations, or any other specific terms of interest. This is useful in applications like customer relationship management, where linking relevant information to individuals or organizations is crucial. 5. Contextual Analysis: Delivers insights into the context and relationships between entities and concepts in textual data. It helps organizations better und erstand the broader implications and associations between entities, facilitating a deeper understanding of their data. These features collectively empower various applications, enhancing the function ality of the Oracle Database platform to allow businesses and organizations to derive maximum value from their unstructured text data. Let me know if you'd like to dive deeper into any of these specific capabilities , or if there are other aspects of Oracle Text you'd like to explore further.
-
Related Topics
Parent topic: Vector Generation Examples