Renvoi de résultats par procédure stockée Oracle vers MSSQL (Linked Server)

Logo

Introduction

Contexte

Dans un serveur MSSQL, il est possible de définir et interroger des serveurs SGBD distants, serveurs distants pouvant être hétérogènes (Sybase, Oracle, DB2 etc…).

La commande T-SQL openquery de MSSQL permet d’interroger les tables du serveur distant défini par LINKED_SERVER :

select * from
     openquery(LINKED_SERVER, 'select * from TABLE where COL=VALUE')

Dans le contexte de cette documentation, le serveur distant que l’on souhaite interroger est un serveur Oracle DBA_T1_ORA (cf schéma ci-dessous) :

Link Oracle MS SQL

La syntaxe openquery présente quelques inconvénients avec Oracle :

  • il est impossible de mettre en œuvre des variables bindées Oracle dans les requêtes passées, ce qui impose à Oracle de parser chaque requête envoyée par MSSQL. Dans des boucles récursives pour chaque valeur COL=VALUE dans la requête SELECT, chaque requête sera alors parsée par Oracle, ce qui dégrade les performances notamment au niveau de la SGA du serveur Oracle. Pour éviter qu’Oracle ne reparse des requêtes identiques, la syntaxe SELECT * from TABLE where COL = :var est la plus optimale grâce à l’utilisation de variables bindées.
  • Si les requêtes s’avèrent complexes, l’ouverture de droits sur de nombreuses tables de l’instance Oracle est nécessaire, ouverture de droits qui n’est pas forcément souhaitée.

Pour les 2 raisons ci-dessus, on peut souhaiter qu’une procédure stockée Oracle soit exécutée pour renvoyer le jeu de résultats au serveur MSSQL, ainsi seul un droit est donné sur l’exécution de la procédure stockée, un périmètre peut être donné dans la procédure stockée même et ainsi éviter les boucles récursives et cela soulage la SGA car le plan d’exécution n’est parsé alors qu’une fois.

Toutefois le renvoi de jeux de résultats par une procédure stockée n’est pas dans la philosophie Oracle mais cela existe notamment avec les refcursors (voir documentation Oracle - Retour de result Sets par procédures stockées), refcursors qui malheureusement ne sont pas utilisables dans la commande OPENQUERY.

Une autre méthode existe avec la technique des TABLE OF (collections Oracle) dans des packages Oracle, technique qui permet le renvoi de jeux de résultats dans la commande OPENQUERY depuis MSSQL. Cette technique est décrite dans cette documentation.

Exemple pratique de la documentation

Dans l’exemple pratique, depuis MSSQL, les colonnes EmpNo et EName de la table Oracle SCOTT.EMP sont récupérées et le résultat est filtré sur la colonne Ename pour ne spécifier que les employés dont le nom commence par une lettre qui sera donnée en variable :

Select EmpNo, Ename from SCOTT.EMP where Ename like 'J%';

Syntaxe OPENQUERY

La syntaxe générale pour appeler depuis une commande T-SQL OPENQUERY une procédure stockée Oracle d’un package, procédure stockée acceptant n paramètres (parm1, parm2, parm3…) et renvoyant j colonnes ( col1, col2, col3…) est la suivante :

SELECT * FROM OPENQUERY( <LinkedServerName> ,
 '{Call <owner>.<package>.<stored_procedure>( [parm1 [,parm2...]] ,
 {resultset n, col1 [,col2...] } ) } ' )

Le paramètre resultset n permet de filtrer le nombre de lignes maximal à recevoir :

  • Lorsque resultset vaut 0, toutes les lignes renvoyées par l’instance Oracle sont récupérées.
  • Lorsque resultset vaut 25 par exemple, seules les 25 premières lignes renvoyées par l’instance Oracle sont récupérées.

Dans l’exemple pratique de cette documentation, la procédure stockée WrapPackSP du package WrapPack appartenant au schéma SCOTT est appelée. Cette procédure stockée n’accepte qu’un seul paramètre (le filtre pour le nom de l’employé) et renvoie deux colonnes EID et EName. Voici comment la procédure stockée WrapPackSP peut être appelée avec la commande T-SQL OPENQUERY

SELECT * FROM OPENQUERY( LKORA ,
  '{Call Scott.WrapPack.WrapPackSP( "J%", {resultset 20, EID,EName})}' )

Mise en œuvre du package Oracle

Définition des colonnes renvoyées avec des TABLE OF dans le package Oracle

Dans un package Oracle sont définis les procédures stockées, fonctions et variables (curseurs, collections, etc.).

Pour notre cas pratique, dans le package Oracle sont définies les collections (TABLE OF), collections qui seront les colonnes retournées au serveur MSSQL. A toutes ces collections, il est obligatoire d’associer un index by binary_integer pour l’indexation, sinon la méthode ne fonctionne pas.

La colonne EmpID étant de type number et la colonne Ename étant de type varchar2(30), les collections dans le package sont alors définies par exemple de la manière suivante dans le package PL/SQL WrapPack :

TYPE EmpID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE EmpName IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

D’une manière générale, les collections à définir (colonnes à retourner) doivent être déclarées ainsi dans le package :

TYPE <collection_name> IS TABLE OF <element_type> INDEX BY BINARY_INTEGER;

Le prototype de la procédure stockée WrapPackSP doit être également défini dans le package WrapPack, et d’une manière générale, outre les paramètres IN nécessaires à la procédure stockée, les collections retournées doivent impérativement être définies en paramètre OUT de la procédure stockée :

PROCEDURE <stored_procedure_name>
(
        parm1 IN <type> [, parm2 IN <type>..., ]
        col1 OUT <collection_name1> [,col2 OUT <collection_name2>...,]
);

col1 et col2,… seront les noms des colonnes retournées dans l’appel de la commande T-SQL OPENQUERY.

Voici le code source du package WrapPack pour les besoins de l’application pratique.

CREATE OR REPLACE PACKAGE WrapPack
AS
        TYPE EmpID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
        TYPE EmpName IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
        PROCEDURE WrapPackSP
        (
                NameLike IN varchar2,
                EID OUT EmpID,
                EName OUT EmpName
        );
END WrapPack;
/

À noter que pour s’affranchir de l’écriture de la syntaxe TABLE OF VARCHAR2(30), il est également possible d’écrire TABLE OF scott.emp.Ename%TYPE.

Alimentation des collections

Dans le corps du package WrapPack, la procédure stockée WrapPackSP a alors pour simple objectif de remplir les collections à renvoyer au serveur MSSQL.

Un curseur est généralement ouvert sur les données à renvoyer et les collections sont remplies en bouclant sur les lignes du curseur.

CREATE OR REPLACE PACKAGE BODY WrapPack
AS
PROCEDURE WrapPackSP
(
        NameLike IN varchar2,
        EID OUT EmpID,
        EName OUT EmpName
)
IS
        EmpCount NUMBER DEFAULT 1;
        CURSOR EmpCur IS
        SELECT EmpNo , EName FROM SCOTT.EMP
        WHERE EName LIKE NameLike;

BEGIN
     FOR Emp IN EmpCur
     LOOP
          EID( EmpCount ) := Emp.EmpNo;
          EName( EmpCount ) := Emp.EName;
          EmpCount := EmpCount + 1;
     END LOOP;
END WrapPackSP;
END WrapPack;
/