Appeler des procédures externes en tant que fonctions SQL

Présente les étapes permettant d'appeler des procédures externes à l'aide de PL/SQL dans 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 du code PL/SQL ou SQL comme s'il s'agissait d'un sous-programme 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 que lorsque cela est nécessaire 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, mieux adapté au traitement des transactions SQL.

Les procédures externes sont utiles dans les cas suivants :

  • Résoudre des problèmes scientifiques et techniques

  • Analyse des données

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

Pour plus d'informations, reportez-vous à 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 (VCN) Oracle Cloud Infrastructure.

Les procédures externes sont prises en charge uniquement 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.

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 :

  • Une image de conteneur fournie par Oracle avec un 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 (VCN) Oracle Cloud Infrastructure. La communication sécurisée entre votre instance Autonomous Database et l'instance d'agent EXTPROC est assurée en définissant des règles de groupe de sécurité réseau de sorte que le trafic soit autorisé à partir de votre 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, d'enregistrer et d'appeler des fonctions et procédures externes.

    Pour plus d'informations, reportez-vous à 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 à l'aide de 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 dans le cadre des promotions d'argument par défaut.

    Cet exemple montre comment modifier la taille des promotions d'argument par défaut :

    void UpdateSalary(float x)
    ...

Créer 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és à 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), la bibliothèque extproc.so. La procédure UpdateSalary, définie à l'étape précédente, est contenue 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. Pour plus d'informations, reportez-vous à Connexion à la console Oracle Cloud Infrastructure.
  2. Dans le menu de navigation de gauche d'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 Recherche.
  4. Cliquez sur le widget EXTPROC de Type : pile.
Vous accédez ainsi à la page de détails de l'agent EXTPROC Oracle Autonomous Database.

Lancer l'application EXTPROC Stack

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 Pile de type, 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 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 vérifié et accepté les conditions standard Oracle et les restrictions.

  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 la 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 informations suivantes 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 la clé de votre choix.

      • Valeur de balise : saisissez la valeur de balise de votre choix.

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

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

  2. Cliquez sur Suivant.
    Vous accédez ainsi à la page Configurer les variables qui permet de configurer des variables pour les ressources d'infrastructure créées par la pile lors de l'exécution du travail d'application pour ce plan d'exécution.
  3. Sur la page Configurer les variables, entrez les informations dans les zones suivantes : 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 de la machine virtuelle d'agent EXTPROC.

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

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

        Une fois le portefeuille créé, il est impossible de modifier son mot de passe.
    2. Fournissez des informations dans la zone Network Configuration.
      • 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 le VCN et le 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 nouveau 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. Cette opération crée l'instance d'agent EXTPROC 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 privées indiquées dans votre réseau cloud virtuel (VCN) à se connecter à l'agent EXTPROC.

          Lorsque cette option est sélectionnée, vous devez fournir 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 de calcul.
      • Compartiment : sélectionnez le compartiment dans 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 à affecter à l'instance d'agent EXTPROC.

      • Taille de la 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 :
        • Sélectionner un fichier de clés SSH : téléchargez la partie de clé publique de votre paire de clés. Accédez au fichier de clés à télécharger ou glissez-déplacez-le dans la zone.

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

  4. Cliquez sur Suivant.

    Cette action vous dirige vers 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 par défaut ou des variables que vous n'avez pas modifiées.

    Le gestionnaire de ressources exécute le travail d'application pour créer les ressources de pile en conséquence. Vous accédez alors à la page Détails du travail et l'état du travail est Accepté. Lorsque le travail d'application démarre, le statut passe à En cours.

    Remarque

    Les informations dont vous avez besoin 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 sur laquelle 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. Notez ce qui suit pour le fichier de portefeuille :
    • Le fichier de portefeuille, ainsi que l'ID utilisateur et le mot de passe de la 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 permettant d'accéder à Object Storage dans lequel vous stockez le fichier de portefeuille cwallet.sso. Reportez-vous à Procédure CREATE_CREDENTIAL pour plus d'informations sur les paramètres de nom utilisateur et de mot de passe des différents services de stockage d'objet.
    La création d'informations d'identification pour accéder à Oracle Cloud Infrastructure Object Storage 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 aux 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 le portefeuille. Exemples :
    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 Oracle Cloud Infrastructure Object Storage et bucketname est le nom du bucket. Pour plus d'informations, reportez-vous à Espaces de noms 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

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

Après avoir lancé l'application de pile EXTPROC OCI Marketplace 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 de la machine virtuelle EXTPROC.

Pour créer une bibliothèque dans votre instance Autonomous Database et inscrire des 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. Exemples :

    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 à CREATE_CATALOG Procédure.

    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.

    Exemples :

    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 à l'aide de la procédure DROP_CATALOG. Exemples :
    BEGIN
        DBMS_CLOUD_FUNCTION.DROP_CATALOG (
          LIBRARY_NAME  => 'demolib'
      );
    END;
    /
    

    La bibliothèque DEMOLIB est supprimée.

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