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. 

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 

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 

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

---------------------------------------------- 
-- 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. 

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;

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

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  

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.

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;

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

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

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

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

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.

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

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

SET SERVEROUTPUT ON SIZE 1000000

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

END;
/

Declare
  LC$OrdreSql  Varchar2(2000) ;
Begin
  LC$OrdreSql := 'ALTER INDEX my_index REBUILD' ;
  EXECUTE IMMEDIATE LC$OrdreSql ;
End;

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

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

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

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

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

SQL> select table_name from v_orafrance;

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

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"

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

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

SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);
BEGIN
  Host_Command (p_command => 'move C:\test1.txt C:\test2.txt');
END;
/

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

...\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

SQL> @c:\temp\debut.plb

Fonction créée.