FAQ OracleConsultez toutes les FAQ

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.

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é

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

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

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

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.

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;

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;  
/

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

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.

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;

1
2
3
4
5
SELECT text 
  FROM DBA_SOURCE 
 WHERE type = 'PACKAGE' 
   AND name like UPPER('%&package%') 
 ORDER BY line;

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

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

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 ; 
/

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.

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; 
/

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; 
/

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; 
/

1
2
3
4
5
6
Declare 
  LC$OrdreSql  Varchar2(2000) ; 
Begin 
  LC$OrdreSql := 'ALTER INDEX my_index REBUILD' ; 
  EXECUTE IMMEDIATE LC$OrdreSql ; 
End;

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; 
/

1
2
INSERT INTO MaTable (MaColonne_PK, ...)  
           VALUES (maSequence.NextVal, ...);

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; 
/

1
2
3
4
5
CREATE OR REPLACE VIEW v_orafrance 
AS 
   SELECT * 
     FROM all_tables 
    WHERE table_name = UPPER (orafrance.get_param1);

SQL> execute orafrance.param1 := 't_orafrance';

1
2
3
4
5
SQL> select table_name from v_orafrance; 
  
TABLE_NAME 
------------------------------ 
T_ORAFRANCE

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"

1
2
3
4
CREATE OR REPLACE PROCEDURE Host_Command (p_command  IN  VARCHAR2) 
AS LANGUAGE JAVA  
NAME 'Host.executeCommand (java.lang.String)'; 
/

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', '');

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; 
/

$ORACLE_HOME\javavm\install\initjvm.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;

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

1
2
3
SQL> @c:\temp\debut.plb 
  
Fonction créée.

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 /* */).