Appeler des procédures externes en tant que fonctions SQL

Affiche les étapes permettant d'appeler des procédures externes à l'aide de PL/SQL au sein de la base de données.

Présentation des procédures externes

Les procédures externes sont des fonctions écrites dans un langage de troisième génération et pouvant être appelées à partir de code PL/SQL ou SQL comme s'il s'agissait d'une routine ou d'une fonction PL/SQL.

Les procédures externes favorisent la réutilisabilité, l'efficacité et la modularité. Les bibliothèques de liens dynamiques (DLL) existantes écrites dans d'autres langages peuvent être appelées à partir de programmes PL/SQL. Les DLL ne sont chargées qu'en cas de besoin et peuvent être améliorées sans affecter les programmes appelants.

L'utilisation de procédures externes améliore également les performances, car les langages de troisième génération exécutent certaines tâches plus efficacement que le langage PL/SQL, qui est mieux adapté au traitement des transactions SQL.

Les procédures externes sont utiles lorsque :

  • Résoudre les problèmes scientifiques et techniques

  • Analyse des données

  • Contrôle des appareils et des processus en temps réel

Pour plus d'informations, voir Qu'est-ce qu'une procédure externe ?.

A propos de l'utilisation de procédures externes dans Autonomous Database

Vous pouvez appeler et utiliser des procédures externes dans votre instance Autonomous Database avec des fonctions définies par l'utilisateur.

Vous n'installez pas de procédures externes sur une instance Autonomous Database. Pour utiliser une procédure externe, la procédure est hébergée à distance sur une machine virtuelle exécutée sur un réseau cloud virtuel Oracle Cloud Infrastructure (VCN).

Les procédures externes ne sont prises en charge que lorsque votre instance Autonomous Database se trouve sur une adresse privée. L'instance d'agent EXTPROC est hébergée sur un sous-réseau privé et Autonomous Database accède à l'agent EXTPROC via une adresse de connexion inverse (RCE).

Remarque

Autonomous Database prend uniquement en charge les procédures externes en langage C.

Les procédures externes sont déployées à l'aide des éléments suivants :

  • Image de conteneur fournie par Oracle avec l'agent EXTPROC installé et configuré dans le cadre de la pile Oracle Cloud Infrastructure (OCI) Marketplace.

    L'instance d'agent EXTPROC est hébergée à distance sur une machine virtuelle exécutée sur un réseau cloud virtuel Oracle Cloud Infrastructure (VCN). La communication sécurisée entre votre instance Autonomous Database et l'instance d'agent EXTPROC est assurée par la définition de règles de groupe de sécurité réseau (NSG) de sorte que le trafic soit autorisé de l'instance Autonomous Database exécutée sur une adresse privée vers l'instance d'agent EXTPROC.

    L'image de l'agent EXTPROC est préconfigurée pour héberger et exécuter des procédures externes sur le port 16000.

  • Procédures PL/SQL permettant de créer une bibliothèque et d'enregistrer et d'appeler des fonctions et procédures externes.

    Pour plus d'informations, reportez-vous à la section DBMS_CLOUD_FUNCTION Package.

Pour appeler une procédure externe sur Autonomous Database, procédez comme suit :

Définition de la procédure C

Définissez la procédure C en utilisant l'un de ces prototypes.

  • Prototypes de style Kernighan & Ritchie. Par exemple :

    void UpdateSalary(x)
     float x;
    ...
    
  • prototypes ISO/ANSI autres que les types de données numériques dont la largeur est inférieure à la largeur totale (tels que float, short, char). Par exemple :

    void UpdateSalary(double x)
    ...
    
  • Autres types de données qui ne changent pas de taille sous les promotions par argument par défaut.

    Cet exemple modifie la taille sous Promotions de l'argument par défaut :

    void UpdateSalary(float x)
    ...

Création d'un fichier de bibliothèque partagée (.so)

Créez une bibliothèque d'objets partagés (fichier .so). La bibliothèque d'objets partagés contient la procédure C (procédure externe) qui a été définie à l'étape précédente.

Vous générez une bibliothèque d'objets partagée à l'aide de la commande suivante :

gcc -I/u01/app/oracle/extproc_libs/ -shared -fPIC -o extproc.so UpdateSalary.c

Cela crée l'objet partagé (.so), bibliothèque extproc.so. La procédure UpdateSalary, définie à l'étape précédente, figure dans la bibliothèque extproc.so. Les bibliothèques d'objets partagés (.so) sont chargées dynamiquement lors de l'exécution.

Obtenir l'application OCI Marketplace EXTPROC Stack

Présente les étapes d'obtention de l'application de pile EXTPROC OCI Marketplace.

Procédez comme suit :
  1. Connectez-vous à la console OCI à l'adresse http://cloud.oracle.com. Reportez-vous à Connexion à la console Oracle Cloud Infrastructure pour plus d'informations.
  2. Dans le menu de navigation de gauche Oracle Cloud Infrastructure, cliquez sur Marketplace, puis, sous Marketplace, cliquez sur Toutes les applications. Vous accédez ainsi au tableau de bord Toutes les applications Marketplace.
  3. Entrez EXTPROC dans le champ de recherche et cliquez sur Rechercher.
  4. Cliquez sur le widget EXTPROC de type : pile.
Vous accédez alors à la page de détails de l'agent Oracle Autonomous Database EXTPROC.

Lancer l'application de pile EXTPROC

Lancez l'application de pile EXTPROC à partir de la page Détails de l'application EXTPROC.

  1. Sur la page Agent EXTPROC d'Oracle Autonomous Database, sous Type de pile, effectuez les opérations suivantes :
    • Dans la liste déroulante Version, sélectionnez la version de package de la pile. Par défaut, le menu affiche la dernière version.

    • Dans la liste déroulante Compartiment, sélectionnez le nom du compartiment dans lequel vous voulez lancer l'instance.

      Remarque

      Si vous ne disposez pas des droits nécessaires pour lancer l'instance dans le compartiment sélectionné, elle est lancée dans le compartiment racine.
    • Cochez la case J'ai pris connaissance des Conditions standard Oracle et des restrictions et je vous les accepte.

  2. Cliquez sur Lancer la pile.

Vous accédez ainsi à la page Créer une pile qui vous permet de créer une pile pour l'agent EXTPROC.

Créer une pile pour l'application d'agent EXTPROC

Présente les étapes de création de pile pour l'instance EXTPROC.

Dans l'assistant Créer une pile, procédez comme suit :
  1. Sur la page Informations sur la pile, vérifiez et modifiez les informations suivantes si nécessaire :
    • Informations sur la pile

    • Fournisseurs personnalisés

    • Nom (facultatif) : vous pouvez modifier le nom de pile par défaut. Evitez de saisir des informations confidentielles.

    • Description (facultatif) : vous pouvez modifier la description de pile par défaut. Evitez de saisir des informations confidentielles.

    • Créer dans le compartiment

    • Version de Terraform

    • Balises : indiquez les éléments suivants pour affecter des balises à la pile.

      • Espace de noms de balise : pour ajouter une balise définie, sélectionnez un espace de noms existant. Pour ajouter une balise free-from, laissez la valeur vide.

      • Clé de balise : pour ajouter une balise définie, sélectionnez une clé de balise existante. Pour ajouter une balise à format libre, saisissez le nom de clé souhaité.

      • Valeur de balise : saisissez la valeur de balise souhaitée.

      Ajouter une balise : cliquez sur cette option pour ajouter une autre balise.

      Pour plus d'informations sur le balisage, reportez-vous à Balises de ressources.

  2. Cliquez sur Suivant.
    Vous accédez ainsi à la page Configurer des variables, qui vous permet de configurer des variables pour les ressources d'infrastructure que la pile crée lorsque vous exécutez le travail d'application pour ce plan d'exécution.
  3. Sur la page Configurer les variables, entrez les informations dans les zones Configurer l'agent EXTPROC, Configuration réseau et Configuration de calcul.
    1. Fournissez des informations dans la zone Configurer l'agent EXTPROC.
      • Bibliothèques externes : indiquez la liste des bibliothèques, séparées par une virgule (,), que vous voulez autoriser à appeler à partir de votre instance Autonomous Database. Par exemple, extproc.so, extproc1.so.

        Une fois la pile créée, vous devez copier les bibliothèques dans le répertoire /u01/app/oracle/extproc_libs sur la machine virtuelle d'agent EXTPROC.

      • Mot de passe de portefeuille : indiquez le mot de passe de portefeuille.

        Le portefeuille et un certificat auto-signé sont générés pour l'authentification TLS mutuelle entre la machine virtuelle d'agent EXTPROC et Autonomous Database. Le portefeuille est créé dans le répertoire /u01/app/oracle/extproc_wallet.
        Remarque

        Une fois le portefeuille créé, le mot de passe du portefeuille ne peut pas être modifié.
    2. Fournissez des informations dans la zone Configuration réseau.
      • Compartiment : dans la liste déroulante, choisissez le compartiment dans lequel placer la configuration.

      • Stratégie réseau : choisissez l'une des options de la liste déroulante, Créer un VCN et un sous-réseau ou Utiliser un VCN et un sous-réseau existants.

        • Créer un VCN et un sous-réseau : choisissez cette option si aucune adresse privée n'est configurée pour votre instance Autonomous Database. Cela crée un VCN avec un sous-réseau public et privé préconfiguré avec des règles de sécurité.

          Si vous sélectionnez cette option, la page affiche également la liste déroulante Configuration Strategy :

          Choisissez Utiliser la configuration recommandée dans la liste déroulante Stratégie de configuration.

        • Utiliser le VCN et le sous-réseau existants : sélectionnez cette option pour créer l'agent EXTPROC à l'aide d'un VCN existant. L'instance d'agent EXTPROC est ainsi créée dans le sous-réseau fourni.

          Lorsque vous sélectionnez cette option, fournissez les informations suivantes pour le VCN et le sous-réseau existants :

          • Sous Réseau cloud virtuel, procédez comme suit :

            Dans la liste déroulante VCN existant, choisissez un VCN existant. Si le VCN spécifié n'existe pas, un nouveau VCN est créé.

          • Sous Sous-réseau EXTPROC :

            Dans la liste déroulante Sous-réseau existant, choisissez un sous-réseau existant.

            Lorsque vous choisissez d'utiliser un VCN et un sous-réseau existants, ajoutez une règle entrante pour le port 16000 de l'instance d'agent EXTPROC. Vous ajoutez également une règle sortante sur le sous-réseau public.

            Pour plus d'informations, reportez-vous à Configuration de l'accès réseau avec des adresses privées.

      • Type d'accès à l'agent EXTPROC : choisissez l'une des options suivantes dans la liste déroulante.

        • Accès sécurisé à partir de bases de données d'adresse privée ADB-S spécifiques dans votre VCN : choisissez cette option pour autoriser uniquement les adresses IP d'adresse privée spécifiées dans votre réseau cloud virtuel (VCN) à se connecter à votre agent EXTPROC.

          Lorsque cette option est sélectionnée, vous indiquez la liste des adresses IP d'adresse privée autorisées à l'étape suivante.

        • Accès sécurisé à partir de toutes les bases de données d'adresse privée ADB-S dans votre VCN : choisissez cette option pour autoriser toute adresse privée dans votre réseau cloud virtuel (VCN) à se connecter à votre agent EXTPROC.

      • Adresses IP d'adresse privée

        Fournissez la liste des adresses IP d'adresse privée séparées par une virgule (,) pour la variable Adresses IP d'adresse privée. Par exemple, 10.0.0.0, 10.0.0.1.

        Remarque

        Ce champ s'affiche uniquement lorsque vous sélectionnez Accès sécurisé à partir de bases de données d'adresse privée ADB-S spécifiques dans votre VCN pour le type d'accès d'agent EXTPROC.
    3. Fournissez les informations de configuration Compute.
      • Compartiment : sélectionnez le compartiment vers lequel créer la pile.

      • Forme : sélectionnez une forme en fonction des exigences de charge globale de l'instance d'agent EXTPROC. La forme détermine les ressources allouées à l'instance d'agent EXTPROC.

      • Nombre d'OCPU : choisissez le nombre d'OCPU à allouer à l'instance d'agent EXTPROC.

      • Taille de mémoire (Go) : choisissez la quantité de mémoire en gigaoctets (Go) à allouer à l'instance d'agent EXTPROC.

      • Ajouter des clés SSH : téléchargez une clé publique SSH ou collez-la. Sélectionnez l'une des options suivantes :
        • Choisir un fichier de clés SSH : téléchargez la partie de clé publique de votre paire. Accédez au fichier de clés à télécharger ou glissez-déplacez-le dans la zone.

        • Coller la clé SSH : collez la partie de la clé publique de la paire dans la zone.

  4. Cliquez sur Suivant.

    Vous accédez alors à la page Vérifier.

  5. Sur la page Vérifier, procédez comme suit :
    1. Vérifiez les variables de configuration.
    2. Cochez la case Exécuter l'application sous Exécuter l'application sur la pile créée ?
    3. Cliquez sur Créer.
    Remarque

    Cette zone n'affiche pas les variables qui ont des valeurs ou des variables par défaut que vous n'avez pas modifiées.

    Resource Manager exécute le travail d'application pour créer les ressources d'empilement en conséquence. Vous accédez à la page Détails du travail et l'état du travail est Accepté. Lorsque le travail d'application des modifications démarre, le statut passe à En cours.

    Remarque

    Les informations nécessaires pour vous connecter à l'instance créée dans le cadre de la pile sont disponibles dans l'onglet Informations sur l'application.

Télécharger le portefeuille pour créer une connexion sécurisée à l'instance d'agent EXTPROC

Un portefeuille auto-signé est créé dans le cadre de la création de l'application d'agent EXTPROC. Ce portefeuille vous permet d'accéder à l'instance d'agent Extrpoc.

Pour exécuter des procédures distantes sur l'instance d'agent EXTPROC, Autonomous Database et l'agent EXTPROC se connectent à l'aide de l'authentification par couche Transport Layer Security (mTLS). Lorsque le protocole mTLS (Mutual Transport Layer Security) est utilisé, les clients se connectent via une connexion de base de données TCP (Secure TCP) à l'aide du Protocole TLS 1.2 standard avec un certificat d'autorité de certificat client sécurisé. Pour plus d'informations, reportez-vous à A propos de la connexion à une instance Autonomous Database.
Remarque

Vous pouvez également obtenir et utiliser un certificat public émis par une autorité de certification.

Vous devez au préalable exporter le portefeuille vers Object Storage à partir du répertoire /u01/app/oracle/extproc_wallet sur la machine virtuelle où EXTPROC est exécuté.

Pour télécharger le portefeuille vers votre instance Autonomous Database, procédez comme suit :

  1. Importez le portefeuille cwallet.sso contenant les certificats de l'instance d'agent EXTPROC à partir d'Object Storage dans votre instance Autonomous Database. Pour le fichier de portefeuille, tenez compte des points suivants :
    • Le fichier de portefeuille, ainsi que l'ID utilisateur et le mot de passe de base de données, permettent d'accéder à l'instance d'agent EXTPROC. Stockez les fichiers de portefeuille dans un emplacement sécurisé et partagez-les uniquement avec les utilisateurs autorisés.

    • Ne renommez pas le fichier de portefeuille. Le fichier de portefeuille dans Object Storage doit être nommé cwallet.sso.

  2. Créez des informations d'identification pour accéder à Object Storage dans lequel stocker le fichier de portefeuille cwallet.sso. Pour plus d'informations sur les paramètres de nom utilisateur et de mot de passe des différents services Object Storage, reportez-vous à Procédure CREATE_CREDENTIAL.
    La création d'informations d'identification pour accéder à la banque d'objets Oracle Cloud Infrastructure n'est pas requise si vous activez les informations d'identification de principal de ressource. Pour plus d'informations, reportez-vous à A propos de l'utilisation du principal de ressource pour accéder à des ressources Oracle Cloud Infrastructure.
  3. Créez un répertoire sur Autonomous Database pour le fichier de portefeuille cwallet.sso.
    CREATE DIRECTORY wallet_dir AS 'directory_location';

    Pour plus d'informations sur la création de répertoires, reportez-vous à Création d'un répertoire dans Autonomous Database.

  4. Utilisez DBMS_CLOUD.GET_OBJECT pour télécharger vers le serveur le portefeuille. Par exemple :
    BEGIN
      DBMS_CLOUD.GET_OBJECT (
        credential_name     => 'DEF_CRED_NAME',
        object_uri          => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
        directory_name      => 'WALLET_DIR'
    );
    END;
    /

    Dans cet exemple, namespace-string est l'espace de noms d'objet Oracle Cloud Infrastructure Object Storage et bucketname est le nom du bucket. Pour plus d'informations, reportez-vous à Espaces de nom Object Storage.

    Le portefeuille est copié dans le répertoire créé à l'étape précédente, WALLET_DIR. Le portefeuille qui vous permet de vous connecter à l'instance d'agent EXTPROC est désormais disponible sur votre instance Autonomous Database.

Etapes d'appel d'une procédure externe en tant que fonction SQL

Affiche les étapes permettant d'appeler une procédure externe en tant que fonction SQL.

Après avoir lancé l'application de pile OCI Marketplace EXTPROC et l'avoir configurée pour exécuter des procédures externes, vous créez une bibliothèque de fonctions de wrapper SQL qui référencent et appellent leurs procédures externes respectives.

Vous devez au préalable copier les bibliothèques sur liste blanche dans le répertoire /u01/app/oracle/extproc_libs sur la machine virtuelle EXTPROC.

Pour créer une bibliothèque dans votre instance Autonomous Database et enregistrer les routines C en tant que procédure externe dans la bibliothèque, procédez comme suit :
  1. Créez une bibliothèque.

    Une procédure externe est une routine de langage C stockée dans une bibliothèque. Pour appeler des procédures externes avec Autonomous Database, vous créez une bibliothèque.

    Exécutez DBMS_CLOUD_FUNCTION.CREATE_CATALOG pour créer une bibliothèque. Par exemple :

    BEGIN
        DBMS_CLOUD_FUNCTION.CREATE_CATALOG (
            library_name               => 'demolib',
            library_listener_url       => 'remote_extproc_hostname:16000',
            library_wallet_dir_name    => 'wallet_dir',
            library_ssl_server_cert_dn => 'CN=VM Hostname',
            library_remote_path        => '/u01/app/oracle/extproc_libs/library name'
    );
    END;
    /
    

    Cette opération crée la bibliothèque demolib dans votre instance Autonomous Database et inscrit la bibliothèque de liens dynamiques dans votre base de données. L'instance d'agent EXTPROC est préconfigurée pour héberger les procédures externes sur le port 16000.

    Pour plus d'informations, reportez-vous à Procédure CREATE_CATALOG.

    Interrogez les vues DBA_CLOUD_FUNCTION_CATALOG View et USER_CLOUD_FUNCTION_CATALOG View pour extraire la liste de tous les catalogues et bibliothèques de votre base de données.

    Interrogez la vue USER_CLOUD_FUNCTION_ERRORS View pour répertorier les erreurs générées lors de la validation de la connexion à l'emplacement de la bibliothèque distante.

  2. Créez la fonction.

    Par exemple :

    CREATE OR REPLACE FUNCTION ftest(
          x VARCHAR2, 
          y VARCHAR2) 
    RETURN VARCHAR2 AS LANGUAGE C
          LIBRARY test
          NAME "demolib"
          PARAMETERS(
              x STRING, 
              y STRING)
          AGENT IN (x);
    /
  3. Vous pouvez supprimer une bibliothèque existante en utilisant la procédure DROP_CATALOG. Par exemple :
    BEGIN
        DBMS_CLOUD_FUNCTION.DROP_CATALOG (
          LIBRARY_NAME  => 'demolib'
      );
    END;
    /
    

    Cette opération supprime la bibliothèque DEMOLIB.

    Pour plus d'informations, reportez-vous à Procédure DROP_CATALOG.