Vérifier le format de numéros à l'aide d'une procédure stockée

Le Job suivant permet de se connecter à une base Oracle contenant des numéros de sécurité social et le nom de leur propriétaire, en appelant une procédure stockée qui vérifie le format des numéros en fonction du format standard ###-##-####. Puis le résultat de la vérification, 1 pour un format valide et 0 pour un format non valide, est affiché en sortie dans la console.

• Glissez-déposez les composants suivants de la Palette dans l'espace de modélisation graphique : tOracleConnection, tOracleInput, tOracleSP et tLogRow.

• Reliez le composant tOracleConnection au tOracleInput à l'aide d'un lien Then Run puisqu'aucune donnée n'est traitée ici.

• Connectez les autres composants à l'aide de liens Row Main puisque des lignes sont transmises comme paramètres au composant SP et à la console.

• Dans l'onglet Basic settings du composant tOracleConnection, paramétrez les informations de connexion à la base de données correspondante. Vous pourrez donc réutiliser cette information dans tout autre composant lié aux bases de données.

• Puis sélectionnez le composant tOracleInput et paramétrez ses propriétés.

• Cochez la case Use an existing connection et sélectionnez le composant tOracleConnection dans la liste pour réutiliser les informations de connexion déjà paramétrées.

• Sélectionnez l'option Repository dans le champ Property type puisque le schéma Oracle est défini dans une entrée DB Oracle Connections du Repository. Si vous n'avez pas enregistré de métadonnée contenant les informations de connexion à la base de données Oracle dans le Repository, renseignez le nom du schéma manuellement.

• Puis sélectionnez l'option Repository dans le champ Schema, et récupérez le schéma correspondant à la table de la base de données Oracle.

• Dans cet exemple, la table contenant le numéro de Sécurité sociale contient un schéma à quatre colonnes dont : ID, NAME, CITY et SSNUMBER.

• Dans le champ Query, saisissez la requête Select suivante ou sélectionnez-la dans la liste si vous l'aviez stockée dans le Repository.

  select ID, NAME, CITY, SSNUMBER from SSN

• Puis sélectionnez le tOracleSP et paramétrez ses propriétés dans l'onglet Basic settings.

• Comme pour le composant tOracleInput, sélectionnez l'option Repository dans le champ Property type et cochez la case Use an existing connection, puis sélectionnez les entrées adéquates dans la liste, respectivement.

• Le schéma utilisé pour le composant tOracleSP varie légèrement du schéma d'entrée. En effet, une colonne supplémentaire (SSN_Valid) est ajoutée au schéma d'entrée. Cette colonne contiendra le statut de validité (1 ou 0) généré par la procédure.

• Dans le champ SP Name, saisissez le nom exact de la procédure stockée (ou de la fonction) telle qu'elle est appelée dans la base de données. Dans cet exemple, le nom de la procédure stockée est is_ssn.

• La fonction de base utilisée pour cet exemple est :

  CREATE OR REPLACE FUNCTION is_ssn(string_in VARCHAR2)
  RETURN PLS_INTEGER
  IS
  -- validating ###-##-#### format
  BEGIN
    IF TRANSLATE(string_in, '0123456789A', 'AAAAAAAAAAB') =
       'AAA-AA-AAAA' THEN
      RETURN 1;
    END IF;
    RETURN 0;
  END is_ssn;
  /

• Comme une valeur retournée est attendue dans cet exemple, la procédure agit comme une fonction, ainsi cochez la case Is function.

• La seule valeur retournée attendue est basée sur la colonne ssn_valid. Ainsi, sélectionnez l'entrée correspondante dans la liste.

• Dans la zone Parameters, définissez les paramètres d'entrée et de sortie utilisés dans la procédure. Dans cet exemple, seule la colonne SSNumber du schéma est utilisée dans la procédure.

• Cliquez sur le signe [+] pour ajouter un nouvelle ligne à la table et sélectionnez la colonne adéquate (SSNumber) et son type (IN).

• Puis sélectionnez le composant tLogRow et cliquez sur Sync Column pour récupérer le schéma du composant précédent (tOracleSP).

• Cochez la case Print values in cells of a table pour faciliter la lecture de la sortie.

• Sauvegardez votre Job et appuyez sur F6 pour l'exécuter.

Dans la console, vous pouvez lire les résultats en sortie. Toutes les colonnes du schéma d'entrée sont affichées même si elles ne sont pas utilisées comme paramètres dans la procédure stockée.

La colonne finale affiche la valeur retournée attendue, c'est-à-dire que le numéro de Sécurité sociale vérifié soit valide ou non.