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:
- Combiné APEX et ORDS pour bénéficier d’une interface Web.
- Intégrée à Tomcat, pour une installation native.
- Le tout basé sur Docker, pour un usage standard.
Premier pas:
- Tutoriel Oracle sur un 1er service.
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 😉 :