ORACLE SQL/PLSQL/ FORM / REPORT/ APEX: 2012

Friday 13 July 2012

Import export d'un CLOB

1- Export d'un CLOB

le présent article vous montre une méthode simple pour l'export du contenu d'un type CLOB sur un système de fichier.

Premiérement on doit crée un repértoire(directory) qui pointera sur le clob à exporter.

 CREATE OR REPLACE DIRECTORY fichier AS 'C:\Images\';
SQL> Directory created.

On va lire le contenu du CLOB pour l'écrire(Enregistrer) dans le répertoire crée précédemment.

SQL> SET SERVEROUTPUT ON
SQL>DECLARE
2 l_file    UTL_FILE.FILE_TYPE;
3 l_clob    CLOB;
4 l_buffer  VARCHAR2(32767);
5  l_amount  BINARY_INTEGER := 32767;
6  l_pos     INTEGER := 1;
6 BEGIN
7   SELECT col1
8  INTO   l_clob
9  FROM   tab1
10  WHERE  rownum = 1;

11  l_file := UTL_FILE.fopen('FICHIER', 'Test01.txt', 'w', 32767);

12  LOOP
13      DBMS_LOB.read (l_clob, l_amount, l_pos, l_buffer);
14      UTL_FILE.put(l_file, l_buffer);
15      l_pos := l_pos + l_amount;
16   END LOOP;
17  EXCEPTION
18  WHEN NO_DATA_FOUND THEN
19    UTL_FILE.fclose(l_file);
20  WHEN OTHERS THEN
21    UTL_FILE.fclose(l_file);
22    RAISE;
23  END;
/
PL/SQL procedure successfully  completed.

Remarque: l’arrêt du processus est fait par le biais de l'exception NO_DATA_FOUND.D'autre exception pouvent causer l'arrêt

de l'écriture dans le répertoire, pour cela vous pouvez vous referez au package:UTL_FILE pour gérer d'autre exception

2- Import d'un CLOB

On va utiliser le répertoire crée précédent pour faire enregistrer le fichier dans le CLOB.

On va crèer la table qui va servir comme sauvgarde de notre fichier text.



SQL>CREATE TABLE tab1 (
  id_file        NUMBER,
  clob_data      CLOB
);
Table created

On va importer notre fichier text et l'insérer dans la table par le processu suivant:



SQL>DECLARE
2  l_bfile  BFILE;
3  l_clob   CLOB;
4 BEGIN
5  INSERT INTO tab1 (id_file, clob_date)
6  VALUES (1, empty_clob())
7  RETURN clob_data INTO l_clob;

8  l_bfile := BFILENAME('FICHIER', 'Test01.txt');
9  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
10  DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
11  DBMS_LOB.fileclose(l_bfile);
12   COMMIT;
13 END;
/
PL/SQL procedure successfully  completed.

Wednesday 11 July 2012

Cryptage de mot de passe (Storing Passwords in an Oracle Database)

Sauvgarde de mot de passe dans une base de donnée Oracle

Quand on parle de gestion de la sécurité des applications,il y'a souvent un besoin de stocker des mots de passe

dans une table de base de données. Ceci peut en soi mener aux questions de sécurité, puisque des utilisateurs

avec des privilèges appropriés peuvent lire le contenu des ces tables . Une approche de sécurité consiste en

le cryptage des mots de passe avant leurs stockages,mais un mécanisme de décryptage pourrait vous exposer a une

faille de sécurité. Une alternative plus sûr est de stocker le hash code du nom utilisateur et de son mot de passe

comme mot passe de sécurité.Dans cette article, je vous montre un processus utilisant le package DBMS_OBFUSCATION_TOOLKIT

qui est disponible sur Oracle9i ou bien en sha_1 avec Oracle 10

Premiérement on va crée une table pour sauvgarder le nom utilisateur et sont mot de passe.

Example

> SQLPLUS scott/tiger 


Oracle Database 10g Release 10.2.0.1.0 - Production

SQL>  SET LINESIZE 130
 SQL>  CREATE TABLE demo_users (
      id_user    NUMBER(12)    NOT NULL,
      username  VARCHAR2(128)  NOT NULL,
      password  VARCHAR2(128)  NOT NULL
    );
    
SQL> Table created.

SQL>ALTER TABLE demo_users ADD (
      CONSTRAINT id_users_pk PRIMARY KEY (id_user)
    );
    
SQL> Table altered.
	
SQL>ALTER TABLE demo_users ADD (
      CONSTRAINT users_name_uk UNIQUE (username)
    );
	
SQL> Table altered.
	
SQL>CREATE SEQUENCE demo_users_seq;

SQL> sequence created.



--Création du package pour la sécurisation des informations des utilisateur
SQL>  CREATE OR REPLACE PACKAGE demo_user_security AS

    FUNCTION GET_HASH   (p_username  IN  VARCHAR2,
                         p_password  IN  VARCHAR2)
        RETURN VARCHAR2;
        
    PROCEDURE add_user   (p_username  IN  VARCHAR2,
                          p_password  IN  VARCHAR2);

    PROCEDURE change_password   (p_username      IN  VARCHAR2,
                                 p_old_password  IN  VARCHAR2,
                                 p_new_password  IN  VARCHAR2);

    PROCEDURE valid_user   (p_username  IN  VARCHAR2,
                            p_password  IN  VARCHAR2);

    FUNCTION valid_user   (p_username  IN  VARCHAR2,
                           p_password  IN  VARCHAR2)
        RETURN BOOLEAN;

    END;
 
 SQL> package created.
  
SQL>CREATE OR REPLACE PACKAGE BODY demo_user_security AS

      FUNCTION GET_HASH (p_username  IN  VARCHAR2,
                         p_password  IN  VARCHAR2)
        RETURN VARCHAR2 AS
        v_secur VARCHAR2(30) := 'Test';
      BEGIN
        -- Pre Oracle 10g
        RETURN DBMS_OBFUSCATION_TOOLKIT.MD5(
          input_string => UPPER(p_username) || v_secur || UPPER(p_password));

        -- Oracle 10g+ : Require EXECUTE on DBMS_CRYPTO
        --RETURN DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(UPPER(p_username) 
		--|| v_secur || UPPER(p_password)),DBMS_CRYPTO.HASH_SH1);
      END;

      PROCEDURE add_user (p_username  IN  VARCHAR2,
                          p_password  IN  VARCHAR2) AS
      BEGIN
        INSERT INTO demo_users (
          id_user,
          username,
          password
        )
        VALUES (
          demo_users_seq.NEXTVAL,
          UPPER(p_username),
          GET_HASH(p_username, p_password)
        );
        
        COMMIT;
      END;
       
      PROCEDURE change_password (p_username      IN  VARCHAR2,
                                 p_old_password  IN  VARCHAR2,
                                 p_new_password  IN  VARCHAR2) AS
        v_rowid  ROWID;
      BEGIN
        SELECT rowid
        INTO   v_rowid
        FROM   demo_users
        WHERE  username = UPPER(p_username)
        AND    password = get_hash(p_username, p_old_password)
        FOR UPDATE;
        
        UPDATE demo_users
        SET    password = get_hash(p_username, p_new_password)
        WHERE  rowid    = v_rowid;
        
        COMMIT;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR(-20010, 'nom utilisateur/mot de passe incorrect.');
      END;

      PROCEDURE valid_user (p_username  IN  VARCHAR2,
                            p_password  IN  VARCHAR2) AS
        v_numy  VARCHAR2(1);
      BEGIN
        SELECT '1'
        INTO   v_numy
        FROM   demo_users
        WHERE  username = UPPER(p_username)
        AND    password = get_hash(p_username, p_password);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          RAISE_APPLICATION_ERROR(-20000, 'nom utilisateur/mot de passe incorrect.');
      END;
      
      FUNCTION valid_user (p_username  IN  VARCHAR2,
                           p_password  IN  VARCHAR2) 
        RETURN BOOLEAN AS
      BEGIN
        valid_user(p_username, p_password);
        RETURN TRUE;
      EXCEPTION
        WHEN OTHERS THEN
          RETURN FALSE;
      END;
      
    END;
    /
SQL> package body created.

La surcharge de VALID_USER permet un contrôle de sécurité de façon différente.La fonction de GET_HASH est

utilisée pour hacher la combinaison du nom d'utilisateur et du mot de passe. Il rend toujours un VARCHAR2

indépendamment de la longueur des paramètres de saisie.DBMS_OBFUSCATION_TOOLKIT.MD5 permet de vous générer un

hash code en MD5.



--Exemple:
--Création des utilisateurs

SQL> exec demo_user_security.add_user('Smith','secret');
     
    PL/SQL procedure successfully completed.
   
SQL> exec demo_user_security.add_user('William','terces121');
     PL/SQL procedure successfully completed.
SQL> select * from demo_users;
    ID      USERNAME                 PASSWORD
 ----------- -----------------------------------------------
    1       Smith                   ãXFõˆC„®W3–E
    2       William                 b¾õmûMÀ*æ„¶}ˆ

--Ensuite on va vérifier la procédure VALID_USER		


SQL> EXEC demo_user_security.valid_user('Smith','secret');

    PL/SQL procedure successfully completed.

SQL> EXEC demo_user_security.valid_user('William','bblsld');
    BEGIN app_user_security.valid_user('william','bblsld'); END;

    *
    ERROR at line 1:
    ORA-20000: nom utilisateur/mot de passe incorrect.
    ORA-06512: at "W2K1.DEMO_USER_SECURITY", line 66
    ORA-06512: at line 1

--Ensuite on vérifiéra la fonction VALID_USER.

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
      2    IF demo_user_security.valid_user('Smith','secret') THEN
      3      DBMS_OUTPUT.PUT_LINE('TRUE');
      4    ELSE
      5      DBMS_OUTPUT.PUT_LINE('FALSE');
      6    END IF;
      7  END;
      8  /
    TRUE

    PL/SQL procedure successfully completed.

SQL> BEGIN
      2    IF demo_user_security.valid_user('William','bblsld') THEN
      3      DBMS_OUTPUT.PUT_LINE('TRUE');
      4    ELSE
      5      DBMS_OUTPUT.PUT_LINE('FALSE');
      6    END IF;
      7  END;
      8  /
    FALSE

    PL/SQL procedure successfully completed.

SQL>

--Au final on vérifiéra la procédure CHANGE_PASSWORD.

SQL> exec demo_user_security.change_password('Smith','secret','tresect');

    PL/SQL procedure successfully completed.

SQL> exec demo_user_security.change_password('William','vfrdtg','vcftg12');
    BEGIN app_user_security.change_password('William','vfrdtg','vfrd14g'); END;

    *
    ERROR at line 1:
    ORA-20000:  nom utilisateur/mot de passe incorrect.
    ORA-06512: at "W2K1.DEMO_USER_SECURITY", line 52
    ORA-06512: at line 1

Friday 27 April 2012

CLOB Oracle

CLOB ORACLE

cette procédure vous permettra de parser un contenu clob de type script SQL

Ensuite, elle va éxécuter le contenu SQL, par le bias du

package DBMS_SQL. On peut aussi subsitutuer le contenu du clob en un

ensemble d'instruction et utilisé : EXECUTE IMMEDIATE.

dans cette exemple, je vous montre la première option.

declare
    v_sql  CLOB;
     v_num        NUMBER := 0;
    v_upperbound NUMBER;
      v_sql        DBMS_SQL.VARCHAR2S;
      v_cur        INTEGER;
      v_ret        NUMBER;
    begin
     -- Build a very large SQL statement in the CLOB
    LOOP
    IF  v_num = 0 THEN
         v_sql := 'CREATE VIEW vw_tmp AS SELECT ''le nombre de ligne est : 
'||to_char(v_num,'fm0999999')||''' as col1 FROM DUAL'; 
ELSE
         v_sql := v_sql || ' UNION ALL SELECT ''Le nombre de ligne est :
'||to_char(v_num,'fm0999999')||''' as col1 FROM DUAL';
 END IF;
      v_num := v_num + 1;
       EXIT WHEN DBMS_LOB.GETLENGTH(v_sql) > 40000 OR v_num > 800;
     END LOOP;
    DBMS_OUTPUT.PUT_LINE('Length:'||DBMS_LOB.GETLENGTH(v_sql));
     DBMS_OUTPUT.PUT_LINE('Num:'||v_num);
     -- décomposer le clob en bloc de 256 caractères et les mètres dans un tableau VARCHAR2S
    
     v_upperbound := CEIL(DBMS_LOB.GETLENGTH(v_sql)/256);
     FOR i IN 1..v_upperbound
     LOOP
       v_sql(i) := DBMS_LOB.SUBSTR(v_sql,256 -- amount
                                        	  ,((i-1)*256)+1 -- offset
                                  );
     END LOOP;
     --
     --Parsé puis éxécuter le script sql
     v_cur := DBMS_SQL.OPEN_CURSOR;
     DBMS_SQL.PARSE(v_cur, v_sql, 1, v_upperbound, FALSE, DBMS_SQL.NATIVE);
     v_ret := DBMS_SQL.EXECUTE(v_cur);
     DBMS_OUTPUT.PUT_LINE('View Created');
  end;

Sunday 15 January 2012

Merge sur Oracle

Oracle: la commande Merge

Merge help you to update and insert a new record with the same instruction.
the following present the syntax of it.

MERGE INTO Table1 T1
USING (SELECT Id, fields FROM Table2) T2
ON ( T1.Id = T2.Id ) -- Matching condition
WHEN MATCHED THEN -- true
UPDATE SET T1.fields = T2.fields --many fields with separator (,)
WHEN NOT MATCHED THEN -- false
INSERT (T1.ID, T1.fields) VALUES ( T2.ID, T2.fields);

Example

>SQLPLUS scott/tiger


Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> SET LINESIZE 130
SQL>   CREATE Table product (
  2  Id Number (10),
  3  Ref VARCHAR2 (16),
  4  Price NUMBER (12,2));
SQL> Table created
SQL>CREATE SEQUENCE Seq_Id_Pro START WITH 1 INCREMENT BY 1;

SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '001',    5.50);
SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '002',  3.5);
SQL>INSERT INTO product VALUES (Seq_Id_Pro.NextVal, '004',    5.99);

SQL>COMMIT;

--the second table
SQL>  
CREATE Table Temp_Product (
2 Ref VARCHAR2 (16),
3 Price NUMBER (12,2));
SQL> Table created
SQL>INSERT INTO Temp_Product VALUES ('001', 6.99);
SQL>INSERT INTO Temp_Product VALUES ('002',2.5);
SQL>INSERT INTO Temp_Product VALUES ('003', 2.99);
SQL>INSERT INTO Temp_Product VALUES ('004', 5.5);
SQL>INSERT INTO Temp_Product VALUES ('005', 4.9);
SQL>COMMIT;
  
  
SQL>   MERGE INTO Product P
    2  USING (SELECT Ref, price FROM Temp_Product) T
    3   ON (P.Ref = T.Ref)
    4  WHEN MATCHED THEN 
    5  UPDATE SET P.Price = T.Price -- you can other fields 
    6 WHEN NOT MATCHED THEN 
    7  INSERT (P.Id, P.Ref,  P.Price) VALUES (Seq_Id_Pro.NextVal, T.Ref, T.Price);
  
SQL>5 records matched.

--when you do select on table product you will find update of the
   price and a new records inserted.
;

SQL>select * from product;

        ID REF                   PRICE
---------- ---------------- ----------
         1 001                    6,99
         2 004                     5,5
         3 002                     2,5
         7 003                    2,99
         8 005                     4,9


--know we have upadate and new records at the same times.

Friday 13 July 2012

Import export d'un CLOB

1- Export d'un CLOB

2- Import d'un CLOB

Wednesday 11 July 2012

Cryptage de mot de passe (Storing Passwords in an Oracle Database)

Friday 27 April 2012

CLOB Oracle

Sunday 15 January 2012

Merge sur Oracle

MERGE INTO Table1 T1 USING (SELECT Id, fields FROM Table2) T2 ON ( T1.Id = T2.Id ) -- Matching condition WHEN MATCHED THEN -- true UPDATE SET T1.fields = T2.fields --many fields with separator (,) WHEN NOT MATCHED THEN -- false INSERT (T1.ID, T1.fields) VALUES ( T2.ID, T2.fields);

Example

MERGE INTO Table1 T1
USING (SELECT Id, fields FROM Table2) T2
ON ( T1.Id = T2.Id ) -- Matching condition
WHEN MATCHED THEN -- true
UPDATE SET T1.fields = T2.fields --many fields with separator (,)
WHEN NOT MATCHED THEN -- false
INSERT (T1.ID, T1.fields) VALUES ( T2.ID, T2.fields);