Dans la base de données Oracle, nous pouvons facilement trouver un texte ou un mot-clé que nous recherchons dans les tables d’un schéma. Comme nous pouvons rechercher toutes les tables du schéma, nous pouvons également rechercher dans une table souhaitée appartenant à ce schéma.
Comme indiqué dans le bloc PL/SQL ci-dessous, le texte/mot-clé que nous allons rechercher est effectué sur les colonnes varchar des tables associées.
- Tout d’abord, nous spécifions le « p_search_text » –> texte constant à rechercher dans la section DECLARATION du bloc PL/SQL.
- Dans la ligne du bas, nous spécifions le SCHEMA NAME comme une constante.
- Dans la ligne suivante, nous spécifions TABLE NAME.
- Lorsque nous passons le champ TABLE NAME à null, nous recherchons toutes les tables de ce schéma.
- Si nous passons TABLE NAME full, nous devons faire attention à la table dans ce schéma. Sinon, aucun enregistrement ne sera trouvé pour notre recherche.
Après avoir entré nos définitions comme ci-dessus, nous pouvons exécuter le bloc PL/SQL ci-dessous via Oracle SQL Developer ou Toad.
‘ || ltr_table (i)); SELECT col.column_name BULK COLLECT INTO ltr_column_data FROM sys.dba_tab_columns col INNER JOIN sys.dba_tables t ON col.owner = t.owner AND col.table_name = t.table_name WHERE col.table_name = ltr_table (i) AND col.DATA_TYPE VARCHAR2’ ORDER BY col.column_id; FOR j IN 1 .. ltr_column_data.COUNT LOOP DBMS_APPLICATION_INFO.set_client_info ( j || « https://dzone.com/ » || ltr_column_data.COUNT || ‘ -> ‘ || ltr_column_data (j).column_name); l_sql := ‘SELECT /*+ PARALLEL (A 8)*/ count(1) from ‘ || p_schema_name || ‘.’ || ltr_table (i) || ‘ UN O ‘ || ltr_column_data (j).column_name || ‘ = »’ || p_search_text || » »; EXÉCUTER IMMÉDIAT l_sql INTO l_count ; SI l_count > 0 ALORS SI l_found = 0 ALORS DBMS_OUTPUT.put_line ( ‘Rechercher le mot-clé : ‘ || p_search_text); DBMS_OUTPUT.put_line ( ‘———————————————‘) ; FIN SI; DBMS_OUTPUT.put_line (‘nom de table trouvé : ‘||ltr_table (i) ); DBMS_OUTPUT.put_line (‘nom de colonne trouvé : ‘ || ltr_column_data (j).column_name); DBMS_OUTPUT.put_line (‘count : ‘ || l_count); DBMS_OUTPUT.put_line (‘nom sql : ‘ || l_sql); DBMS_OUTPUT.put_line (‘*****’); l_trouvé := l_trouvé + 1; FIN SI; FIN DE BOUCLE ; FIN DE BOUCLE ; DBMS_APPLICATION_INFO.set_module (NULL, NULL); DBMS_APPLICATION_INFO.set_client_info (NULL) ; DBMS_OUTPUT.put_line (‘——————————————–‘) ; DBMS_OUTPUT.put_line (‘nombre total de tables recherchées : ‘ || ltr_table.COUNT); DBMS_OUTPUT.put_line (‘nombre total de tables trouvées : ‘ || l_found); FINIR; /*select module,client_info from v$session où module comme ‘%PV_FINDER%’*/ » data-lang= »text/x-plsql »>
DECLARE
p_search_text CONSTANT VARCHAR2 (1000) := 'SAMPLE SEARCH WRITE';
p_schema_name CONSTANT VARCHAR2 (1000) := 'SAMPLE SCHEMA NAME';
p_table_name CONSTANT VARCHAR2 (1000) := 'SAMPLE TABLE NAME';-- NULL (If FULL will work, give the table name null)
TYPE r_column_data IS RECORD
(
column_name SYS.DBA_TAB_COLUMNS.COLUMN_NAME%TYPE
);
TYPE tr_column_data IS TABLE OF r_column_data
INDEX BY PLS_INTEGER;
ltr_column_data tr_column_data;
TYPE tr_table IS TABLE OF VARCHAR2 (200)
INDEX BY PLS_INTEGER;
l_sql VARCHAR2 (1000);
l_count NUMBER;
ltr_table tr_table;
l_found NUMBER := 0;
l_column_name VARCHAR2 (100);
BEGIN
SELECT table_name
BULK COLLECT INTO ltr_table
FROM dba_tables
WHERE owner = p_schema_name
AND table_name LIKE '' || p_table_name || '' || '%'
AND ROWNUM <= 19999
ORDER BY 1;
DBMS_APPLICATION_INFO.set_module ('PV_FINDER', NULL);
FOR i IN 1 .. ltr_table.COUNT
LOOP
DBMS_APPLICATION_INFO.set_client_info (
i || "https://dzone.com/" || ltr_table.COUNT || ' -> ' || ltr_table (i));
SELECT col.column_name
BULK COLLECT INTO ltr_column_data
FROM sys.dba_tab_columns col
INNER JOIN sys.dba_tables t
ON col.owner = t.owner AND col.table_name = t.table_name
WHERE col.table_name = ltr_table (i) AND col.DATA_TYPE = 'VARCHAR2'
ORDER BY col.column_id;
FOR j IN 1 .. ltr_column_data.COUNT
LOOP
DBMS_APPLICATION_INFO.set_client_info (
j
|| "https://dzone.com/"
|| ltr_column_data.COUNT
|| ' -> '
|| ltr_column_data (j).column_name);
l_sql :=
'SELECT /*+ PARALLEL (A 8)*/ count(1) from '
|| p_schema_name
|| '.'
|| ltr_table (i)
|| ' A WHERE '
|| ltr_column_data (j).column_name
|| ' ='''
|| p_search_text
|| '''';
EXECUTE IMMEDIATE l_sql INTO l_count;
IF l_count > 0
THEN
IF l_found = 0
THEN
DBMS_OUTPUT.put_line (
'Search Keyword: ' || p_search_text);
DBMS_OUTPUT.put_line (
'-------------------------------------------');
END IF;
DBMS_OUTPUT.put_line ('table name found : '||ltr_table (i) );
DBMS_OUTPUT.put_line ('column name found : ' || ltr_column_data (j).column_name);
DBMS_OUTPUT.put_line ('count : ' || l_count);
DBMS_OUTPUT.put_line ('sql name : ' || l_sql);
DBMS_OUTPUT.put_line ('*****');
l_found := l_found + 1;
END IF;
END LOOP;
END LOOP;
DBMS_APPLICATION_INFO.set_module (NULL, NULL);
DBMS_APPLICATION_INFO.set_client_info (NULL);
DBMS_OUTPUT.put_line ('-------------------------------------------');
DBMS_OUTPUT.put_line ('total number of tables searched : ' || ltr_table.COUNT);
DBMS_OUTPUT.put_line ('total number of tables found : ' || l_found);
END;
/*select module,client_info from v$session where module like '%PV_FINDER%'*/Section de résultat :
- Si le mot-clé/texte que nous recherchons est trouvé dans les tables pertinentes, le nom de la table trouvée et le nom de la colonne trouvée, le nombre et les informations de la requête sont écrits.
- En bas se trouvent les informations générales générales. Nous pouvons voir le nombre total de tables recherchées et combien de ces tables sont trouvées.
Search Keyword: SAMPLE SEARCH KEYWORD
-------------------------------------------
table name found : TABLE - 1
column name found : COLUMN NAME
count : 4
sql name : SELECT /*+ PARALLEL (A 8)*/ count(1) from SCHEMA_NAME.TABLE-1 A WHERE TABLE-1.COLUMN ='SAMPLE SEARCH KEYWORD'
*****
table name found : TABLE - 2
column name found : COLUMN NAME
count : 2
sql name : SELECT /*+ PARALLEL (A 8)*/ count(1) from SCHEMA_NAME.TABLE-2 A WHERE TABLE-1.COLUMN ='SAMPLE SEARCH KEYWORD'
*****
table name found : TABLE - 3
column name found : COLUMN NAME
count : 2
sql name : SELECT /*+ PARALLEL (A 8)*/ count(1) from SCHEMA_NAME.TABLE-3 A WHERE TABLE-1.COLUMN ='SAMPLE SEARCH KEYWORD'
*****
table name found : TABLE - 4
column name found : COLUMN NAME
count : 2
sql name : SELECT /*+ PARALLEL (A 8)*/ count(1) from SCHEMA_NAME.TABLE-4 A WHERE TABLE-1.COLUMN ='SAMPLE SEARCH KEYWORD'
*****
-------------------------------------------
total number of tables searched : 72
total number of tables found : 4