FAQ OracleConsultez toutes les FAQ

Nombre d'auteurs : 15, nombre de questions : 137, dernière mise à jour : 26 octobre 2006  Ajouter une question

 

Cette F.A.Q. a été réalisée à partir des questions fréquemment posées sur le forum Oracle de www.developpez.com et de l'expérience personnelle des auteurs. Elle pourra traiter de tout type de questions portant sur les technologies Oracle.

Nous espérons que cette F.A.Q. saura répondre à un maximum de vos questions. Nous vous souhaitons une bonne lecture.

L'équipe Oracle de Developpez.


SommaireDéveloppementPL/SQL (18)
précédent sommaire suivant
 

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

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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.

Mis à jour le 30 novembre 2004 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) :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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é
Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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

Mis à jour le 30 novembre 2004 SheikYerbouti

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

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

Mis à jour le 30 novembre 2004 SheikYerbouti

En installant l'outil Oracle : Profiler
Vous devez être connecté avec le privilège SYSDBA

Code sql :
1
2
3
4
5
6
7
8
--------------------------------------  
-- 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 :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
----------------------------------------------  
-- 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.

Mis à jour le 30 novembre 2004 SheikYerbouti

Le package DBMS_LOB ne fournit pas de procédure ou fonction REPLACE pour les CLOBs. En voici une :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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;

Mis à jour le 30 novembre 2004 helyos

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 :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
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 :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

Mis à jour le 30 novembre 2004 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 :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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 occurrences
  • (n,} : au moins n occurrences
  • {n,m} : entre n et m occurrences

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.

Mis à jour le 30 novembre 2004 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 :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
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;

Mis à jour le 30 novembre 2004 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 : Différentes façon de tracer l'activité de sessions

Mis à jour le 30 novembre 2004 lalystar

Pour avoir immédiatement la documentation minimale d'un package Oracle installé, on peut utiliser la requête suivante sous SQL*Plus :

Code sql :
1
2
3
4
5
SELECT text 
  FROM DBA_SOURCE 
 WHERE type = 'PACKAGE' 
   AND name LIKE UPPER('%&package%') 
 ORDER BY line;
Par exemple, pour DBMS_OUTPUT :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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

Mis à jour le 30 novembre 2004 lalystar

Simplement grâce la fonction TO_NUMBER :

En voici un exemple d'utilisation :

Code sql :
1
2
3
4
5
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

Mis à jour le 18 septembre 2006 SheikYerbouti

Grâce la fonction suivante :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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 :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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.

Mis à jour le 18 septembre 2006 SheikYerbouti

À partir d'Oracle 9i, vous pouvez utiliser le package DBMS_XMLQuery. En voici deux exemples d'utilisation :

  • Avec Utl_File :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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 :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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é :

Code sql :
1
2
3
4
5
6
7
8
9
10
SET SERVEROUTPUT ON SIZE 1000000 
  
DECLARE 
   fichier CLOB; 
BEGIN 
   fichier := dbms_XMLQuery.getXML ('SELECT * FROM EMP'); 
   OutPut_Fichier (fichier); 
  
END; 
/

Mis à jour le 18 septembre 2006 Xo

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:

Code sql :
1
2
3
4
5
6
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.

Mis à jour le 18 septembre 2006 SheikYerbouti

Voir le tutoriel sur le SQL Dynamique Natif

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 :

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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; 
/
Lorsqu’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 :

Code sql :
1
2
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.

Mis à jour le 18 septembre 2006 orafrance

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

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évéler ennuyeux.

Voici donc une méthode pour les rendre dynamiques :

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

Code sql :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 :

Code sql :
1
2
3
4
5
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 :

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

Code sql :
1
2
3
4
5
SQL> SELECT table_name FROM v_orafrance; 
  
TABLE_NAME 
------------------------------ 
T_ORAFRANCE

Mis à jour le 18 septembre 2006 orafrance

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 :

Code java :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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 » :

Code sql :
1
2
3
4
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 :

Code sql :
1
2
3
4
5
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

Code sql :
1
2
3
4
5
6
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 :
Code :
$ORACLE_HOME\javavm\install\initjvm.sql
En version 10g, vous pouvez utiliser le scheduler Oracle.

Mis à jour le 15 octobre 2006 helyos

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 deux arguments suivants :

  • iname : nom du fichier en entrée
  • oname : nom du fichier de sortie

Voici un exemple avec le fichier suivant :

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

Code :
1
2
3
4
5
6
7
...\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 :

Code sql :
1
2
3
SQL> @c:\temp\debut.plb 
  
Fonction créée.
Attention, ceci ne fonctionne pas sur les triggers ou les blocs PL/SQL anonymes, cf la documentation Oracle
Code :
1
2
3
4
5
6
7
8
9
10
11
12
13
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 /* */).

Mis à jour le 15 octobre 2006 LeoAnderson Xo

Proposer une nouvelle réponse sur la FAQ

Ce n'est pas l'endroit pour poser des questions, allez plutôt sur le forum de la rubrique pour ça


Réponse à la question

Liens sous la question
précédent sommaire suivant
 

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 © 2014 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et 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.

 
 
 
 
Partenaires

PlanetHoster
Ikoula