Pour trier des numériques, des alphabétiques et des alphanumériques. CREATE TABLE TOTO(col VARCHAR2(10)); INSERT INTO TOTO VALUES (’0′); INSERT INTO TOTO VALUES (’10′); INSERT INTO TOTO VALUES (’100′); INSERT INTO TOTO VALUES (’11′); INSERT INTO TOTO VALUES (‘TOTO’); INSERT INTO TOTO VALUES (‘TITI’); INSERT INTO TOTO VALUES (‘T’); COMMIT; SELECT col , DECODE(LENGTH(REPLACE(TRANSLATE(col, ’0123456789′, ’0000000000′), [...]
Category Archives: Informatique
Vérifier le mot de passe
Posted by DiabloCorps on 12 octobre 2010
0 comments
Oracle permet la vérification de mot de passe (longueur, contenu et complexité).
password_verify_function
ALTER PROFILE developer LIMIT
password_verify_function uw_pwd_verification;
BULK COLLECT 1
Posted by DiabloCorps on 12 octobre 2010
0 comments
DECLARE TYPE TAB_NODE IS TABLE OF BSS_Node.Nod_Key%TYPE INDEX BY BINARY_INTEGER; TYPE TAB_TYPE_NODE IS TABLE OF BSS_Node.Nod_Typ_Key%TYPE INDEX BY BINARY_INTEGER; TabZone TAB_NODE; TabZoneTyp TAB_TYPE_NODE; BEGIN DELETE &3 WHERE Nod_Key IN ( SELECT Nod_Key FROM ( SELECT Nod_Key, COUNT(Nod_Key) FROM &3 GROUP BY Nod_Key HAVING COUNT(Nod_Key) > 1 ) ) RETURNING Nod_Key, Nod_Typ_Key BULK COLLECT INTO [...]
BULK COLLECT 2
Posted by DiabloCorps on 12 octobre 2010
0 comments
PROCEDURE lancer IS L_FunctionException EXCEPTION; TYPE myArray IS TABLE OF BDE_RNC.Nod_Key%TYPE; l_data myarray; CURSOR Noeuds IS SELECT Nod_Key FROM BDE_RNC; BEGIN OPEN Noeuds; LOOP — Recherche des hiérarchies — FETCH Noeuds BULK COLLECT INTO l_data LIMIT 100; FORALL i IN 1..l_data.COUNT UPDATE BDE_RNC SET NIDTSGSN = ( SELECT WT_SGSN.Nod_NIDT FROM WT_SGSN , ( SELECT BSS_Arc.Nod_Upper_Key [...]
Calculer les statistiques
Posted by DiabloCorps on 12 octobre 2010
0 comments
Pour calculer les statistiques d’un schéma :
SQL> EXEC DBMS_STATS.Gather_Schema_Stats(‘SCOTT’);
Pour calculer les statistiques d’une base :
SQL> DBMS_STATS.Gather_Database_Stats;