### Shell: Start Ollama Serve

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/generate-text-locally-ollama

Starts the Ollama server in the foreground. This command is used when Ollama is installed as a standalone binary and needs to be manually started.

```shell
ollama serve

```

--------------------------------

### UTL_TO_GENERATE_TEXT Provider Specific Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_generate_text-dbms_vector

Provides example JSON configurations for integrating with different AI service providers, illustrating how to set up the necessary parameters for each.

```APIDOC
## UTL_TO_GENERATE_TEXT Provider Examples

### Description
Examples demonstrating how to configure the `UTL_TO_GENERATE_TEXT` procedure for specific third-party AI providers. These examples are for illustration and may require adjustments based on current provider documentation.

### Method
N/A (Procedure Call)

### Endpoint
N/A (Internal Database Procedure)

### Parameters
See the main "UTL_TO_GENERATE_TEXT Configuration Parameters" section for a detailed list of available parameters.

### Request Examples
#### Cohere Example
```json
{
  "provider": "cohere",
  "credential_name": "COHERE_CRED",
  "url": "https://api.cohere.example.com/chat",
  "model": "command"
}
```

#### Generative AI Example (Conceptual)
```json
{
  "provider": "vertexai",
  "credential_name": "VERTEXAI_CRED",
  "url": "https://us-central1-aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION_ID/publishers/google/models/MODEL_NAME:predict",
  "model": "gemini-pro",
  "temperature": 0.2,
  "maxOutputTokens": 256
}
```

### Response
(Response details depend on the procedure's execution and the AI provider's output.)

### Error Handling
Ensure that all provider-specific parameters and values are correctly formatted according to the respective third-party documentation.
```

--------------------------------

### Install OML4Py Client

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-pretrained-models-onnx-model-end-end-instructions

Installs the OML4Py client by first unzipping the downloaded client archive and then installing the specific Python wheel file. This makes the OML4Py library available for use in Python.

```bash
unzip oml4py-client-linux-x86_64-2.1.zip
pip3 install client/oml-2.1-cp312-cp312-linux_x86_64.whl
```

--------------------------------

### Install OML4Py Client (Shell)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/generate-multi-modal-embeddings-using-clip

This sequence of commands first unzips the OML4Py client archive and then installs the OML4Py Python package using pip. Ensure you have downloaded the correct client version for your system.

```shell
unzip oml4py-client-linux-x86_64-2.0.1.zip
pip3 install client/oml-2-0-cp312-cp312-linux_x86_64.whl
```

--------------------------------

### Install Python 3.12.3 on Oracle Linux 8

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/generate-multi-modal-embeddings-using-clip

Installs Python 3.12.3 by downloading, extracting, configuring, and compiling the source code. It assumes necessary development libraries are installed and uses a specific directory for installation. Ensure to replace `$HOME` with your actual home directory path.

```shell
sudo yum install libffi-devel openssl openssl-devel tk-devel xz-devel zlib-devel bzip2-devel readline-devel libuuid-devel ncurses-devel libaio
mkdir -p $HOME/python
wget https://www.python.org/ftp/python/3.12.3/Python-3.12.3.tgz
tar -xvzf Python-3.12.3.tgz --strip-components=1 -C $HOME/python
cd $HOME/python
./configure --prefix=$HOME/python
make clean; make
make altinstall
```

--------------------------------

### Oracle Database: Generate Text from Image (PL/SQL Example)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/describe-images-using-public-third-party-apis

Uses PL/SQL to generate text from an image. It declares variables for input, media data, media type, parameters, and output. The example is started but not completed, showing the setup for calling UTL_TO_GENERATE_TEXT.

```plsql
-- PL/SQL example

declare
  input clob;
  media_data blob;
  media_type varchar2(32);
  params clob;
  output clob;
begin
  input := 'Describe this image';

```

--------------------------------

### Example INT8 to BINARY Conversion

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/binary-vectors

Provides a concrete example of converting a list of INT8 values to their BINARY representation, illustrating the conversion logic.

```text
INT8: [25, 11, -99, -114, 13, -17, -59, 44]
BINARY: [1, 1, 0, 0, 1, 0, 0, 1]
```

--------------------------------

### Configure Hugging Face for Vector Embeddings

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_embedding-and-utl_to_embeddings-dbms_vector

Example configuration for using the Hugging Face provider. Requires 'provider', 'credential_name', 'url', and 'model'.

```json
{
  "provider"       : "huggingface",
  "credential_name": "HF_CRED",
  "url"            : "https://api.huggingface.example.com/",
  "model"          : "sentence-transformers/all-MiniLM-L6-v2"
}
```

--------------------------------

### SQL*Plus Connection and Setup

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/sql-rag-example

Connects to Oracle Database using SQL*Plus and sets server output and echo. Creates a local test user 'vector' with necessary privileges and grants network access. This is a prerequisite for using AI Vector Search functionalities.

```sql
conn sys/password AS sysdba

SET SERVEROUTPUT ON;
SET ECHO ON;
SET LONG 100000;

DROP USER vector cascade;
CREATE USER vector identified by <my vector password>
GRANT DB_DEVELOPER_ROLE, CREATE CREDENTIAL TO vector;

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => '*',
    ace => xs$ace_type(privilege_list => xs$name_list('connect'),
                       principal_name => 'VECTOR',
                       principal_type => xs_acl.ptype_db));
END;
/

conn docuser/password;
```

--------------------------------

### Install Python 3.12 on Oracle Linux

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-pretrained-models-onnx-model-end-end-instructions

Installs Python 3.12.6 from source on Oracle Linux. This involves downloading the source code, configuring the build, and installing it in the user's home directory. Dependencies like development libraries are also installed using yum.

```shell
sudo yum install libffi-devel openssl openssl-devel tk-devel xz-devel zlib-devel bzip2-devel readline-devel libuuid-devel ncurses-devel libaio
mkdir -p $HOME/python
wget https://www.python.org/ftp/python/3.12.6/Python-3.12.6.tgz
tar -xvzf Python-3.12.6.tgz --strip-components=1 -C $HOME/python
cd $HOME/python
./configure --prefix=$HOME/python
make clean; make
make altinstall
```

--------------------------------

### Configure Ollama for Vector Embeddings

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_embedding-and-utl_to_embeddings-dbms_vector

Example configuration for the Ollama provider, often used for local model hosting. Includes 'provider', 'host', 'url', and 'model'.

```json
{
  "provider"       : "ollama", 
  "host"           : "local", 
  "url"            : "http://localhost:11434/api/embeddings", 
  "model"          : "phi3:mini"
}
```

--------------------------------

### Oracle SQL: Create Global IVF Index Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/inverted-file-flat-index-creation-syntax-and-parameters

Demonstrates the creation of a global Inverted File Flat (IVF) vector index using SQL. This example specifies the index name, table, column, distance metric (COSINE), and target accuracy.

```sql
CREATE VECTOR INDEX galaxies_ivf_idx ON galaxies (embedding) 
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 95;
```

--------------------------------

### Create requirements.txt for Python Packages

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-pretrained-models-onnx-model-end-end-instructions

Defines the Python packages required for the project, including libraries for data manipulation, machine learning, and Oracle integration. This file is used by pip to install all necessary dependencies.

```plaintext
--extra-index-url https://download.pytorch.org/whl/cpu
pandas==2.2.2
setuptools==70.0.0
scipy==1.14.0
matplotlib==3.8.4
oracledb==2.4.1
scikit-learn==1.5.1
numpy==2.0.1
onnxruntime==1.20.0
onnxruntime-extensions==0.12.0
onnx==1.17.0
torch==2.6.0
transformers==4.49.0
sentencepiece==0.2.0
```

--------------------------------

### Upgrade Pip and Install Requirements

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-pretrained-models-onnx-model-end-end-instructions

Upgrades the pip package installer to the latest version and then installs all Python packages listed in the 'requirements.txt' file. This ensures all project dependencies are met.

```bash
pip3 install --upgrade pip
pip3 install -r requirements.txt
```

--------------------------------

### Generate Text with OCI Generative AI (meta.llama-3.1-70b-instruct) - PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_generate_text-dbms_vector_chain

PL/SQL code to generate text using OCI Generative AI with the 'meta.llama-3.1-70b-instruct' model. This example sets up the input prompt and provider configuration, executes the text generation function, and prints the output. Includes basic exception handling.

```plsql
-- PL/SQL example

declare
  input clob;
  params clob;
  output clob;
begin
  input := 'What is Oracle Text?';

  params := '
{
   "provider"       : "ocigenai",
   "credential_name": "OCI_CRED",
   "url"            : "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
   "model"          : "meta.llama-3.1-70b-instruct",
   "chatRequest"    : {
                       "topK" : 1
                      }
}';

  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;
/
```

--------------------------------

### Install s3fs-fuse for OCI Object Storage

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/sql-quick-start-using-vector-embedding-model-uploaded-database

Installs the s3fs-fuse package on a Linux system, enabling the mounting of OCI Object Storage buckets as local file systems. This is essential for managing cloud-based model files.

```shell
yum install -y s3fs-fuse
```

--------------------------------

### Cohere Example Configuration for Text Generation

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_generate_text-dbms_vector

This JSON configuration demonstrates how to set up parameters for using the Cohere AI service provider for text generation. It specifies the provider, credential name, the REST endpoint URL for Cohere's API, and the desired model.

```json
{
  "provider"       : "cohere", 
  "credential_name": "COHERE_CRED",
  "url"            : "https://api.cohere.example.com/chat",
  "model"          : "command"
}
```

--------------------------------

### Set Python Environment Variables

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-pretrained-models-onnx-model-end-end-instructions

Configures essential environment variables for the newly installed Python 3.12. It sets PYTHONHOME, PATH, and LD_LIBRARY_PATH to point to the Python installation directory, ensuring the system can find and use the Python executable and libraries.

```shell
export PYTHONHOME=$HOME/python
export PATH=$PYTHONHOME/bin:$PATH
export LD_LIBRARY_PATH=$PYTHONHOME/lib:$LD_LIBRARY_PATH
```

--------------------------------

### Oracle SQL: Vector Index Hints

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/your-vector-documentation-map-genai-prompts

Illustrates how to use index hints in Oracle SQL to guide the optimizer towards using specific vector indexes (e.g., HNSW, IVF) for similarity search queries. This allows manual control over index selection for performance tuning.

```sql
SELECT /*+ INDEX(t my_vector_hnsw_idx) */ id, v1
FROM my_vector_table t
WHERE VECT_NH(v1, :query_vector) = 1;

SELECT /*+ INDEX(t my_vector_ivf_idx) */ id, v1
FROM my_vector_table t
WHERE VECT_IVF(v1, :query_vector) = 1;
```

--------------------------------

### Show Preconfigured ONNX Pipeline Models (Python)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-pretrained-models-onnx-model-end-end-instructions

Starts a Python interpreter and imports the ONNXPipelineConfig class to display a list of all available pre-configured ONNX models. This helps in selecting a model for export or direct database loading.

```python
python3

from oml.utils import ONNXPipelineConfig

ONNXPipelineConfig.show_preconfigured()
```

--------------------------------

### Create OCI Credential with Example Values using PL/SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/generate-text-using-public-third-party-apis

This PL/SQL example provides concrete values for creating an OCI credential. It populates the JSON object with sample OCIDs, a private key string, and a fingerprint, then uses `dbms_vector_chain.create_credential` to save it. This is useful for testing or as a template.

```PL/SQL
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_vector_chain.create_credential(
    credential_name   => 'OCI_CRED',
    params            => json(jo.to_string));
end;
/
```

--------------------------------

### Configure Cohere for Vector Embeddings

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_embedding-and-utl_to_embeddings-dbms_vector

Example configuration for using the Cohere provider to generate vector embeddings. Requires 'provider', 'credential_name', 'url', 'model', and 'input_type'.

```json
{
  "provider"       : "cohere",
  "credential_name": "COHERE_CRED",
  "url"            : "https://api.cohere.example.com/embed",
  "model"          : "embed-english-light-v2.0",
  "input_type"     : "search_query"
}
```

--------------------------------

### Configure OpenAI for Vector Embeddings

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_embedding-and-utl_to_embeddings-dbms_vector

Example configuration for the OpenAI provider. Specifies 'provider', 'credential_name', 'url', and 'model' for embedding generation.

```json
{
  "provider"       : "openai",
  "credential_name": "OPENAI_CRED",
  "url"            : "https://api.openai.example.com/embeddings",
  "model"          : "text-embedding-3-small"
}
```

--------------------------------

### Display Available Templates

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/onnx-pipeline-models-text-embedding

This command retrieves and displays a list of all available text templates for configuring ONNX pipelines. Templates provide a starting point for custom model configurations, including preprocessing and postprocessing operations.

```python
ONNXPipelineConfig.show_templates()
```

--------------------------------

### Add Crontab Entry for Auto-Mount

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/sql-quick-start-using-vector-embedding-model-uploaded-database

Appends the auto-mount configuration to the server's crontab. This step finalizes the setup for persistent OCI Object Storage mounting, ensuring the bucket is mounted after each reboot.

```shell
crontab -l | cat - crontab-fragment.txt >crontab.txt && crontab crontab.txt

rm -f crontab.txt crontab-fragment.txt
```

--------------------------------

### Importing ONNX Model and Generating Embeddings in Oracle Database

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/import-onnx-models-oracle-database-end-end-example

This script outlines the steps to import a pretrained ONNX embedding model into Oracle Database and prepare the environment for generating embeddings. It includes granting necessary privileges, creating a directory object, and loading the model using `DBMS_VECTOR.LOAD_ONNX_MODEL`. The script also shows example queries to verify the model's attributes and details after loading.

```sql
conn sys/<password>@pdbname as sysdba
grant db_developer_role to dmuser identified by <password>;
grant create mining model to dmuser;
 
create or replace directory DM_DUMP as '<work directory path>';
grant read on directory dm_dump to dmuser;
grant write on directory dm_dump to dmuser;
>conn dmuser/<password>@<pdbname>;

–- Drop the model if it exits							  
exec DBMS_VECTOR.DROP_ONNX_MODEL(model_name => 'doc_model', force => true);

-- Load Model
EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL(
	'DM_DUMP',
	'my_embedding_model.onnx',
	'doc_model',
	JSON('{"function" : "embedding", "embeddingOutput" : "embedding"}')
);
/

--check the attributes view
set linesize 120
col model_name format a20
col algorithm_name format a20
col algorithm format a20
col attribute_name format a20
col attribute_type format a20
col data_type format a20 

SQL> SELECT model_name, attribute_name, attribute_type, data_type, vector_info
FROM user_mining_model_attributes
WHERE model_name = 'DOC_MODEL'
ORDER BY ATTRIBUTE_NAME;
 
 
OUTPUT:

MODEL_NAME           ATTRIBUTE_NAME       ATTRIBUTE_TYPE       DATA_TYPE  VECTOR_INFO
-------------------- -------------------- -------------------- ---------- ---------------
DOC_MODEL                INPUT_STRING         TEXT                 VARCHAR2
DOC_MODEL                ORA$ONNXTARGET       VECTOR               VECTOR     VECTOR(128,FLOA
                                                                          T32)
 
 
 
SQL> SELECT MODEL_NAME, MINING_FUNCTION, ALGORITHM,
ALGORITHM_TYPE, MODEL_SIZE
FROM user_mining_models
WHERE model_name = 'DOC_MODEL'
ORDER BY MODEL_NAME;
 
OUTPUT:
MODEL_NAME           MINING_FUNCTION                ALGORITHM            ALGORITHM_ MODEL_SIZE
-------------------- ------------------------------ -------------------- ---------- ----------
DOC_MODEL                EMBEDDING                      ONNX                 NATIVE       17762137
 
 
 
SQL> select * from DM$VMDOC_MODEL ORDER BY NAME;
 
OUTPUT:
NAME                                     VALUE
---------------------------------------- ----------------------------------------
Graph Description                        Graph combining g_8_torch_jit and torch_
                                         jit
                                         g_8_torch_jit
 
 
Graph Name                               g_8_torch_jit_torch_jit
Input[0]                                 input:string[1]
Output[0]                                embedding:float32[?,128]
Producer Name                            onnx.compose.merge_models
Version                                  1

6 rows selected.
 
 
 
SQL> select * from DM$VPDOC_MODEL ORDER BY NAME;
 
OUTPUT:
NAME                                     VALUE
---------------------------------------- ----------------------------------------
batching                                 False
embeddingOutput                          embedding
 
 
SQL> select * from DM$VJDOC_MODEL;
 
OUTPUT:
METADATA
```

--------------------------------

### Oracle SQL: Create Global IVF Index with Parameters Example

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/inverted-file-flat-index-creation-syntax-and-parameters

Illustrates creating a global Inverted File Flat (IVF) vector index with specific parameters, including target accuracy and the number of neighbor partitions. This allows for finer control over index creation.

```sql
CREATE VECTOR INDEX galaxies_ivf_idx ON galaxies (embedding) 
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 90 PARAMETERS (type IVF, neighbor partitions 10);
```

--------------------------------

### Generate Text from Image using OpenAI

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_generate_text-dbms_vector

This snippet demonstrates how to generate text from an image using the OpenAI provider via Oracle Database. It includes SQL SELECT and PL/SQL examples, setting up input prompt, media data, media type, and provider configuration parameters.

```sql
-- select example

var input clob;
var media_data blob;
var media_type clob;
var params clob;

begin
  :input := 'Describe this image';
  :media_data := load_blob_from_file('DEMO_DIR', 'sample_image.jpeg');
  :media_type := 'image/jpeg';
  :params := '
{
  "provider"       : "openai",
  "credential_name": "OPENAI_CRED",
  "url"            : "https://api.openai.com/v1/chat/completions",
  "model"          : "gpt-4o-mini",
  "max_tokens"     : 60
}';
end;
/

select dbms_vector.utl_to_generate_text(:input, :media_data, :media_type, json(:params));

```

```plsql
-- PL/SQL example

declare
  input clob;
  media_data blob;

```

--------------------------------

### Configure Generative AI for Vector Embeddings

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_embedding-and-utl_to_embeddings-dbms_vector

Example configuration for using the Generative AI (OCI GenAI) provider. Includes 'provider', 'credential_name', 'url', 'model', and 'batch_size'. Batch size can optimize performance.

```json
{
  "provider"       : "ocigenai",
  "credential_name": "OCI_CRED",
  "url"            : "https://generativeai.oci.example.com/embedText",
  "model"          : "cohere.embed-english-v3.0",
  "batch_size"     : 10
}
```

--------------------------------

### Generate Summary using Oracle Generative AI Provider (SQL)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_summary

This SQL example shows how to generate a summary using Oracle's Generative AI service. It configures the 'ocigenai' provider and specifies a credential name. This setup allows leveraging Oracle's cloud-based AI models for summarization tasks.

```sql
-- select example

var params clob;
begin
  :params := '
{
 "provider": "ocigenai", 
 "credential_name": "OCI_CRED", 

```

--------------------------------

### Set up Oracle Database for AI Vector Search

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-text-string-embedding-oracle-database

These SQL commands configure Oracle Database for AI Vector Search by creating a tablespace, a user with necessary roles, and granting privileges. This setup is a prerequisite for loading and utilizing embedding models.

```sql
conn sys/password as sysdba

```

```sql
CREATE TABLESPACE tbs1
DATAFILE 'tbs5.dbf' SIZE 20G AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

```

```sql
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 10000
SET LONG 10000

```

```sql
DROP USER docuser cascade;

```

```sql
CREATE USER docuser identified by docuser DEFAULT TABLESPACE tbs1 quota unlimited on tbs1;

```

```sql
GRANT DB_DEVELOPER_ROLE, create credential to docuser;

```

```sql
CONN docuser/password

```

--------------------------------

### Create Python 3 and Pip 3 Symlinks

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-pretrained-models-onnx-model-end-end-instructions

Creates symbolic links for 'python3' and 'pip3' to point to the specific version of Python installed (e.g., 'python3.12' and 'pip3.12'). This allows for easier invocation of Python and pip commands.

```shell
cd $HOME/python/bin
ln -s python3.12 python3
ln -s pip3.12 pip3
```

--------------------------------

### Oracle HNSW Vector Index Creation Examples

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/hierarchical-navigable-small-world-index-syntax-and-parameters

Provides practical examples of SQL statements for creating HNSW vector indexes. These examples demonstrate different configurations, including specifying distance metrics, target accuracy, and HNSW-specific parameters like neighbors and efconstruction.

```sql
CREATE VECTOR INDEX galaxies_hnsw_idx ON galaxies (embedding) ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;
```

```sql
CREATE VECTOR INDEX galaxies_hnsw_idx ON galaxies (embedding) ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 90 PARAMETERS (type HNSW, neighbors 40, efconstruction 500);
```

--------------------------------

### Hugging Face Configuration for Vector Search

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_generate_text-dbms_vector

This configuration example sets up the Hugging Face provider for vector search. It includes the provider, credential name, the Hugging Face API endpoint, and the specific model identifier, like 'gpt2'. Verify that HF_CRED is properly configured.

```json
{
  "provider"        : "huggingface",
  "credential_name" : "HF_CRED",
  "url"             : "https://api.huggingface.example.com/models/",
  "model"           : "gpt2"
}
```

--------------------------------

### Oracle PL/SQL: UTL_TO_CHUNKS Example PARAMS JSON

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_chunks-dbms_vector

Demonstrates a practical example of the JSON object for the PARAMS parameter in UTL_TO_CHUNKS. This specific example configures chunking by vocabulary, sets a maximum size, specifies custom split points, and includes normalization options.

```json
JSON('
  {
    "by"           :    "vocabulary",
    "vocabulary"   :    "myvocab",
    "max"          :    "100",
    "overlap"      :    "0",
    "split"        :    "custom",
    "custom_list"  :    [ "<p>" , "<s>" ],
    "language"     :    "american",
    "normalize"    :    "options",
    "norm_options" :    [ "WHITESPACE" ] 
  }
')
```

--------------------------------

### Generate Text with OCI Generative AI (meta.llama-3.1-70b-instruct) - SQL

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/utl_to_generate_text-dbms_vector_chain

SQL example for text generation using OCI Generative AI with the 'meta.llama-3.1-70b-instruct' model. It configures the provider and model parameters within a CLOB and utilizes the `dbms_vector_chain.utl_to_generate_text` function. Assumes valid OCI credentials.

```sql
-- select example

var params clob;
exec :params := '
{
   "provider"       : "ocigenai",
   "credential_name": "OCI_CRED",
   "url"            : "https://inference.generativeai.us-chicago-1.oci.oraclecloud.com/20231130/actions/chat",
   "model"          : "meta.llama-3.1-70b-instruct",
   "chatRequest"    : {
                       "topK" : 1
                      }
}';

select dbms_vector_chain.utl_to_generate_text(
 'What is Oracle Text?',
 json(:params)) from dual;
```

--------------------------------

### Explain Plan for Similarity Search

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/sql-quick-start-using-vector-embedding-model-uploaded-database

Generates and displays the execution plan for a similarity search query using Oracle's `EXPLAIN PLAN` and `DBMS_XPLAN`. This helps in understanding how the database optimizer resolves the query for performance analysis. Dependencies include the similarity search query and a `PLAN_TABLE`.

```sql
EXPLAIN PLAN FOR
SELECT doc_id, chunk_id, chunk_data
FROM doc_chunks
ORDER BY vector_distance(chunk_embedding , :query_vector, COSINE)
FETCH FIRST 4 ROWS ONLY;

select plan_table_output from table(dbms_xplan.display('plan_table',null,'all'));

```

```text
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1651750914
-------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |     4 |   104 |       |   549   (3)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |               |       |       |       |            |          |
|   2 |   VIEW                  |               |  5014 |   127K|       |   549   (3)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY|               |  5014 |   156K|   232K|   549   (3)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | DOC_CHUNKS    |  5014 |   156K|       |   480   (3)| 00:00:01 |
-------------------------------------------------------------------------------------------------

```

--------------------------------

### Ollama: Running the Local LLM Server

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/describe-images-using-ollama

This command initiates the Ollama server as a standalone binary. This is necessary for local LLM operations, allowing the database to communicate with the Ollama API. Ensure Ollama is installed and configured before running this command.

```bash
ollama serve
```

--------------------------------

### Example Generated Embedding (Text)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-text-string-embedding-using-public-third-party-apis

This is an example of the numerical vector output, representing an embedding, generated by one of the supported providers. The format is a list of floating-point numbers.

```text
EMBEDDING
-------------------------------------------------------------------------------------------------------------------------------------
[8.78423732E-003,-4.29633334E-002,-5.93001908E-003,-4.65480909E-002,2.14333013E-002,6.53376281E-002,-5.93746938E-002,2.10403297E-002,
4.38376889E-002,5.22960871E-002,1.25104953E-002,6.49512559E-002,-9.26998071E-003,-6.97442219E-002,-3.02916039E-002,-4.74979728E-003,
-1.08755399E-002,-4.63751052E-003,3.62781435E-002,-9.35919806E-002,-1.13934642E-002,-5.74270077E-002,-1.36667723E-002,2.42995787E-002,
-6.96804151E-002,4.93822657E-002,1.01460628E-002,-1.56464987E-002,-2.39410568E-002,-4.27529104E-002,-5.65665103E-002,-1.74160264E-002,
5.05326502E-002,4.31500375E-002,-2.6994409E-002,-1.72731467E-002,9.30535868E-002,6.85951149E-004,5.61876409E-003,-9.0233935E-003,
-2.55788807E-002,-2.04174276E-002,3.74175981E-002,-1.67872179E-002,1.07479304E-001,-6.64602639E-003,-7.65537247E-002,-9.71965566E-002,
-3.99636962E-002,-2.57076006E-002,-5.62455431E-002,-1.3583754E-001,3.45946029E-002,1.85191762E-002,3.01524661E-002,-2.62163244E-002,
-4.05582506E-003,1.72979087E-002,-3.66434865E-002,-1.72491539E-002,3.95228416E-002,-1.05518714E-001,-1.27463877E-001,1.42578809E-002
```

--------------------------------

### Ollama: Setting Up Proxy for Internet Access

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/describe-images-using-ollama

This example shows how to configure proxy settings for Ollama on a Linux system. These environment variables are crucial if your network requires a proxy to access the internet, enabling Ollama to download models and connect to external services. It covers HTTPS, HTTP, and FTP proxies, as well as exceptions for local connections.

```bash
-- set a proxy if you require one

export https_proxy=<proxy-hostname>:<proxy-port>
export http_proxy=<proxy-hostname>:<proxy-port>
export no_proxy=localhost,127.0.0.1,.example.com
export ftp_proxy=<proxy-hostname>:<proxy-port>
```

--------------------------------

### Create Object Storage Mount Point

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/sql-quick-start-using-vector-embedding-model-uploaded-database

Creates a directory to serve as the mount point for an OCI Object Storage bucket and sets the ownership to the 'oracle' user and 'oinstall' group. This prepares the system for mounting the bucket.

```shell
mkdir /mnt/bucket
chown oracle:oinstall /mnt/bucket
```

--------------------------------

### Shell: Ollama Proxy and Server Configuration

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-text-string-embedding-locally-ollama

This shell script demonstrates how to set environment variables for proxy configuration if required for Ollama to access the internet. It also shows how to start the Ollama server.

```shell
-- set a proxy if you require one

export https_proxy=<proxy-hostname>:<proxy-port>
export http_proxy=<proxy-hostname>:<proxy-port>
export no_proxy=localhost,127.0.0.1,.example.com
export ftp_proxy=<proxy-hostname>:<proxy-port>

ollama serve
```

--------------------------------

### Example INT8 and UBINARY Embeddings (Conceptual)

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/binary-vectors

This conceptual example illustrates the representation of an INT8 embedding from Cohere and a UBINARY embedding, as processed by Oracle Database. It highlights that any necessary quantization or binary formatting should be managed by the client application before data is sent to the database.

```plaintext
INT8 Embedding of 1024 dimensions from Cohere embed-english-v3.0:
[25, 11, -99, -114, 13, -17, -59, 44, 65, 33, -50, -2, 28, -16, -6, -20, -33, 
49, -59, -50, 0, -82, -67, 10, 82, -2, -126, -28, -32, -69, -13, 120, 54, 4, 
-71, 24, 4, -37, -57, 34, 16, -7, 27, -74, -12, 13, 1, -24, 65, -24, 28, 46, 
25, -33, -25, 36, 3, -47, 12, -49, -17, 11, 53, 70, -18, 10, -8, 4, 0, -33, 
10, -3, 27, -24, -35, -24, 23, -32, 0, -4, -21, -7, -29, -48, -7, -28, -25, 
-8, 54, -7, 14, -8, 39, 78, 0, -13, 26, 2, 40, 27, -35, -26, 5, -23, 15, 72, 
-4, -5, 33, 14, 18, 11, 0, -6, 6, -16, -53, 56, -35, 15, -1, -8, 83, 28, -2, 
27, -34, -60, 36, 4, 14, 21, -69, 17, -22, 0, 16, -77, 29, 27, 26, 0, 81, 15, 
-90, 7, 22, -2, -26, -39, -31, -10, 2, 32, -30, 40, -71, 29, 2, 36, -72, -6, 
42, -16, -16, 6, 40, 30, 1, -31, -42, 31, 56, 18, 0, 9, 27, 59, 11, 38, 28, 
-30, 73, -10, -56, 6, 17, 87, 15, 1, 49, -33, -68, 0, 10, -49, 18, -10, 8, 12, 
52, -31, 7, -37, -25, -53, 9, -5, 72, 14, -37, -41, 30, -54, -60, 30, -62, 20, 
3, 7, 64, -7, 48, 16, 19, 1, -43, -18, -91, -6, -113, 104, 42, 61, -24, -15, 
20, -9, 4, 36, 27, 46, -30, -39, 43, -14, 53, -36, -4, 35, 74, 37, 1, -19, 62, 
12, -13, 8, -11, 21, -4, 96, 29, 17, -99, 2, -67, -32, -55, -8, 55, 16, -29, 
28, 47, 47, -77, 0, -24, 1, 1, 38, 28, -11, 2, -55, 4, 18, 42, 99, 98, 1, 17, 
18, -21, 4, 89, 66, -32, 17, 56, 14, -2, -45, 19, -30, 26, 14, 34, -36, 5, 74, 
50, 33, 47, -37, 34, 61, -8, -62, 46, 56, -55, 0, 33, 5, -72, -29, -48, 21, 40, 
22, 3, 39, -1, 10, 32, -47, 28, 19, 92, -5, -13, 2, 12, -21, -33, -9, 31, -2, 
-25, -20, -14, 1, 53, -34, -26, 17, 72, -35, -36, -26, -86, -20, 55, -4, -53, 
-14, 47, 26, 82, -3, -41, -18, -40, -94, 87, 3, -17, 38, 54, 17, 62, -23, 61, 
20, -4, 18, 37, 21, -37, -10, -43, -32, -40, -29, 43, 75, -44, -3, 47, 9, -10, 
29, -26, 55, 35, -17, 43, 37, -8, 19, 0, -32, -49, 43, -27, 16, -81, 34, 56, 
15, -33, -13, -30, -13, -28, 54, -61, -90, -45, -101, -52, -101, 5, 22, 7, 72, 
-30, 31, 27, 42, -47, -6, -30, -30, 42, 13, -23, 63, -84, -20, -17, 61, -40, 
35, 37, 21, -8, 110, 108, 26, -49, -1, -31, 8, 10, 7, 29, -67, -29, 72, 15, 11, 
4, -34, 12, 28, -48, -21, -81, 38, -29, 26, 4, 10, 29, -11, 26, -78, -51, -52, 
27, -92, -23, -5, -11, 31, 18, -33, -49, 7, -51, -35, -57, -14, 121, -8, 29, 25, 
70, -19, 29, 48, -41, 48, -18, 19, -18, -13, 46, 27, 47, 42, 1, -33, 20, -27, 8, 
-31, 31, 1, 0, 11, -4, 32, -65, -7, 9, -11, 15, 3, -34, 42, -15, -71, -5, 3, 8, 
-8, 22, -7, -70, 10, 21, -127, -114, 13, -11, 46, -13, -10, -10, 29, -59, 43, 
-1, -17, -21, 8, -15, 12, 1, -73, -26, -5, 6, 37, 23, 46, 73, 14, -74, 84, -2, 
-22, -6, 5, -7, -26, 28, -39, -23, -22, 14, 38, 0, -2, 41, 27, -65, 30, 3, -23, 
53, 86, 35, -32, -48, -15, 32, 21, -26, -48, -26, 32, 32, 4, -70, -72, -62, -28, 
-14, -86, -10, -63, 44, -68, -41, 27, -52, 33, -56, -30, 5, 84, -54, 16, -22, 
-20, 16, 34, 14, -25, 8, -14, -13, -28, -40, 16, 41, -5, -88, -35, 55, -82, 55, 
74, -55, -12, 58, 57, -83, -26, 55, 32, -6, 42, -14, 35, -5, -36, 84, -40, -29, 
7, -20, -17, 23, -20, -49, -48, 22, 49, -30, 35, 48, 5, 34, 17, 13, 30, 33, -38, 
-37, 10, -52, -24, 67, -15, -12, -3, -11, -46, -7, 32, 10, -46, 3, 18, -7, -26, 
0, -40, 23, -46, 89, 37, 3, -29, -51, -32, 49, -51, 9, 16, -47, -26, 14, 10, 14, 
-13, 11, 16, -18, 54, -24, 18, -14, -51, -89, -24, 20, 12, 2, 62, 13, 53, -22, 
2, 22, -14, 29, -9, 51, -42, -97, 28, 49, -4, -93, -17, -26, 46, 47, 33, -33, 
25, 81, -29, 5, 17, 24, 54, -10, -14, -2, 29, 17, -4, -47, 56, 4, 9, 30, -87, 
39, -16, 39, 67, -13, 37, 13, 67, 50, -16, -55, 8, 24, -50, -1, -36, -51, -20, 
-58, 11, -28, -22, -26, 16, 7, -17, 39, -9, -21, -9, -8, -18, 37, -47, -19, 36
```

--------------------------------

### Connect to Oracle Database as SYSDBA

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/convert-text-chunks-custom-chunking-specifications

Connects to the Oracle Database as the SYS user with SYSDBA privileges. This is a prerequisite for setting up the database for the examples.

```sql
conn sys/password as sysdba
```

--------------------------------

### SQL: Explain Plan for Similarity Search

Source: https://docs.oracle.com/en/database/oracle/oracle-database/23/vecse/sql-quick-start-using-float32-vector-generator

Generates an explain plan for a similarity search query that uses a pre-defined query vector. It first sets the `EXPLAIN PLAN FOR` command and then queries the `plan_table` to display the execution plan. This is useful for understanding query performance and potential optimizations.

```sql
EXPLAIN PLAN FOR
  SELECT name
  FROM genvec
  ORDER BY VECTOR_DISTANCE(v, :query_vector, EUCLIDEAN)
  FETCH EXACT FIRST 20 ROWS ONLY;

SELECT plan_table_output
  FROM table(dbms_xplan.display('plan_table',null,'all'));
```