Vous devez avoir un compte Developpez.com et être connecté pour pouvoir participer aux discussions.

Identifiez-vous
Identifiant
Mot de passe
Mot de passe oublié ?
Créer un compte

Vous n'avez pas encore de compte Developpez.com ? L'inscription est gratuite et ne vous prendra que quelques instants !

Je m'inscris !

Developpez.com

Oracle

Choisissez la catégorie, puis la rubrique :

logo
Sommaire > Développement > PL/SQL
        Comment obtenir le nombre de lignes impactées par le dernier ordre SQL (DML) ?
        Comment afficher les erreurs de compilation ?
        Comment compter le nombre d'occurrences dans une chaîne ?
        Comment obtenir le temps d'exécution instruction par instruction de vos codes PL/SQL ?
        Comment faire un REPLACE sur un CLOB ?
        Comment savoir si une chaine de caractères correspond à un nombre ?
        Comment vérifier si une chaine vérifie un certain format ?
        Ma requête fonctionne sous SQL*Plus mais lorsque j'essaie de l'utiliser dans une procédure PL/SQL, celle-ci ne compile pas !
        Comment générer un fichier de trace pour une session en cours ?
        Je n'ai pas de documentation Oracle, ni d'accès Internet, comment puis-je trouver la documentation d'un package Oracle ?
        Comment convertir une valeur hexadécimale en valeur décimale ?
        Comment afficher la différence de temps entre 2 dates en nombre de jours, heures, minutes et secondes ?
        Comment exporter le résultat d'une requête au format XML ?
        Comment exécuter une instruction du DDL dans un block PL/SQL ?
        Comment implémenter une colonne à valeur auto-incrémentée ?
        Comment créer une vue paramétrée ?
        Comment exécuter un shell depuis le PL/SQL ?
        Comment crypter mon code ?



Comment obtenir le nombre de lignes impactées par le dernier ordre SQL (DML) ?
auteur : SheikYerbouti
La variable SQL%ROWCOUNT contient le nombre de lignes impactées par le dernier ordre DML exécuté.

Vous pouvez interroger cette variable après tout ordre INSERT, UPDATE ou DELETE

SQL> SET SERVEROUTPUT ON 
SQL> DECLARE 
  2    LN$Cpt PLS_INTEGER ; 
  3  BEGIN 
  4    SELECT COUNT(*) INTO LN$Cpt FROM test ; 
  5    DBMS_OUTPUT.PUT_LINE( To_char( LN$Cpt ) || ' enregistrements') ; 
  
  6    -- Mise à jour -- 
  7    UPDATE test 
  8    SET nom = nom 
  9    WHERE ROWNUM <= 3 ;  
 10    DBMS_OUTPUT.PUT_LINE( To_char( SQL%ROWCOUNT ) || ' enregistrements mis à jour') ; 
 
 11    -- Insertion -- 
 12    INSERT INTO test 
 13    SELECT * FROM test ; 
 14    DBMS_OUTPUT.PUT_LINE( To_char( SQL%ROWCOUNT ) || ' enregistrements insérés') ; 
 
 15    -- Suppression -- 
 16    DELETE FROM test 
 17    WHERE ROWNUM < 3 ; 
 18    DBMS_OUTPUT.PUT_LINE( To_char( SQL%ROWCOUNT ) || ' enregistrements supprimés') ; 
 
 19    ROLLBACK ; 
 20  END ; 
 21  / 
6 enregistrements 
3 enregistrements mis à jour 
6 enregistrements insérés 
2 enregistrements supprimés 

Procédure PL/SQL terminée avec succès. 

Comment afficher les erreurs de compilation ?
auteur : SheikYerbouti
Les erreurs de compilation sont stockées dans la vue USER_ERRORS

Vous pouvez, après compilation d'un objet, afficher les éventuelles erreurs de compilation de deux façons :
  • SHOW ERROR
  • SELECT ... FROM USER_ERRORS
Exemple (SUBTR au lieu de SUBSTR) :

SQL> CREATE OR REPLACE FUNCTION debut 
  2  ( PC$Chaine IN VARCHAR2) RETURN VARCHAR2 IS 
  3  BEGIN 
  4    RETURN SUBTR( PC$Chaine, 1, 5 ) ; 
  5  END; 
  6  / 

Avertissement : Fonction créée avec erreurs de compilation. 

SQL> SHOW ERRORS 
Erreurs pour FUNCTION DEBUT : 

LINE/COL ERROR 
-------- ----------------------------------------------------------------- 
4/3      PL/SQL: Statement ignored 
4/10     PLS-00201: l'identificateur 'SUBTR' doit être déclaré 

SQL> 
SQL> SELECT * 
  2  FROM   user_errors 
  3  WHERE  name = 'DEBUT' 
  4  AND    type = 'FUNCTION' 
  5  ORDER BY SEQUENCE 
  6  / 

NAME                           TYPE           SEQUENCE       LINE   POSITION 
------------------------------ ------------ ---------- ---------- ---------- 
TEXT 
-------------------------------------------------------------------------------- 
DEBUT                          FUNCTION              1          4         10 
PLS-00201: l'identificateur 'SUBTR' doit être déclaré 

DEBUT                          FUNCTION              2          4          3 
PL/SQL: Statement ignored 

Comment compter le nombre d'occurrences dans une chaîne ?
auteur : SheikYerbouti
Comment compter le nombre d'occurrences dans une chaîne sans l'aide d'une boucle itérative ?

En utilisant les fonctions LENGTH() et REPLACE() de la façon suivante :

CREATE OR REPLACE FUNCTION CPT_OCCURRENCES 
  ( 
     PC$Entree IN VARCHAR2,   -- Chaîne en entrée 
     PC$Recherche IN VARCHAR2 -- Chaîne à rechercher 
  ) RETURN PLS_INTEGER IS 
BEGIN 
   RETURN ( (LENGTH(PC$Entree) - LENGTH(REPLACE(PC$Entree,PC$Recherche,NULL)) ) / NVL(LENGTH(PC$Recherche),1) ) ; 
END; 


SQL> SELECT CPT_OCCURRENCES( 'le et le et le', 'le' ) "Nombre d'occurrences" FROM DUAL ; 

Nombre d'occurrences 
-------------------- 
                   3 

Comment obtenir le temps d'exécution instruction par instruction de vos codes PL/SQL ?
auteur : SheikYerbouti
En installant l'outil Oracle : Profiler
Vous devez être connecté avec le privilège SYSDBA

-------------------------------------- 
-- Installation de l'outil profiler -- 
-------------------------------------- 
connect / as sysdba 

@<ORACLE_HOME>/rdbms/admin/proftab.sql 
@<ORACLE_HOME>/rdbms/admin/profload.sql 
@<ORACLE_HOME>/plsql/demo/profrep.sql
Et en l'exécutant sur n'importe quelle fonction ou procédure PL/SQL :

---------------------------------------------- 
-- Test de la procédure stockée : F_TRACE() -- 
---------------------------------------------- 

SQL> 
  1  DECLARE 
  2    Pgm NUMBER; 
  3  BEGIN 
  4    DBMS_PROFILER.START_PROFILER('test','test1',Pgm); 
  5    FD.F_TRACE('Hello Oracle world','T'); 
  6    DBMS_PROFILER.STOP_PROFILER; 
  7    DBMS_PROFILER.ROLLUP_RUN(Pgm); 
  8    PROF_REPORT_UTILITIES.PRINT_RUN(Pgm); 
  9* END; 
SQL> / 
===========================Results for run #1 made on 23-OCT-04 14:20:55 ========================= 
(test) Run total time:       .16 seconds 
Unit #1: SYS.DBMS_PROFILER - Total time:       .00 seconds 
Unit #2: <anonymous>.<anonymous> - Total time:       .00 seconds 
Unit #3: FD.F_TRACE - Total time:       .02 seconds 
1                                                procedure F_TRACE ( PC$Message in VARCHAR2, PC$Output in VARCHAR2 DEFAULT 'S' ) 
2                                                Is 
3                                                PRAGMA AUTONOMOUS_TRANSACTION ; 
4                                                  LC$String Varchar2(4000) ; 
5                                                  LN$Slices    PLS_INTEGER ; 
6                                                  LN$Rest      PLS_INTEGER ; 
7           1   ,00001660  ,00001660               LN$Pos       PLS_INTEGER := 1 ; 
8                                                  LN$Inc       PLS_INTEGER ; 
9                                                Begin 
10 
11           1   ,00009573  ,00009573               If Upper( PC$Output ) = 'S' Then 
12                                                      -- Screen output (DBMS_OUTPUT) -- 
13           0   0                                       LN$Inc := 255 ; 
14           0   0                                       LN$Slices := Length( PC$Message ) / LN$Inc ; 
15           0   0                                       LN$Rest    := MOD( Length( PC$Message ), LN$Inc ) ; 
16           0   0                                       If LN$Rest > 0 Then LN$Slices := LN$Slices + 1 ; End if ; 
17 
18                                                       -- output -- 
19           0   0                                       For i in 1..LN$Slices Loop 
20           0   0                                          LC$String := Substr( PC$Message, LN$Pos, LN$Inc ) ; 
21           0   0                                              DBMS_OUTPUT.PUT_LINE( LC$String ) ; 
22           0   0                                              LN$Pos := LN$Pos + LN$Inc ; 
23                                                       End loop ; 
24 
25                                                  Else 
26                                                      -- Table output (INSERT) -- 
27           1   ,00000095  ,00000095                    LN$Inc := 4000 ; 
28           1   ,00003589  ,00003589                    LN$Slices := Length( PC$Message ) / LN$Inc ; 
29           1   ,00001622  ,00001622                    LN$Rest    := MOD( Length( PC$Message ), LN$Inc ) ; 
30           1   ,00000411  ,00000411                    If LN$Rest > 0 Then LN$Slices := LN$Slices + 1 ; End if ; 
31 
32                                                       -- output -- 
33           2   ,00004027  ,00002013                    For i in 1..LN$Slices Loop 
34           1   ,00001587  ,00001587                       LC$String := Substr( PC$Message, LN$Pos, LN$Inc ) ; 
35           1   ,01580613  ,01580613                           Insert into TRACE (LIGNE) Values ( LC$String ) ; 
36           1   ,00094689  ,00094689                           Commit ; 
37           1   ,00000270  ,00000270                           LN$Pos := LN$Pos + LN$Inc ; 
38                                                       End loop ; 
39                                                  End if ; 
40 
41                                                End; 

Procédure PL/SQL terminée avec succès. 

Comment faire un REPLACE sur un CLOB ?
auteur : Helyos
Le package DBMS_LOB ne fournit pas de procédure ou fonction REPLACE pour les CLOBs. En voici une :

CREATE OR REPLACE PROCEDURE lob_replace(p_dest_clob    IN OUT CLOB,
                        p_search  VARCHAR2,
                        p_replace VARCHAR2) AS
    v_clob_temp       CLOB;
    v_end_offset      INTEGER := 1;
    v_start_offset    INTEGER := 1;
    v_occurence       NUMBER := 1;
    v_replace_len NUMBER := length(p_replace);
    v_clob_temp_len   NUMBER := 0;
    v_dest_clob_len    NUMBER := 0;
  BEGIN
    IF dbms_lob.ISOPEN(p_dest_clob) = 0 THEN
      NULL;
    END IF;
    dbms_lob.createtemporary(v_clob_temp, TRUE, dbms_lob.session);
    LOOP
      v_end_offset := dbms_lob.instr(p_dest_clob, p_search, 1, v_occurence);
      IF v_end_offset = 0 THEN
        v_clob_temp_len := dbms_lob.getlength(v_clob_temp);
        v_dest_clob_len  := dbms_lob.getlength(p_dest_clob) - v_start_offset + 1;
        IF v_dest_clob_len > 0 THEN
          dbms_lob.copy(v_clob_temp,
                        p_dest_clob,
                        v_dest_clob_len,
                        v_clob_temp_len + 1,
                        v_start_offset);
        END IF;
        EXIT;
      END IF;
      v_clob_temp_len := dbms_lob.getlength(v_clob_temp);
      IF (v_end_offset - v_start_offset) > 0 THEN
        dbms_lob.copy(v_clob_temp,
                      p_dest_clob,
                      (v_end_offset - v_start_offset),
                      v_clob_temp_len + 1,
                      v_start_offset);
      END IF;
      v_start_offset := v_end_offset + length(p_search);
      v_occurence    := v_occurence + 1;
      IF p_replace IS NOT NULL THEN
        dbms_lob.writeappend(v_clob_temp, v_replace_len, p_replace);
      END IF;
    END LOOP;
    IF length(p_search) > length(p_replace) THEN
      dbms_lob.TRIM(p_dest_clob, dbms_lob.getlength(v_clob_temp));
    END IF;
    dbms_lob.copy(p_dest_clob, v_clob_temp, dbms_lob.getlength(v_clob_temp), 1, 1);
  EXCEPTION
    WHEN OTHERS THEN
      htp.prn('[Error in lob_replace : ' || SQLCODE || ' ' ||
              SQLERRM || ']');
  END;

Comment savoir si une chaine de caractères correspond à un nombre ?
auteur : lalystar
Plutôt que de coder une fonction qui traite tous les cas de figure correspondant aux différents formats que peut prendre un nombre (entier, nombre à virgule, nombre avec exposant...), il vaut mieux laisser Oracle gérer la conversion et nous signaler s'il a rencontré une erreur comme dans la fonction suivante :

create or replace function isNumeric(x in varchar2) return number as 
   -- renvoie 1 si le paramètre correspond à un nombre 
   -- 0 sinon 
   nb   number; 
begin 
   nb := to_number(x); 
   return 1; 
exception 
   when others then 
      return 0; 
end; 
/
Exemple d'utilisation : si la colonne X correspond à un nombre, on veut avoir ce nombre multiplié par 1000 :

SQL> SELECT x, 
   2        isNumeric(x), 
   3        CASE 
   4           WHEN isNumeric(x) = 1 THEN 1000 * TO_NUMBER(x) 
   5        END x_fois_1000 
   6   FROM TEST;
  
X          ISNUMERIC(X) X_FOIS_1000 
---------- ------------ ----------- 
1                     1        1000 
1,10                  1        1100 
1,234E3               1     1234000 
-1,23E-3              1       -1,23 
1 toto                0  

Comment vérifier si une chaine vérifie un certain format ?
auteur : lalystar
Pour vérifier qu'une chaine de caractère vérifie un format donné, on peut utiliser, à partir d'Oracle 10g, la fonction MATCH du OWA_PATTERN qui permet de manipuler des expressions régulières. Cette fonction retourne un booléen indiquant si le format est vérifié ou pas; elle ne peut donc être appelée qu'en PL/SQL.
Par exemple, pour vérifier que les numéros de téléphones en base suivent le format français sur 10 chiffres avec comme séparateur des tirets, on peut faire :

SQL> select *
  2  from   TELEPHONE;

TELEPHONE#
--------------------------------------------------------------------------------
07.08.09.02.02
07-08-09-02-02
01 02 02 02 02
TOTO
07/07/07/07/07

SQL>
  1  begin
  2     for tel in (select * from TELEPHONE)
  3     loop
  4        if owa_pattern.match(tel.telephone#, '^\d{2}-\d{2}-\d{2}-\d{2}-\d{2}$') then
  5           dbms_output.put_line(tel.telephone#);
  6        end if;
  7     end loop;
  8* end;
SQL> /
07-08-09-02-02

PL/SQL procedure successfully completed.
Les expressions régulières peuvent utiliser les symboles suivants :
  • ^ : début de la ligne
  • $ : saut de ligne ou fin de ligne
  • \n : saut de ligne
  • . : tout caractères sauf le saut de ligne
  • \t : tabulation
  • \d : chiffre (équivalent à [0-9])
  • \D : tout caractère sauf un chiffre (équivalent à [not 0-9])
  • \w : tout caractère alphanumérique (chiffres, lettres, _)
  • \W : tout caractère sauf un caractère alphanumérique
  • \s : tout espace (espace, tabulation, saut de ligne)
  • \S : tout caractère sauf un espace
  • \b : délimiteur de mots (entre un caractère qui vérifie \w et un autre qui vérifie \W)
  • \xnn : caractère dont le code ascii est en hexadécimal nn
  • \nnn : caractère dont le code ascii est en octal nnn
Les éléments ci-dessus peuvent être suivi par les indicateurs de cardinalité suivants :
  • ? : 0 ou 1 occurrence
  • * : 0 ou plus occurrences
  • + : 1 ou plus occurrences
  • {n} : exactement n occurences
  • (n,} : au moins n occurences
  • {n,m} : entre n et m occurences
L'expression régulière qu'on a utilisé pour décrire notre format de numéro de téléphone ^\d{2}-\d{2}-\d{2}-\d{2}-\d{2}$ se décrypte donc de la manière suivante :
  • \d{2} : on cherche deux chiffres
  • - : suivis d'un tiret
  • \d{2}- : suivi de deux chiffres et d'un tiret
  • \d{2}- : suivi de deux chiffres et d'un tiret
  • \d{2}- : suivi de deux chiffres et d'un tiret
  • \d{2} : suivi de deux chiffres
Comme notre expression régulière commence par ^, elle décrit le début de la chaine; comme elle se termine par $, elle décrit aussi sa fin.


Ma requête fonctionne sous SQL*Plus mais lorsque j'essaie de l'utiliser dans une procédure PL/SQL, celle-ci ne compile pas !
auteur : lalystar
Sous Oracle 8i, le moteur SQL utilisé dans les procédures PL/SQL est différent et est en retard sur le moteur SQL utilisé pour les requêtes. Certaines fonctionnalités disponibles en SQL ne le sont donc pas en PL/SQL comme :
  • les structures CASE ... WHEN
  • les fonctions analytiques
La solution est d'utiliser du SQL dynamique pour masquer la requête au moteur PL/SQL en utilisant par exemple EXECUTE IMMEDIATE :

begin
   execute immediate '
      insert into TEST
      select
         case
            when deptno = 10 then ''Dept 10''
            when deptno = 20 then ''Dept 20''
            else
               ''Departement inconnu''
         end
      from
         TEST1';
end;

Comment générer un fichier de trace pour une session en cours ?
auteur : lalystar
Quand une procédure est en cours d'exécution, il est possible de générer un fichier de trace (pour exploitation par TkProf par exemple) en utilisant la routine set_sql_trace_in_session du package DBMS_SYSTEM. Les paramètres de cette routine sont les suivants :
  • SID : identifiant de la session en provenance de V$SESSION
  • SERIAL# : second identifiant de la session en provenance de V$SESSION
  • TRACE: booléen; il faut entrer TRUE pour demander à Oracle de générer le fichier de trace, et FALSE pour arrêter sa génération
Vous trouverez plus de détails dans cet article : fr Différentes façon de tracer l'activité de sessions


Je n'ai pas de documentation Oracle, ni d'accès Internet, comment puis-je trouver la documentation d'un package Oracle ?
auteur : lalystar
Pour avoir immédiatement la documentation minimale d'un package Oracle installé, on peut utiliser la requête suivante sous SQL*Plus :

SELECT text
  FROM DBA_SOURCE
 WHERE type = 'PACKAGE'
   AND name like UPPER('%&package%')
 ORDER BY line;
Par exemple, pour DBMS_OUTPUT :

SQL> SELECT text
  2    FROM dba_source
  3  WHERE type = 'PACKAGE'
  4    AND name like UPPER('%&package%');
Enter value for package: dbms_output
old   4:        name like UPPER('%&package%')
new   4:        name like UPPER('%dbms_output%')

  ------------
  --  OVERVIEW
  --
  --  These procedures accumulate information in a buffer (via "put" and
  --  "put_line") so that it can be retrieved out later (via "get_line" or
  --  "get_lines").  If this package is disabled then all
  --  calls to this package are simply ignored.  This way, these routines
  --  are only active when the client is one that is able to deal with the
  --  information.  This is good for debugging, or SP's that want to want
  --  to display messages or reports to sql*dba or plus (like 'describing
  --  procedures', etc.).  The default buffer size is 20000 bytes.  The
  --  minimum is 2000 and the maximum is 1,000,000.
...
  procedure put_line(a varchar2);
  pragma restrict_references(put_line,WNDS,RNDS);
  procedure put_line(a number);
  pragma restrict_references(put_line,WNDS,RNDS);
  --  Put a piece of information in the buffer followed by an end-of-line
  --    marker.  When retrieved by get_line(s), the number and date items
  --    will be formated with to_char using the default formats.  If you
  --    want another format then format it explicitly. get_line(s) return
  --    "lines" as delimited by "newlines". So every call to put_line or
  --    new_line will generate a line that will be returned by get_line(s).
  --  Input parameters:
  --    a
  --      Item to buffer
  --  Errors raised:
  --    -20000, ORU-10027: buffer overflow, limit of <buf_limit> bytes.
  --    -20000, ORU-10028: line length overflow, limit of 255 bytes per line

Comment convertir une valeur hexadécimale en valeur décimale ?
auteur : SheikYerbouti
Simplement grâce la fonction TO_NUMBER :

En voici un exemple d'utilisation :

SQL> SELECT TO_NUMBER('ff','XXXXXXXX'),TO_NUMBER('fff','XXXXXXXX'),TO_NUMBER('ffff','XXXXXXXX') from dual;

TO_NUMBER('FF','XXXXXXXX') TO_NUMBER('FFF','XXXXXXXX') TO_NUMBER('FFFF','XXXXXXXX')
-------------------------- --------------------------- ----------------------------
                       255                        4095                        65535

Comment afficher la différence de temps entre 2 dates en nombre de jours, heures, minutes et secondes ?
auteur : SheikYerbouti
Grâce la fonction suivante :

CREATE OR REPLACE FUNCTION Diff_Temps
  (
     LD$Date_Deb IN DATE DEFAULT SYSDATE
	,LD$Date_Fin IN DATE DEFAULT SYSDATE
	,LN$JJ       OUT PLS_INTEGER
	,LN$HH       OUT PLS_INTEGER
	,LN$MI       OUT PLS_INTEGER
	,LN$SS       OUT PLS_INTEGER
  ) Return NUMBER
IS
  dif   NUMBER ;
Begin
  
  If LD$Date_Fin < LD$Date_Deb Then
     Return ( -1 ) ;
  End if ;

  Select  LD$Date_Fin - LD$Date_Deb Into dif  From DUAL ;
  Select  trunc ( LD$Date_Fin - LD$Date_Deb)  Into LN$JJ  From DUAL ;
  Select  trunc ( (LD$Date_Fin - LD$Date_Deb) * 24) -  ( LN$JJ * 24 ) Into LN$HH From DUAL ;
  Select  trunc ( (LD$Date_Fin - LD$Date_Deb) * 1440) - ( (LN$HH * 60) + ( LN$JJ * 1440) ) Into LN$MI From DUAL ;
  Select  trunc ( (LD$Date_Fin - LD$Date_Deb) * 86400) - ( (LN$MI * 60) + (LN$HH * 3600) + ( LN$JJ * 3600 * 24 ) ) Into LN$SS From DUAL ;

  Return( dif ) ;
  
End ;
/
En voici un exemple d'utilisation :

SQL> set serveroutput on
SQL> declare
  2   dd pls_integer;
  3   hh pls_integer;
  4   mi pls_integer;
  5   ss pls_integer;
  6   dif number ;
  7  Begin 
  8   dif := diff_temps ( sysdate, sysdate + 10.523, dd,hh,mi,ss ) ;
  9   dbms_output.put_line( 
 10     '(' || ltrim(to_char(dif,'99999.99999')) || ')' || '  ' 
 11     || to_char(dd,'99999') || 'j ' 
 12     || to_char(hh,'00') ||':' 
 13     || to_char(mi,'00') ||':'
 14     || to_char(ss,'00') 
 15     ) ;
 16  End;
 17  /
(10.52300)      10j  12: 33: 07

Procédure PL/SQL terminée avec succès.

Comment exporter le résultat d'une requête au format XML ?
auteur : Xavier Vlieghe
À partir d'Oracle 9i, vous pouvez utiliser le package DBMS_XMLQuery. En voici 2 exemples d'utilisation :
  • Avec Utl_File :

SET SERVEROUTPUT ON
DECLARE
  XmlFic  Utl_File.File_Type;
  XmlData CLOB;
  Fin     BOOLEAN := TRUE;
BEGIN
   -- Créer des données au format XML à partir d'une requête :
   XmlData := DBMS_XMLQuery.GetXML ('SELECT * FROM EMP');
  
   -- Copie les données au format XML dans un fichier :
   XmlFic := Utl_File.FOpen ('C:\TMP\XML', 'TEST.xml', 'W');

   WHILE FIN LOOP
      Utl_File.Put (XmlFic, SUBSTR (XmlData, 1, 32767));

      IF LENGTH (XmlData) > 32767 THEN
         XmlData :=  SUBSTR (XmlData, 32768);
      ELSE
         FIN := FALSE;
      END IF;

   END LOOP;

   Utl_File.FClose (XmlFic);

EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM,1,255));
      Utl_File.FClose (XmlFic);
END;
/
  • Sans Utl_File : compilez d'abord la procédure suivante :

CREATE OR REPLACE PROCEDURE OutPut_Fichier (fichier IN OUT NOCOPY CLOB) IS
   ContenuXML VARCHAR2 (32767);
   Ligne VARCHAR2 (2000);
BEGIN

   ContenuXML := dbms_lob.SUBSTR(fichier, 32767);

   LOOP
      EXIT WHEN ContenuXML IS NULL;
      -- découpe en lignes :
      Ligne := SUBSTR(ContenuXML, 1, INSTR(ContenuXML, CHR(10)) - 1);
      dbms_output.put_line (Ligne);
      ContenuXML := SUBSTR(ContenuXML, INSTR(ContenuXML, CHR(10)) + 1);
   END LOOP;
END;
/
Et le tour est joué :

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
   fichier CLOB;
BEGIN
   fichier := dbms_XMLQuery.getXML ('SELECT * FROM EMP');
   OutPut_Fichier (fichier);

END;
/

Comment exécuter une instruction du DDL dans un block PL/SQL ?
auteur : SheikYerbouti
Si vous souhaitez exécuter un ordre du DDL (Data Description Language) pour créer une table, un index, altérer un objet existant, utilisez l'instruction EXECUTE IMMEDIATE.

Par exemple, pour forcer la recréation d'un index:

Declare
  LC$OrdreSql  Varchar2(2000) ;
Begin
  LC$OrdreSql := 'ALTER INDEX my_index REBUILD' ;
  EXECUTE IMMEDIATE LC$OrdreSql ;
End;
N'oubliez-pas que tout ordre du DDL génère un COMMIT implicite et que les droits nécessaires à l'exécution de l'instruction contenue dans la fonction EXECUTE IMMEDIATE doivent avoir été donné directement à l'utilisateur, et non pas via un rôle.

lien : fr Voir le tutoriel sur le SQL Dynamique Natif

Comment implémenter une colonne à valeur auto-incrémentée ?
auteur : Fred_D
Les champs de type "Auto-Increment" n'existe pas en Oracle. Pour émuler ce comportement, vous avez besoin
  • d'une séquence pour incrémenter la valeur de 1 à chaque insertion
  • d'un trigger pour renseigner automatiquement le champ auto-incrémenté de votre table.
Voici comment procéder :

CREATE SEQUENCE MaSequence
  START WITH 1
  MAXVALUE 999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

CREATE TRIGGER MonTrigger
   BEFORE INSERT
   ON MaTable
   FOR EACH ROW
BEGIN
   SELECT MaSequence.NEXTVAL
     INTO :NEW.macolonne_PK
     FROM DUAL;
END;
/
Lorqu'une ligne sera insérée dans la table MaTable, la colonne macolonne_PK sera alors renseignée avec la valeur suivante de la séquence MaSequence.

Il est également possible de se passer du trigger, et d'opter pour la syntaxe suivante :

INSERT INTO MaTable (MaColonne_PK, ...) 
           VALUES (maSequence.NextVal, ...);
Il est utile de noter que la séquence n'est donc pas liée à un champ ou une table, et que vous pouvez utiliser la même séquence pour plusieurs champs.

lien : fr Plus d'infos sur les triggers (ou déclencheurs)

Comment créer une vue paramétrée ?
auteur : Fred_D
Il peut être utile de rendre certains objets dynamiques, pour plus de souplesse dans vos applications. Or, les vues sont souvent "créées en dur" dans la base, ce qui peut se réveler ennuyeux.

Voici donc une méthode pour les rendre dynamiques :

Créez d'abord un package, avec un paramètre global param1 :

CREATE OR REPLACE PACKAGE orafrance
AS
   param1   VARCHAR2 (30);

   FUNCTION get_param1
      RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY orafrance
AS
   FUNCTION get_param1
      RETURN VARCHAR2
   AS
   BEGIN
      RETURN param1;
   END;
END;
/
Créez ensuite votre vue, en faisant appel à ce paramètre :

CREATE OR REPLACE VIEW v_orafrance
AS
   SELECT *
     FROM all_tables
    WHERE table_name = UPPER (orafrance.get_param1);
Pour utiliser votre vue, il faut d'abord initialiser le paramétre :

SQL> execute orafrance.param1 := 't_orafrance';
Et vous pouvez désormais faire appel à votre vue :

SQL> select table_name from v_orafrance;

TABLE_NAME
------------------------------
T_ORAFRANCE

Comment exécuter un shell depuis le PL/SQL ?
auteur : Helyos
Cette méthode est valable jusqu'en version 9 :
Pour exécuter une commande système depuis du PL/SQL nous allons utiliser une classe Java. La création d'un classe java exécutable en PL/SQL passe par trois étapes :
  • tout d'abord on créé la Java Source
  • ensuite on publie la spécification d'appel de cette source
  • et enfin on accorde les privilèges nécessaires à l'exécution de cette classe Java
Donc on va tout d'abord créer la classe Java permettant d'exécuter une commande système :

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
  public static void executeCommand(String command) {
    try {
      String[] finalCommand;
      if (isWindows()) {
        finalCommand = new String[4];
        finalCommand[0] = "C:\\windows\\system32\\cmd.exe";
        finalCommand[1] = "/y";
        finalCommand[2] = "/c";
        finalCommand[3] = command;
      }
      else {
        finalCommand = new String[3];
        finalCommand[0] = "/bin/sh";
        finalCommand[1] = "-c";
        finalCommand[2] = command;
      }
  
      final Process pr = Runtime.getRuntime().exec(finalCommand);
      new Thread(new Runnable() {
        public void run() {
          try {
            BufferedReader br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
            String buff = null;
            while ((buff = br_in.readLine()) != null) {
              System.out.println("Process out :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_in.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process output.");
            ioe.printStackTrace();
          }
        }
      }).start();
  
      new Thread(new Runnable() {
        public void run() {
          try {
            BufferedReader br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
            String buff = null;
            while ((buff = br_err.readLine()) != null) {
              System.out.println("Process err :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_err.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process error.");
            ioe.printStackTrace();
          }
        }
      }).start();
    }
    catch (Exception ex) {
      System.out.println(ex.getLocalizedMessage());
    }
  }
  
  public static boolean isWindows() {
    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
      return true;
    else
      return false;
  }

};
/
show errors java source "Host"
Ensuite on publie la spécification d'appel de cette classe en utilisant une procédure PL/SQL pour la "wrapper" :

CREATE OR REPLACE PROCEDURE Host_Command (p_command  IN  VARCHAR2)
AS LANGUAGE JAVA 
NAME 'Host.executeCommand (java.lang.String)';
/
Enfin, les permissions nécessaire doivent être données par l'utilisateur SYS pour que le JServer puisse accèder aux fichiers su système :
 
EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');

EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');

EXEC Dbms_Java.Grant_Permission('SCHEMA-NAME', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
On doit se reconnecter pour activer les privilèges.

Ensuite on lance notre commande

SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
  Host_Command (p_command => 'move C:\test1.txt C:\test2.txt');
END;
/
PS : ne pas oublier de lancer le script :
$ORACLE_HOME\javavm\install\initjvm.sql

En version 10g, vous pouvez utiliser le fr scheduler Oracle.


Comment crypter mon code ?
auteurs : Xavier Vlieghe, LeoAnderson
Il est parfois nécessaire, lors du déploiement de vos applicatifs, de crypter votre code afin de le protéger : il suffit pour cela de sauvegarder votre code sous forme de fichiers sql et d'utiliser le programme wrap.exe, se trouvant dans le répertoire {Oracle_Home}\Bin\.

Le programme wrap accepte les 2 arguments suivants :
  • iname : nom du fichier en entrée
  • oname : nom du fichier de sortie
Voici un exemple avec le fichier suivant :
debut.pls

CREATE OR REPLACE FUNCTION debut 
  (PC$Chaine IN VARCHAR2) RETURN VARCHAR2 IS 
BEGIN 
    RETURN SUBSTR( PC$Chaine, 1, 5 ) ; 
END; 
Encryptons-le :

...\BIN> wrap iname=c:\temp\debut.pls oname=c:\temp\debut.plb

PL/SQL Wrapper: Release 10.2.0.1.0- Production on Dim. Oct.  15 23:52:50 2006

Copyright (c) 1993, 2004, Oracle.  All rights reserved.

Processing c:\temp\debut.pls to c:\temp\debut.plb
Vous pouvez ouvrir le nouveau fichier pour vérifier son contenu.

Attention, prenez évidemment soin de sauvegarder vos sources, cette procédure est bien entendu irréversible !

Vous pouvez (devez) également vérifier que la compilation s'effectue correctement :

SQL> @c:\temp\debut.plb

Fonction créée.
Attention, ceci ne fonctionne pas sur les triggers ou les blocs PL/SQL anonymes, cf la en documentation Oracle

The following CREATE statements are obfuscated: CREATE [OR REPLACE] FUNCTION function_name CREATE [OR REPLACE] PROCEDURE procedure_name CREATE [OR REPLACE] PACKAGE package_name CREATE [OR REPLACE] PACKAGE BODY package_name CREATE [OR REPLACE] TYPE type_name AS OBJECT CREATE [OR REPLACE] TYPE type_name UNDER type_name CREATE [OR REPLACE] TYPE BODY type_name The CREATE [OR REPLACE] TRIGGER statement, and [DECLARE] BEGIN..END anonymous blocks, are not obfuscated. All other SQL statements are passed unchanged to the output file. All comment lines in the unit being wrapped are deleted, except for those in a CREATE OR REPLACE header and C-style comments (delimited by /* */).


Consultez les autres F.A.Q's


Valid XHTML 1.0 TransitionalValid CSS!

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2007 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site ni de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Contacter le responsable de la rubrique Oracle

Partenaire : Hébergement Web