Tagged: fonction

Remplacer les caracteres accentues dans une chaine de carractere (PL/SQL / Oracle / Fonction)

Il est souvent utile de pouvoir assainir un champ ou une variable dans un bloc PL/SQL ou bien simplement dans une requête. Pour cela, voici une toute petite fonction pouvant être mutualisée dans votre package ‘boîte à outils’.

Cette fonction remplace les caractères ŸÿÝýÜüÙùÛûÚúðߊšŒœÖöÕõØøÒòÔôÓóÑñÏïÌìÎîÍíËëÈèÊêÉéÇçÆæÄäÃãÅåÀàÂâÁá par leurs équivalents YyYyUuUuUuUuossSsOEoeOoOoOoOoOoOoNnIiIiIiIiEeEeEeEeCcAEaeAaAaAaAaAaAa ainsi que les retours chariots et tabulations par des espaces.

Libre à vous de lui fournir en argument un debug pour tracer lors de vos recettes.

CREATE OR REPLACE FUNCTION "SANITIZE" (param$text IN VARCHAR2 , param$debug IN NUMBER)
RETURN VARCHAR2
IS

v_text varchar2(32000) := '';

BEGIN
	v_text := param$text;

	IF param$debug = 1 THEN
		DBMS_OUTPUT.put_line ( 'AVANT => : ' || v_text );
	END IF;   

	v_text := translate( v_text, 'á', 'a');
	v_text := translate( v_text, 'Á', 'A');
	v_text := translate( v_text, 'â', 'a');
	v_text := translate( v_text, 'Â', 'A');
	v_text := translate( v_text, 'à', 'a');
	v_text := translate( v_text, 'À', 'A');
	v_text := translate( v_text, 'å', 'a');
	v_text := translate( v_text, 'Å', 'A');
	v_text := translate( v_text, 'ã', 'a');
	v_text := translate( v_text, 'Ã', 'A');
	v_text := translate( v_text, 'ä', 'a');
	v_text := translate( v_text, 'Ä', 'A');
	v_text := translate( v_text, 'æ', 'ae');
	v_text := translate( v_text, 'Æ', 'AE');
	v_text := translate( v_text, 'ç', 'c');
	v_text := translate( v_text, 'Ç', 'C');
	v_text := translate( v_text, 'é', 'e');
	v_text := translate( v_text, 'É', 'E');
	v_text := translate( v_text, 'ê', 'e');
	v_text := translate( v_text, 'Ê', 'E');
	v_text := translate( v_text, 'è', 'e');
	v_text := translate( v_text, 'È', 'E');
	v_text := translate( v_text, 'ë', 'e');
	v_text := translate( v_text, 'Ë', 'E');
	v_text := translate( v_text, 'í', 'i');
	v_text := translate( v_text, 'Í', 'I');
	v_text := translate( v_text, 'î', 'i');
	v_text := translate( v_text, 'Î', 'I');
	v_text := translate( v_text, 'ì', 'i');
	v_text := translate( v_text, 'Ì', 'I');
	v_text := translate( v_text, 'ï', 'i');
	v_text := translate( v_text, 'Ï', 'I');
	v_text := translate( v_text, 'ñ', 'n');
	v_text := translate( v_text, 'Ñ', 'N');
	v_text := translate( v_text, 'ó', 'o');
	v_text := translate( v_text, 'Ó', 'O');
	v_text := translate( v_text, 'ô', 'o');
	v_text := translate( v_text, 'Ô', 'O');
	v_text := translate( v_text, 'ò', 'o');
	v_text := translate( v_text, 'Ò', 'O');
	v_text := translate( v_text, 'ø', 'o');
	v_text := translate( v_text, 'Ø', 'O');
	v_text := translate( v_text, 'õ', 'o');
	v_text := translate( v_text, 'Õ', 'O');
	v_text := translate( v_text, 'ö', 'o');
	v_text := translate( v_text, 'Ö', 'O');
	v_text := translate( v_text, 'œ', 'oe');
	v_text := translate( v_text, 'Œ', 'OE');
	v_text := translate( v_text, 'š', 's');
	v_text := translate( v_text, 'Š', 'S');
	v_text := translate( v_text, 'ß', 'ss');
	v_text := translate( v_text, 'ð', 'o');
	v_text := translate( v_text, 'ú', 'u');
	v_text := translate( v_text, 'Ú', 'U');
	v_text := translate( v_text, 'û', 'u');
	v_text := translate( v_text, 'Û', 'U');
	v_text := translate( v_text, 'ù', 'u');
	v_text := translate( v_text, 'Ù', 'U');
	v_text := translate( v_text, 'ü', 'u');
	v_text := translate( v_text, 'Ü', 'U');
	v_text := translate( v_text, 'ý', 'y');
	v_text := translate( v_text, 'Ý', 'Y');
	v_text := translate( v_text, 'ÿ', 'y');
	v_text := translate( v_text, 'Ÿ', 'Y');
	v_text := translate( v_text, CHR(9), ' ');
	v_text := translate( v_text, CHR(10), ' ');
	v_text := translate( v_text, CHR(13), ' ');

	IF param$debug = 1 THEN
		DBMS_OUTPUT.put_line ( 'APRES => : ' || v_text );
	END IF;   

	RETURN v_text;
END;
/
En savoir plus

Conditionnement de Procedures, Fonctions, Packages, Triggers, etc. selon un environnement en PL/SQL (Oracle)

Très utile pour distinguer et conditionner les blocs PL/SQL spécifiques selon les environnements dans le cadre de réplications et plus spécifiquement lors de sauvegardes par exemple :

select sys_context('USERENV','DB_NAME') as instance
from dual;

Vous pourrez ainsi générer des conditions d’exception selon vos environnements.

Explorez donc les possibilités de la fonction sys_context() vous trouverez sans doute rapidement une grande utilité à identifier certains paramètres du client ou de l’hôte pour le conditionnement d’exécution de packages, triggers etc.

--nom de la base de données
SELECT sys_context('USERENV','DB_NAME') as nom_bdd from dual;
--adresse ip du client
SELECT sys_context('USERENV', 'IP_ADDRESS') as adresse_ip FROM dual;
--nom de la machine
SELECT sys_context('USERENV', 'HOST' ) as hote FROM dual;
--schema actuel
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') as curr_schema FROM DUAL;
--domaine de la base de données
SELECT sys_context('USERENV', 'DB_DOMAIN') as db_domain FROM DUAL;
--user du serveur
SELECT sys_context('USERENV', 'SERVER_HOST') as srv_hote FROM DUAL;

Voici un cas concret d’utilisation dans un bloc PL/SQL, pour identifier vôtre environnement :

DECLARE
    --Variable de travail
    v_server_host varchar2(30) := '';
BEGIN
    --Identification de l'environnement
    SELECT sys_context('USERENV', 'SERVER_HOST')
    INTO v_server_host
    FROM DUAL;

    --Conditionnement
    IF ( v_server_host = 'environnement') THEN
        --Si c'est vrai
        DBMS_OUTPUT.put_line ( 'Vous êtes sur ' || v_server_host );
    ELSE
        --Alternative
        DBMS_OUTPUT.put_line ( 'Vous n''êtes pas sur ' || v_server_host );
    END IF;

--Gestion d'exception
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;
    WHEN OTHERS THEN
        NULL;
END;

Pour aller plus loin  :

En savoir plus

Tester le type de colonne dans une vue ou une table (Oracle / Php)

Lorsque vous utilisez beaucoup les vues et que celles ci sont très utilisées pour l’édition de rapport par dérrière (reporting, décisionnel, marketing, etc.), souvent le type de variable peut poser problème car incompatible. Dans ce cas si la colonne d’une vue est amenée à évoluer en terme ‘d’alimentation’ de donnée vous pouvez générer des erreurs non pas au sein de la vue (pas sur la couche base de données) mais au niveau applicatif (outils dépendant et/ou utilisant ces vues ).

Dans mon exemple ma vue a un champ de type ‘nombre’ (number), son alimentation est issue d’une table qui elle est de type ‘caractère’ (varchar). Il suffirait donc de tester dans le corps de la vue ce champs précis et d’isoler un champs lorsque celui ci ne correspond pas au type ‘nombre’ (number). La façon de procéder est très simple avec l’utilisation d’une petite fonction ou une procédure que vous pourrez ajouter dans un package.

CREATE OR REPLACE FUNCTION IS_NUMBER (LC$A_TESTER VARCHAR2)
RETURN number IS
dummy NUMBER;
INVALID_NUMBER EXCEPTION;
PRAGMA EXCEPTION_INIT(INVALID_NUMBER, -6502);
BEGIN
dummy := TO_NUMBER(LC$A_TESTER);
RETURN 1;
EXCEPTION WHEN INVALID_NUMBER THEN
RETURN 0;
END;
/

Pourquoi utiliser une fonction plutôt qu’une procédure ? Car son utilisation dans la vue par la suite en dépend, et aussi si vous désirez mutualiser son utilisation pour des packages àvenir ou dans d’autres bloc PL/SQL. Ceci dit, vous pouvez très facilement en faire une procédure en retournant directement la colonne ou null dans le cas contraire.

CREATE OR REPLACE PROCEDURE IS_NUMBER (LC$A_TESTER VARCHAR2)
RETURN number IS
dummy NUMBER;
INVALID_NUMBER EXCEPTION;
PRAGMA EXCEPTION_INIT(INVALID_NUMBER, -6502);
BEGIN
dummy := TO_NUMBER(LC$A_TESTER);
RETURN LC$A_TESTER;
EXCEPTION WHEN INVALID_NUMBER THEN
RETURN NULL;
END;
/

Et pour son utilisation dans une vue vous procéderez donc de cette façon :

SELECT decode(is_number('1'),1,to_number(id_salarie_att),'')
FROM DUAL

Je l’utilise ici dans un decode, mais libre à vous de l’utiliser dans un bloc PL/SQL, avec un case, etc.

En savoir plus

Transferer un fichier en FTP depuis une procedure/fonction/package en PL/SQL (Oracle)

Transferer un fichier en FTP depuis une procedure/fonction/package en PL/SQL (Oracle)

Sur dba-oracle.com, DR Timothy S Hall explique quelques méthodes pour le transfert FTP depuis une procédure/fonction/package de façon simple et dans différent modes (ASCII, Binaire,…) sur base oracle 9i. Pour le transfert en mode ASCII de fichiers XML/plats/texte, vous pouvez utilisez le package (API) FTP qui est fourni ici, prenez le ftp.pks (package specs) et le ftp.pkb (package body). Passez ensuite les specs puis le body sur votre base.

Une fois ceux ci installés et la procédure également, vous pourrez ensuite simplement appeler le transfert FTP de cette façon :

‘<schema>.envoi_ftp( <adresse_serveur_ftp>, <port_ftp>, <login>, <mdp>, <repertoire_local>, <nom_fichier>, <repertoire_destination>)’.

Voici le code de la procédure stockée :

CREATE OR REPLACE PROCEDURE ENVOI_FTP (
                                        param$serv     IN VARCHAR2,
                                        param$port     IN VARCHAR2,
                                        param$login    IN VARCHAR2,
                                        param$pass     IN VARCHAR2,
                                        param$rep      IN VARCHAR2,
                                        param$fic_name IN VARCHAR2,
                                        param$rep_dest IN VARCHAR2
                                      )
IS
    --Declaration des variables de travail
    l_conn  UTL_TCP.connection;
    --Initialisation des paramètres
    --partie ftp
    conf$srvftp    VARCHAR2(100) := param$serv ;
    conf$port      VARCHAR2(100) := param$port ;
    conf$login     VARCHAR2(100) := param$login ;
    conf$password  VARCHAR2(100) := param$pass ;
    --partie données a transferées
    conf$rep       VARCHAR2(100) := param$rep ;
    conf$fic_name  VARCHAR2(100) := param$fic_name ;
    conf$rep_dest  VARCHAR2(100) := param$rep_dest ;
BEGIN
    --Ouverture connexion
    l_conn := ftp.login( conf$srvftp, conf$port, conf$login , conf$password );
    --Mode Ascii
    ftp.ascii(p_conn => l_conn);
    --Transfert
    ftp.put(  p_conn      => l_conn,
              p_from_dir  => param$rep,
              p_from_file => conf$fic_name,
              p_to_file   => conf$rep_dest || conf$fic_name );
    --Cloture connexion
    ftp.logout(l_conn);
END;

Source package FTP et infos :

Pour aller plus loin :

En savoir plus

Creer une fonction retournant un curseur dans un package PL/SQL (Oracle)

Dans la déclaration des méthodes du package déclarer la méthode et sont type de curseur retourné :

TYPE curseur_test IS REF CURSOR;
/* DETAILS FONCTION */
FUNCTION FONCTION_TEST   ( PARAM$UN IN NUMBER, PARAM$DEBUG IN NUMBER )
               RETURN curseur_test;

Dans la définition du corps du package ajoutez alors :

FUNCTION FONCTION_TEST   ( PARAM$UN IN NUMBER, PARAM$DEBUG IN NUMBER )
               RETURN curseur_test
IS
    var_cur_test   curseur_test;
BEGIN

Open var_cur_test  FOR
            SELECT 'x'
            FROM DUAL;

RETURN var_cur_test;

END FONCTION_TEST;
/

Puis si vous l’utilisée dans une procédure annexe (ou bien même au sein de ce package comme dans notre exemple) procédez de cette façon :

PROCEDURE PROCEDURE_TEST   ( PARAM$UN IN NUMBER, PARAM$DEBUG IN NUMBER )
IS
    --Définition du curseur tampon
    TYPE TYP_REF_CUR IS REF CURSOR ;
    cur_test_travail TYP_REF_CUR ;

    lc$debug NUMBER :=0;
    lc$tampon NUMBER :=0;

BEGIN

    Cur_test_travail := PKG_NOM.FONCTION_TEST( 0 , lc$debug );

    -- Lecture d'une ligne
    Loop
    Fetch Cur_test_travail 

	  --Ajout de la valeur dans notre tampon
          Into lc$tampon;    

	  -- sortie lorsque le curseur ne ramène plus de ligne
          Exit When Cur_test_travail%NOTFOUND ; 

		/* TRAITEMENT */

    End loop ;
    close Cur_test_travail;

END PROCEDURE_TEST;
/
En savoir plus

Creer un package en PL/SQL (Oracle)

Voici la syntaxe d’écriture d’un package sous oracle, tout d’abord la déclaration :

CREATE OR REPLACE PACKAGE PKG_NOM
AS

/******************************************************************************
   NOM:            PKG_NOM
   DESCRIPTION:    Package de test

   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        22/03/2011  APIAT            1. Création du package.

   NOTES:

   - Cf. détail sur les fonctions / procédures

******************************************************************************/

/* DETAILS FONCTION */
FUNCTION FONCTION_TEST   ( PARAM$UN IN NUMBER, PARAM$DEBUG IN NUMBER )
         RETURN NUMBER;
/* DETAILS PROCEDURE */
PROCEDURE PROCEDURE_TEST ( PARAM$UN IN NUMBER, PARAM$DEBUG IN NUMBER );

END PKG_NOM;
/

Puis le corps du package :

CREATE OR REPLACE PACKAGE BODY PKG_NOM
AS

FUNCTION FONCTION_TEST   ( PARAM$UN IN NUMBER, PARAM$DEBUG IN NUMBER ) RETURN NUMBER
IS
/* DECLARATION VARAIBLES */
BEGIN

/* CORPS FONCTION*/

END FONCTION_TEST;
/

PROCEDURE PROCEDURE_TEST   ( PARAM$UN IN NUMBER, PARAM$DEBUG IN NUMBER )
IS
/* DECLARATION VARAIBLES */
BEGIN

/* CORPS PROCEDURE*/

END PROCEDURE_TEST;
/

END PKG_NOM;
/
En savoir plus