Keyword Search in Oracle Tables
In the Oracle Database, we can easily find a text or keyword that we are looking for in tables within a schema. Read to learn the steps of the process.
By
·
·
Tutorial
Join the DZone community and get the full member experience.
Join For FreeIn the Oracle Database, we can easily find a text or keyword that we are looking for in tables within a schema. As we can search all tables in the schema, we can also search within a desired table belonging to that schema.
As shown in the PL/SQL block below, the text/keyword that we will search is carried out over the varchar columns of the related tables.
- First we specify the "p_search_text" --> constant text to be searched in the DECLARATION section of the PL/SQL block.
- In the bottom line, we specify the SCHEMA NAME as a constant.
- In the next line, we specify TABLE NAME.
- When we pass the TABLE NAME field to null, we search for all tables in that schema.
- If we pass TABLE NAME full, we should pay attention to the table in that schema. Otherwise, no record will be found for our search.
After entering our definitions as above, we can run the PL/SQL block below via Oracle SQL Developer or Toad.
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 || '/' || 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
|| '/'
|| 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%'*/Result Section:
- If the keyword/text we search is found in the relevant tables, the name of the found table and the name of the found column, how many, and the query information are written.
- At the bottom is the general total information. We can see the total number of tables searched and how many of these tables are found.
Plain Text
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
Database
Opinions expressed by DZone contributors are their own.
Comments