Réalisation avancée d’ API avec Oracle REST Data Services

Par Emmanuel LESNEAPI Management

Oracle REST Data Services, ORDS, est un module bien pratique dans la riche collection des produits Oracle. Il permet d’exposer par API le contenu d’une base de données Oracle. Ceci rapidement, simplement et gratuitement. Combiné avec Oracle APEX, la production de services API est accessible aux sachant SQL.

Je vous partage ici les bases pour réaliser un service API plus adapté à la réalité, avec des filtres et une gestion d’erreurs.

Prise en main

La prise en main du produit ORDS est bien documenté. Vous trouverez de nombreux articles pour l’installer ou le documenter.

Installation:

Premier pas:

Présentation des fonctionnalités:

Réaliser un service API de base

Exposer des données par API en quelques clics est assez simple à réaliser par défaut. Le service ORDS permet de produire un résultat API de qualité à partir d’une simple requête SQL.

Exemple d’une recherche d’employés

Résultat attendu

Pour accéder aux données, nous désirons un simple appel HTTP.

GET http://localhost:8181/ords/ordstest/Employees/1/employees

Le résultat est un contenu JSON avec la description de la donnée: id, nom et embauche

{
  "items": [
    {
      "id": 1111,
      "nom": "Emmanuel",
      "embauche": null
    },
    {
      "id": 7369,
      "nom": "SMITH",
      "embauche": "1980-12-17T00:00:00Z"
    },
  ...
  ],
  ...
}

Configuration du service

Pour cela, avec APEX / ORDS, il est nécessaire de définir:

  • un module: GestionDesEmployes1,
  • un template: employees,
  • un handler: GET.

Pour la création de ces éléments, se référer aux liens ci-dessus. Le handler, ou service est alors le suivant:

Source du service

select  
    e.empno id,
    e.ename nom,
    e.hiredate embauche
from emp e
where e.job = :v_job

Résultat

Le résultat du service API est conforme, bien que nous soyons contraint par la gestion des collections par ORDS (element items, métadonnées, etc).

Réalisation avancée du service

Toutefois, lorsque l’on souhaite adapter la réponse en fonction de paramètres, ce service SQL n’est pas suffisant.

Par exemple nous souhaitons ajouter des filtres job et departement sur la recherche des employés.

La requête HTTP devra être:

GET http://localhost:8181/ords/ordstest/Employees/1/employees
    ?departement=30&job=CLERK

Pour cela, il convient de travailler la source avec:

  • les codes de retour HTTP,
  • les variables implicites,
  • les variables en entrée et en sorties,
  • une requête SQL dynamique.

Mise en oeuvre

Source en PLSQL

La configuration est réduite à spécifier le type de source à PL/SQL au niveau du service.

Le code devra alors être un bloc PL/SQL classique:

DECLARE
  -- variables
BEGIN
  -- code
END;

Les codes retours HTTP

Pour adapter un code HTTP spécifique, la variable implicite :status_code sera mise à profit.

:status_code := 202;

Les variables en entrées

Pour enrichir l’appel du service avec 2 nouveaux paramètres, il convient de les ajouter dans le bloc paramètres du service:

  • Access method: IN
  • Source Type: URI

Les variables sont utilisables dans le bloc PLSQL, par le nom “bindé” et avec un “:” en prefix.

Exemple pour la variable job:

:v_job

Les variables en sortie

Pour réaliser une sortie dans le corps JSON de la réponse, il est impératif de déclarer des variables OUT.

Ces variables seront générées, si et seulement si, elles ne sont pas nulles.

Dans notre exemple, 2 variables en sortie sont déclarées:

  • data: en type RESULTSET pour la liste des données trouvées,
  • error: en type STRING pour le détail d’une erreur, si jamais.

Une sortie sera alors composée, de ces 2 éléments de base, et UNIQUEMENT celles-ci. Exemple:

{
  "data": [],
  "error": ""
}

PS: Dans les fait, ce sera, soit data, soit error.

Gestion des exceptions PL/SQL

Le type de source est un bloc PL/SQL. Il convient de gérer les exceptions classiquement. Les exceptions standard sont connues. Les exceptions personnalisées (ex. parametre_obligatoire) sont à déclarer.

En cas d’erreur, le code HTTP est adapté (ici 404, 406 ou 500), et la description de l’erreur enrichie.

DECLARE
    parametre_obligatoire EXCEPTION;
BEGIN
    -- ...
    :status_code := 202;
    
 
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            :status_code := 404;
        WHEN parametre_obligatoire
        THEN
            :v_error := 'Parametre obligatoire manquant.';
            :status_code := 406;
        WHEN OTHERS
        THEN
            :v_error := 'Erreur interne: ' || SQLERRM;
            :status_code := 500;
END;

Construction dynamique de la requête SQL

Une fois que tout cela est prêt. Il reste la requête SQL à réaliser. Elle est adaptée en fonction des paramètres HTTP d’entrée: job et departement. En fait, leur représentation “bindées”, v_job et v_departement.

Le 1er bloc les teste et refuse un paramètre obligatoire, si besoin.

    IF :v_job IS NULL
    THEN
        RAISE parametre_obligatoire;
    END IF;

Puis la requête est construite par morceau pour être parcourue avec un curseur DB.

-- initialisation des bloc
    sql_select := 'e.empno emp_id, e.ename nom, e.hiredate embauche, e.deptno departement, e.job job';
    sql_from := 'emp e';
    sql_where := 'e.empno = e.empno';

-- test des paramètres obligatoires
    IF :v_job IS NULL
    THEN
        RAISE parametre_obligatoire;
    END IF;

-- ajout des contraintes sur la requete SQL 
    IF :v_job IS NOT NULL
    THEN
        sql_where := sql_where || ' and e.job = ''' || :v_job || ''' ';
    END IF;

    IF :v_departement IS NOT NULL
    THEN
        sql_where := sql_where || ' and e.deptno = ''' || :v_departement || ''' ';
    END IF;

-- execution via un curseur.
    OPEN :v_response FOR
      'select ' || sql_select ||
      ' from ' || sql_from ||
      ' where ' || sql_where ;

Le curseur :v_response permet de parcourir les lignes correspondantes, ie. la variable de sortie en RESULTSET !

Résultat final

L’appel HTTP du service est conforme:

GET http://localhost:8181/ords/ordstest/Employees/1/employees
    ?departement=30
    &job=CLERK

Le résultat prend en compte les paramètres en requête HTTP: 

HTTP/1.1 202 Accepted
Connection: close
Content-Type: application/json
X-ORDS_DEBUG: false
ETag: "EnHPeWn8GDsFK/eLjRG0MKlrwyglCJpVmhVvVJKrruW2u9nMN6nI8D0fJuj4iOzr1XPSPpOE6BQhZnlLyaLVFA=="

{
  "data": [
    {
      "emp_id": 7900,
      "nom": "JAMES",
      "embauche": "1981-12-03T00:00:00Z",
      "departement": 30,
      "job": "CLERK"
    }
  ]
}

Une erreur est remontée si le paramètre job est absent:

HTTP/1.1 406 Not Acceptable
Connection: close
Content-Type: application/json
X-ORDS_DEBUG: false
ETag: "AoXCEWpRkdQ/cfDvzbqux8+ofBLutubsaAqnBsA59yqDlhVd3XePxOJLg69XavzgBzNnQIZLeBfOqJtIOBrp3g=="

{
  "error": "Parametre obligatoire manquant."
}

Le bloc de code complet PL/SQL est alors:

DECLARE
    parametre_obligatoire EXCEPTION;

    sql_select VARCHAR2(256);
    sql_from VARCHAR2(256);
    sql_where VARCHAR2(256);
BEGIN
    sql_select := 'e.empno emp_id, e.ename nom, e.hiredate embauche, e.deptno departement, e.job job';
    sql_from := 'emp e';
    sql_where := 'e.empno = e.empno';

    IF :v_job IS NULL
    THEN
        RAISE parametre_obligatoire;
    END IF;

    IF :v_job IS NOT NULL
    THEN
        sql_where := sql_where || ' and e.job = ''' || :v_job || ''' ';
    END IF;

    IF :v_departement IS NOT NULL
    THEN
        sql_where := sql_where || ' and e.deptno = ''' || :v_departement || ''' ';
    END IF;


    OPEN :v_response FOR
      'select ' || sql_select ||
      ' from ' || sql_from ||
      ' where ' || sql_where ;

    :status_code := 202;
    
 
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            :status_code := 404;
        WHEN parametre_obligatoire
        THEN
            :v_error := 'Parametre obligatoire manquant.';
            :status_code := 406;
        WHEN OTHERS
        THEN
            :v_error := 'Erreur interne: ' || SQLERRM;
            :status_code := 500;
END;

Puis dans APEX / ORDS, le contenu est plus agréable 😉 :

Laisser un commentaire