Requête utile
Liste de requête utiles pour un SGBD Oracle
-- Requêtes Utile
-- Modification Mot de passe utilisateur
-- On modifie l'utilisateur WIKI_PROD et on remplace le mdp DORP_IKIW par IKIW
ALTER USER WIKI_PROD IDENTIFIED BY IKIW REPLACE DORP_IKIW;
-- Inversement
ALTER USER WIKI_PROD IDENTIFIED BY DORP_IKIW REPLACE IKIW;
-- Débloquer le compte (se bloque au bout de 10 tentatives)
ALTER USER WIKI_PROD ACCOUNT UNLOCK;
-- Récuperer toutes les contraintes existantes pour une table
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = '<tablename>';
-- Inner join avec COUNT(*)
0 != (SELECT COUNT(*) FROM TABLE2 WHERE TABLE2.ID_PERSONNE = TABLE1.ID)
-- Hiérarchie des Entités
SELECT LPAD(ENTITY.TITLE_0, LENGTH(ENTITY.TITLE_0) + (LEVEL - 1) * 3, '+') AS Organigramme, ENTITY.ID, ENTITY.ID_ENTITY_FATHER1, ID_ENTITY_TYPE, LEVEL FROM ENTITY
CONNECT BY ENTITY.ID = PRIOR ENTITY.ID_ENTITY_FATHER1
START WITH ENTITY.ID = 7812
ORDER BY LEVEL DESC;
-- Récupération des informations de la BDD
SELECT
col.table_name,
col.column_name,
col.data_type,
col.data_length
FROM
sys.all_tab_columns col
INNER JOIN sys.all_tables t ON
col.owner = t.owner
AND col.table_name = t.table_name
WHERE
col.owner = '<schemaname>'
AND col.table_name = '<tablename>' -- optionnel
ORDER BY
col.table_name;
-- Parcours d'un XML avec condition
-- Si on effectue le traitement sur une colonne de type XMLTYPE le resultat pourra s'auto formater en XML (ne pas oublier d'enlever GetClobVal)
SELECT id, code,
XMLQuery(
' for $i in /Root/Options/Option
where $i/Item[@code="WHEN_COME_FROM_PREVIOUS"]
return $i/Item/Value/@value ' PASSING Xmltype(XML_PARAM) RETURNING CONTENT).GetClobVal() AS res
FROM process_step WHERE ID_PROCESS IN (SELECT id FROM PROCESS WHERE id IN (SELECT ID_PROCESS FROM TSESSION WHERE MO = 'CLASSIC' AND MODEL = '0'));
-- Procedure Avec 2 rqts
DECLARE
value NVARCHAR2(2000);
codeProcess NVARCHAR2(255);
c1 SYS_REFCURSOR;
CURSOR cursorData IS SELECT CODE FROM PROCESS WHERE CATEGORY = 'START_ON_STEP' AND TEMPLATE = 'EXPORT_PDF';
BEGIN
OPEN cursorData;
LOOP
FETCH cursorData
INTO codeProcess;
EXIT
WHEN cursorData%NOTFOUND;
value := 'XML_PARAM LIKE ''%"' || codeProcess || '"%'' OR ' || value;
END LOOP;
value := SUBSTR(value,1,LENGTH(value)-3);
CLOSE cursorData;
open c1 for 'SELECT ID, CODE, ID_PROCESS, MO, MODEL, TITLE_0 FROM TSESSION WHERE ID_PROCESS IN
(SELECT ID_PROCESS FROM PROCESS_STEP WHERE ' || value ||')
AND MO = ''CLASSIC'' AND MODEL = ''1''';
DBMS_SQL.RETURN_RESULT(c1);
END;
/
-- Procedure Avec 2 rqts
DECLARE
value NVARCHAR2(255);
res NVARCHAR2(255);
c1 SYS_REFCURSOR;
CURSOR cursorData IS SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME = 'ID_RESOURCE_IOF';
BEGIN
OPEN cursorData;
LOOP FETCH cursorData INTO value;
EXIT WHEN cursorData%NOTFOUND;
-- DBMS_OUTPUT.PUT_LINE(value);
open c1 for 'SELECT COUNT(*) FROM ' || value ||' WHERE ID_RESOURCE_IOF = 97';
LOOP FETCH c1 INTO res;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(res ||' ==> ' || value);
END LOOP;
CLOSE c1;
END LOOP;
CLOSE cursorData;
END;
-- Parcourir tous les schéma
DECLARE
value NVARCHAR2(255);
res NVARCHAR2(500);
c1 SYS_REFCURSOR;
CURSOR cursorData IS SELECT USERNAME FROM dba_users WHERE DEFAULT_TABLESPACE = 'USERS' AND ACCOUNT_STATUS = 'OPEN' AND USERNAME NOT IN ('ABSOLUT7', 'LVMH032022', 'LVMH_2020', 'PALATINE_PROD', 'RDR_2', 'SADE_L2', 'MAIFDEV', 'STD_DEMO', 'OTIS', 'CEBFC');
BEGIN
OPEN cursorData;
LOOP FETCH cursorData INTO value;
EXIT WHEN cursorData%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE(value);
DBMS_OUTPUT.PUT_LINE('----------- ' || value ||' --------------');
open c1 for 'SELECT URI_SAVE FROM ' || value ||'.UPLOAD WHERE ID_DOCUMENT IN (SELECT ID_DOCUMENT FROM ' || value ||'.DOC_FILES df WHERE ID_FILES IN (2,3,4))';
LOOP FETCH c1 INTO res;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( value || ' ==> ' || res);
END LOOP;
CLOSE c1;
END LOOP;
CLOSE cursorData;
END;
-- Récupère l'historique des requêtes avec leurs temps d'éxécution et la date d'éxécution
select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elapsed from v$sql
order by LAST_LOAD_TIME DESC;
-- ACTIVER/DESACTIVER UN TRIGGER
ALTER TRIGGER update_job_history DISABLE;
ALTER TRIGGER update_job_history ENABLE;
create or replace procedure correctObj(codeSession IN VARCHAR2) AS
evo NVARCHAR2(255);
value NVARCHAR2(255);
total NUMBER(38,0);
nbComp NUMBER(38,0);
resultat NUMBER(38,2);
idEvalue NVARCHAR2(255);
idSession NVARCHAR2(255);
c1 SYS_REFCURSOR;
CURSOR cursorData IS SELECT ID_USED FROM EVALUE
WHERE ID_SESSION = (SELECT ID FROM TSESSION WHERE code = codeSession)
AND CURRENT_STEP IN (SELECT CODE FROM PROCESS_STEP WHERE ID_PROCESS = (SELECT ID_PROCESS FROM TSESSION WHERE code = codeSession) AND ORDER_STEP NOT IN (1,7,8));
BEGIN
SELECT ID INTO idSession FROM TSESSION WHERE CODE = codeSession;
OPEN cursorData;
LOOP
FETCH cursorData INTO idEvalue;
EXIT WHEN cursorData%NOTFOUND;
-- Parcours des evalue + initialisation des vars
-- DBMS_OUTPUT.PUT_LINE(idEvalue);
total := 0;
nbComp := 0;
resultat := 0;
open c1 for 'SELECT DECODE(E7136, ''1'',3,''3'',1,''2'',2, NULL, 0) FROM VALEUR_GRP_ED_575 WHERE E7136 IS NOT NULL AND ID_SESSION = ' || idSession ||' AND ID_USED = ' || idEvalue;
LOOP FETCH c1 INTO value;
EXIT WHEN c1%NOTFOUND;
-- Parcours des objectifs de l'evalue
nbComp:= nbComp + 1;
total := total + value;
IF (nbComp != 0) THEN
resultat := total / nbComp;
END IF;
--DBMS_OUTPUT.PUT_LINE(value || ' TOTAL = ' || total || '/' || nbComp || ' = ' || resultat);
END LOOP;
CLOSE c1;
IF (resultat = 0) THEN
evo := 'NULL';
ELSIF (resultat <= 1.499) THEN
evo := '3';
ELSIF (resultat > 2.5) THEN
evo := '1';
ELSIF (resultat >= 1.5) THEN
evo := '2';
END IF;
EXECUTE IMMEDIATE 'UPDATE VALEUR_GRP_ED_598 SET E7821 = ' || evo || ' WHERE ID_USED = ' || idEvalue || ' AND ID_SESSION = ' || idSession;
END LOOP;
CLOSE cursorData;
END;
/
CALL correctObj('Leistung 2023');
/
DROP PROCEDURE correctObj;
