Partie 2: Oracle APEX avec Ollama

Dans cette seconde partie nous explorons l'utilisation du service Ollama dans Oracle APEX en développant une page qui accepte en entrée une requête en langage naturel et qui la traduit vers une requête SQL pour afficher le résultat de celle-ci dans un tableau.

NL2SQL

La conversion du langage naturel vers SQL suivi de l'affichage du résultat présente deux challenges techniques principaux:

1. Le choix du modèle 

La contrainte du matériel, dans mon cas un ordinateur qui n'est pas équipé d'une carte graphique NVidia et avec 16Go de mémoire vive, impose le choix d'un modèle adapté à celui-ci, performant mais qui retourne un résultat plutôt fiable.

Après quelques tests sur le modèle initialement installé dans le conteneur Ollama (sqlcoder), il s'avère que les résultats et la performance de celui-ci ne répondent pas au besoin du cas d'utilisation.

2. L'affichage d'un tableau dynamique

Le résultat de la requête retournera un nombre indéfini de colonnes.  Plusieurs approches ici sont possibles, mais pour rester dans l'esprit APEX, on utilisera simplement un Interactive Report en faisant quelques ajustements.

qwen2.5-coder:7b

Compte-tenu des contraintes, nous installons et configurons le modèle qwen-coder qui s'avère plus approprié pour cet exemple sur mon matériel.  

Via un shell:

> docker exec -it ollama /bin/bash
> ollama pull qwen2.5-coder:7b

 Et on peut aussi profiter pour changer la dernière ligne du script run-ollama.sh 

#!/bin/bash

echo "Starting Ollama..."
ollama serve &

echo "Waiting for Ollama to be active..."
while [ "$(ollama list | grep 'NAME')" == "" ]; do
  sleep 1
done

ollama pull qwen2.5-coder:7b

Ne pas oublier de mettre à jour App Builder > Workspace Utilities > Generative AI Services > AI Model pour pointer sur le nouveau modèle: qwen2.5-coder:7b

Le prompt

La construction du prompt à envoyer au LLM se fait en deux temps.  Premièrement on construit le System Prompt qui aura pour objectif de définir la tâche du LLM, de lui fournir le modèle de données comme contexte avec lequel générer la requête SQL, mais aussi préciser les éléments à exclure de sa réponse tels que le raisonnement, mise en forme ou les commentaires.

Dans un processus Before Header sur la page APEX on utilise le code PL/SQL suivant pour générer le System Prompt et le stocker dans l'item P1_SYSTEM_PROMPT.

declare 
    l_system_prompt                 clob;
    l_owner                         varchar2(100) := 'DEFAULT_SCHEMA';
    l_ddl                           clob;
    l_relationships_comment         clob;
begin   
    :P1_QUERY_OK_YN := 'N';
    
    l_system_prompt := 
    '
    Your task is to convert a question into an SQL SELECT query.
    This query will run on the following database tables:
    {{sql_ddl_statement}}    
    DO NOT GENERATE REASONING. 
    DO NOT FORMAT CODE.  
    DO NOT COMMENT THE CODE.   
    ';
 
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS', true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'EMIT_SCHEMA', false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
    
    for t in (
        select
            ata.table_name,
            dbms_metadata.get_ddl('TABLE', ata.table_name, l_owner) as create_statement
        from 
            all_tables ata
            left join user_tab_comments utc on utc.table_name = ata.table_name
        where
            ata.owner = l_owner
    )
    loop
        l_ddl := l_ddl || t.create_statement;        
    end loop;

    :P1_SYSTEM_PROMPT := replace(l_system_prompt, '{{sql_ddl_statement}}', l_ddl);
end;

Invocation du modèle

On construit la page suivante

P1_QUESTION est le champs de saisie qui accepte la requête en langage naturel de l'utilisateur et le bouton GENERATE_SQL utilise une Dynamic Action: Generate Text With AI avec pour paramètres le system prompt, la question, le modèle et le paramètre de sortie pour la réponse qui sera stockée dans P1_GENERATED_SQL.

Page APEX

Interprétation des résultats

Le LLM retourne la requête SQL générée dans P1_GENERATE_SQL.

Créer une Dynamic Action: Change sur cet item pour analyser et lancer la requête via un Execute Server-side Code dans lequel on mettra le code suivant:

declare
    l_context                   apex_exec.t_context;
    l_columns                   apex_exec.t_columns;
    l_column                    apex_exec.t_column;
begin
    if :P1_GENERATED_SQL is null then
        return;
    end if;

    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name       => 'SEARCH_RESULTS_COLL',
        p_query                 => :P1_GENERATED_SQL,
        p_truncate_if_exists    => 'YES'
    );

    l_context := apex_exec.open_query_context(p_location => apex_exec.c_location_local_db, p_sql_query => :P1_GENERATED_SQL);
    l_columns := apex_exec.get_columns(p_context => l_context);
    :P1_QUERY_COLUMNS_NAMES := '';
    :P1_QUERY_COLUMNS_COUNT := apex_exec.get_column_count(p_context => l_context);
    
    for i in 1..apex_exec.get_column_count(p_context => l_context)
    loop
        l_column := apex_exec.get_column (p_context => l_context, p_column_idx => i);
        :P1_QUERY_COLUMNS_NAMES := :P1_QUERY_COLUMNS_NAMES || l_column.name || ',';
    end loop;
   
    :P1_QUERY_COLUMNS_NAMES := substr(:P1_QUERY_COLUMNS_NAMES, 1, length(:P1_QUERY_COLUMNS_NAMES) - 1);
    :P1_QUERY_OK_YN := 'Y';
    exception when others then
        --raise_application_error(-20001, sqlerrm);
        :P1_QUERY_OK_YN := 'N';
end;

Les données du résultat de la requête SQL sont mises dans une collection et afin de cacher les colonnes vides et renommer celles pertinentes, on garde le nombre total de colonnes dans P1_QUERY_COLUMNS_COUNT et leurs noms dans P1_QUERY_COLUMNS_NAMES.

Une Dynamic Action: Change sur P1_QUERY_OK_YN lorsque l'item prend la valeur 'Y' déclenchera l'affichage de la région Interactive Report.

 

Cette requête va retourner toutes les colonnes de APEX_COLLECTION, donc pour n'afficher que celles pertinentes à la requête SQL générée, on peut utiliser des Server-side Condition sur chacune des colonnes.  Par exemple sur la colonne C002 une expression PL/SQL: P1_QUERY_COLUMNS_COUNT > 1 sur la colonne suivante C003 une expression PL/SQL:P1_QUERY_COLUMNS_COUNT > 2 etc.  

Bien entendu on pourrait aussi définir une autre règle pour afficher ou cacher les colonnes.

Quant au renommage des colonnes le javascript sur le Dynamic Action: After Refresh de l'Interactive Report peut faire l'affaire.

const columns = $v('P1_QUERY_COLUMNS_NAMES').split(',');
const htmlTableId = $('#IR_RESULTS .a-IRR-table').attr('id');
$('a.a-IRR-headerLink').each( function(i, v) {
    $(this).text(columns[i]);
});

Conclusion

Vous pouvez télécharger l'exemple ici sous forme d'application APEX exportée.

Forcément le renommage dynamique des colonnes de l'Interactive Report ne se réalise que sur l'affichage du tableau et lors de manipulations des colonnes dans la barre d'outils, l'utilisateur verra le nom générique des colonnes C001, C002 etc.

Partie 1: Oracle APEX avec Ollama

La dernière version de Oracle APEX (24.2.x) supporte officiellement de son interface de configuration les fournisseurs de services d'intelligence artificielle: OCI Generative Service, Open AI et Cohere.  Toutefois il est théoriquement possible d'utiliser Ollama dans APEX car cette technologie implémente l'API Open AI, mais quand est-il de la pratique?

Motivation

Avec la montée en puissance de l'utilisation des LLM (grands modèles de langage), le problème de sécurité et de confidentialité de données devient un facteur critique à prendre en considération.  Grâce à Ollama c'est possible d'avoir ses propres services d'I.A sur son infrastructure et éventuellement utiliser des modèles plus adaptés et spécialisés selon le cas d'utilisation et l'environnement.

Mise en place

Comme environnement de base pour configurer et tester ces services, j'utiliserai le conteneur Docker de Pretius pour l'installation d'une base de données Oracle, ORDS &  APEX.

00_start_apex_ords_installer.sh

Le script suivant lancera l'installation de Pretius mais on y rajoute l'exécution d'un autre script qui donnera à la base l'autorisation d'accéder au service Ollama.

sh /home/oracle/unattended_apex_install_23c.sh > /home/oracle/unattended_apex_install_23c.log
sh /home/oracle/ollama-acl.sh

ollama-acl.sh

Sur la version gratuite de la base, l'ouverture au port 11434 est faite de la manière suivante:

#!/bin/bash

echo "Enable access to Ollama port..."
sqlplus / as sysdba <<EOF
ALTER SESSION SET CONTAINER = FREEPDB1;

BEGIN
  BEGIN
    DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
      acl => 'ollama_acl',
      description => 'ACL for Ollama API access',
      principal => APEX_APPLICATION.G_FLOW_SCHEMA_OWNER,
      is_grant => TRUE,
      privilege => 'connect'
    );
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -31001 THEN -- ACL already exists
        RAISE;
      END IF;
  END;

  -- Assign ACL to host
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl => 'ollama_acl',
    host => '172.17.0.1',
    lower_port => 11434,
    upper_port => 11434
  );

  COMMIT;
END;
/
EOF

oracle-ords-apex.dockerfile

#  Oracle 23c Free Image
FROM container-registry.oracle.com/database/free:latest

# Mot de passe
ENV ORACLE_PWD=E

COPY ./ollama-acl.sh /home/oracle/ollama-acl.sh
COPY ./00_start_apex_ords_installer.sh /home/oracle/00_start_apex_ords_installer.sh

USER oracle
WORKDIR /home/oracle

# Télécharger les scripts d'installation ORDS & APEX de Pretius: https://pretius.com/blog/oracle-apex-docker-ords/
RUN curl -o unattended_apex_install_23c.sh https://raw.githubusercontent.com/Pretius/pretius-23cfree-unattended-apex-installer/main/src/unattended_apex_install_23c.sh
RUN chmod +x unattended_apex_install_23c.sh
RUN mv 00_start_apex_ords_installer.sh /opt/oracle/scripts/startup/
EXPOSE 1521 8080
CMD ["/bin/bash", "-c", "/opt/oracle/runOracle.sh"]

A ces services je rajoute l'image Docker de Ollama et le script suivant pour charger le modèle sqlcoder dans l'image. Dans une autre partie on verra comment exploiter ce modèle dans APEX.

run-ollama.sh

#!/bin/bash

echo "Starting Ollama..."
ollama serve &

echo "Waiting for Ollama to be active..."
while [ "$(ollama list | grep 'NAME')" == "" ]; do
  sleep 1
done

ollama pull sqlcoder:7b

ollama.dockerfile

FROM ollama/ollama

COPY ./run-ollama.sh /tmp/run-ollama.sh

WORKDIR /tmp

RUN chmod +x run-ollama.sh \
    && ./run-ollama.sh

EXPOSE 11434

Finalement on regroupe tous les services dans un fichier docker compose.

docker-compose.yml

services:
  oracle:
    build:
      context: ./
      dockerfile: oracle-ords-apex.dockerfile
    container_name: 23cfree
    environment:
      ORACLE_PWD: "E"
    ports:
      - "1521:1521"
      - "8080:8080"
    networks:
      - my-network
    volumes:
      - oracle_data:/opt/oracle/oradata
    restart: unless-stopped

  ollama:
    build:
      context: ./
      dockerfile: ollama.dockerfile
    ports:
      - 11434:11434
    volumes:
      - ollama:/root/.ollama
    container_name: ollama
    pull_policy: always
    tty: true
    restart: always
    environment:
      - OLLAMA_KEEP_ALIVE=24h
      - OLLAMA_HOST=0.0.0.0
    networks:
      - my-network 

networks:
  my-network:

volumes:
  oracle_data:
  ollama:

 

On peut lancer les services avec la commande.

> docker-compose up
 

la base, ORDS et ollama seront installés et leurs services lancés.

Ollama dans APEX

Par la suite il vous faudra configurer APEX et rajouter un utilisateur via l'URL

http://127.0.0.1:8080/apex/apex_admin et 

en utilisant les accès par défaut ADMIN / OrclAPEX1999!

Une fois identifié comme un utilisateur ordinaire dans App Builder > Workspace Utilities > Generative AI Services on peut référencer le service de Ollama et voir si ça fonctionne:

Base URL: http://172.17.0.1:11434/v1 

API Key: 123 (entrer n'importe quoi)

AI Model: sqlcoder:7b

et cliquer sur bouton Test Connection

 

Conclusion

L'installation de Ollama dans cet exemple se fait sur un ordinateur qui n'est pas équipé d'une carte NVidia.  Il faudra suivre la procédure d'installation documentée sur Docker Hub si vous avez une carte NVidia.

 

Dans la prochaine partie sera démontré comment exploiter le modèle sqlcoder.

L'injection de dépendances avec Golang

Lorsqu'on développe un service Web et/ou REST API en Go on adopte souvent et à juste titre une architecture logicielle Modèle-Vue-Contrôleur. Le découplage voulu par cette approche pose un dilemme quant à la gestion des dépendances entre les modules.

Un exemple typique est le cas où il est nécessaire de créer qu'une seule instance du module qui gère la connexion et les interactions avec la base de données.  C'est alors que l'injection de cette dépendance peut être utile afin d'éviter d'avoir recours à une variable globale.

Illustration de packages pour une application Web.

 

Dans cet exemple d'architecture, le package main est le point d'entrée de l'application.  Ce package écoute sur un port des requêtes et dirige celles-ci vers leurs fonctions de traitement appropriées qui sont définies dans le package handlers.

 

Le package handlers gère la logique métier en ayant recours à son tour aux fonctions interagissants avec la base de données (package db) et en s'occupe du rendu de la réponse grâce aux fonctions définies dans le package components.

Le package middleware quant à lui permet de traiter par exemple l'authentification et les logs côté serveur.

Injection de dépendance.

package main

import (
"go-website/db"
"go-website/handlers"
"go-website/middleware"
"log"
"net/http"
"os"

"github.com/joho/godotenv"
)

func main() {
// reading environment variables
err := godotenv.Load()
if err != nil {
      log.Fatal("Error loading .env file")
}
port := os.Getenv("PORT")
dbFilePath := os.Getenv("DB_FILE_PATH")

appDb := db.AppDatabase{}
err = appDb.Open(dbFilePath)
if err != nil {
      log.Fatalf("Error opening database %s", err.Error())
      return
}

appHandlers := handlers.AppHandler{DB: &appDb}

router := http.NewServeMux()
// more routing code here…

middlewareChain := middleware.MiddlewareChain(middleware.AuthMiddleware, middleware.RequestLoggerMiddleWare)
server := http.Server{
    Addr: ":" + port,
    Handler: middlewareChain(router),
}
log.Printf("Listening on port %s", port)
server.ListenAndServe()
}

Dans l'extrait ci-dessus, la structure AppDatabase du package db qui encapsule les fonctions relatives aux opérations sur la base de données est instanciée une fois et elle est ensuite injectée dans la structure AppHandler du package handlers.

Le package handlers peut alors utiliser les fonctions de AppDatabase sans à avoir à gérer la connexion à la base comme le démontre le code suivant:

package handlers import ( "go-website/components" "go-website/db" "go-website/session" "log" "net/http" ) type AppHandler struct { DB *db.AppDatabase } func (h *AppHandler) User(w http.ResponseWriter, r *http.Request) { username := r.FormValue("username") user, err := h.DB.GetUserByUserName(username) if err != nil {       http.Error(w, err.Error(), http.StatusInternalServerError)       return } name  := user.Name // extra code to handle logic components.UserTemplate().Render(r.Context(), w) } // more handler code.

Quant au package DB il pourrait être comme suit:

 

package db

import (
"database/sql"
"log"

_ "github.com/mattn/go-sqlite3"
)

type AppDatabase struct {
DB *sql.DB
}

type User struct {
Id     int        `json:"id"`
Username   string     `json:"username"`
Name  string     `json:"name"`
}

func (ab *AppDatabase) Open(dbFilePath string) (err error) {
ab.DB, err = sql.Open("sqlite3", dbFilePath)
if err != nil {
    log.Fatalf("Cannot open database. %s", err.Error())
    return err
}

return ab.DB.Ping()
}

func (ab *AppDatabase) Close() {
ab.DB.Close()
}

func (ab *AppDatabase) GetUserByUserName(username string) (user User, err error) {
u := User{}
err = ab.DB.QueryRow("select id, username, name from users where username = ?", username).Scan(&u.Id, &u.Username, &u.Name)
if err != nil {
    log.Fatalf("GetUserByUserName QueryRow failed: %s", err.Error())
    return
}
return u, nil
}