Vedremo come eseguire la ricerca di un testo contenuto in tutte le tabelle del database
Qual è lo scopo di oggi? Fare una ricerca all’interno del contenuto di tutte le tabelle che si trovano nel nostro database, perché a volte mi ritrovo al buio quando faccio delle analisi e alla fine scopro che il tassello mancante si trova all’interno di alcune tabelle parametriche che contengono le impostazioni che mi interessano.
Si tratta di una procedura laboriosa, in quanto si andrà a controllare il contenuto di tutte le colonne – di tutte le tabelle – di tutti gli owner del nostro database, percui consiglio di farla proprio quando credete che serve. Il database su cui lavoro è enorme, e il lancio di questa procedura impiega circa 4 ore per terminare la ricerca.
Per alleggerire la procedura (azione necessaria) andremo ad escludere tutte le tabelle di sistema e tutte le colonne che non contengono valori alfanumerici. Inoltre, visto il tempo necessario per l’esecuzione, andremo a memorizzare il risultato di ogni ricerca all’interno di una tabella così da andare a consultarla, quando ci serve, per una ricerca fatta da qualche giorno, senza dover lanciare ogni volta la procedura e perdere altro tempo.
Impostiamo l’ambiente per la ricerca
Il primo passaggio da fare è la creazione della sequence e della tabella di destinazione. Posizioniamoci sullo schema su cui vogliamo creare il tutto. Utilizzare questo script per la loro creazione/
-- Create sequence
create sequence NOME_SCHEMA.NOME_SEQUENCE
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
-- Create table
create table NOME_SCHEMA.NOME_TABELLA
(
id_search NUMBER,
text VARCHAR2(1000),
owner VARCHAR2(32),
table_name VARCHAR2(32),
column_name VARCHAR2(32),
dt_ricerca DATE
);facendo attenzione a sostituire il NOME_SCHEMA, NOME_SEQUENCE e NOME_TABELLA con i nomi che interessano a noi.
Dopo aver creato i due oggetti precedenti dobbiamo andare a creare la procedure che eseguirà la nostra ricerca, usando questo script
-- create procedure
CREATE OR REPLACE PROCEDURE NOME_PROCEDURE(text IN VARCHAR2) AS
cnt NUMBER;
query VARCHAR2(20000);
textup VARCHAR2(2000);
idsearch number;
vdt_dt_ricerca DATE;
lin_ricerca NOME_TABELLA%rowtype;
BEGIN
IF (text is not null) then
textup := UPPER(text);
idsearch := NOME_SEQUENCE.nextval;
vdt_dt_ricerca := sysdate;
DBMS_OUTPUT.PUT_LINE('Query per risultati: SELECT * FROM NOME_SCHEMA.NOME_TABELLA WHERE ID_SEARCH = '||idsearch);
FOR CLN IN (SELECT OWNER, TABLE_NAME, COLUMN_NAME
FROM ALL_TAB_COLS
WHERE OWNER NOT LIKE '%$%'
AND TABLE_NAME NOT LIKE '%$%'
AND COLUMN_NAME NOT LIKE '%$%'
AND data_type LIKE '%CHAR%') LOOP
cnt := 0;
BEGIN
query := 'SELECT COUNT(*) FROM ' || CLN.OWNER || '.' ||
CLN.TABLE_NAME || ' WHERE UPPER(TO_CHAR(' || CLN.COLUMN_NAME ||
')) LIKE ''%' || textup || '%''';
EXECUTE IMMEDIATE query
INTO cnt;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(CLN.OWNER||'.'||CLN.TABLE_NAME||'.'||CLN.COLUMN_NAME||': '||dbms_utility.format_error_backtrace);
END;
IF (cnt > 0) THEN
lin_ricerca.id_search := idsearch;
lin_ricerca.text := textup;
lin_ricerca.owner := cln.owner;
lin_ricerca.table_name := cln.table_name;
lin_ricerca.column_name:= cln.column_name;
lin_ricerca.dt_ricerca := vdt_dt_ricerca;
insert into NOME_TABELLA values lin_ricerca;
commit;
END IF;
END LOOP;
else
DBMS_OUTPUT.PUT_LINE('nessun input');
end if;
END;
facendo attenzione a sostituire anche qui il NOME_SCHEMA, NOME_SEQUENCE e NOME_TABELLA insieme al NOME_PROCEDURE.
Una volta compilato il tutto non ci resta che eseguire la ricerca.
Eseguiamo la ricerca
Attenzione: Queste sono istruzioni valide in PL/SQL, non ho Toad sotto mano per guidarvi nel suo utilizzo.
Aprire una finestra di Test Windows ed incollare il seguente script/
begin
-- Call the procedure
NOME_SCHEMA.NOME_PROCEDURA(text => :text);
end;compiliamo per fargli riconoscere l’input :text. A questo punto dobbiamo valorizzare quest’input con il valore che vogliamo cercare, e lanciare la procedura.
Alla fine della procedura di ricerca ci ritroveremo, nella scheda DBMS Output tutti gli errori che si sono verificati durante la ricerca, insieme alla query che dovete eseguire per visualizzare tutti i risultati.