Hi Team,
For our requirement as part of the sample data , we can use our scott.emp table for reference.
The requirement is how to find a particular string value in the entire DB schema/user.
I would like to inform our friends that i found the below code snippet on Google , but I could not get the correct output .
Sample Code # 1
SQL> variable val varchar2(10)
SQL> exec :val := 'KING'
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
KING EMP ENAME
The above snippet works for smaller tables present in scott user.
But it does not work for our project related tables.
And we are getting the below exception message :
There is another method to over come the above issue and below is the code snippet found on our Forum , but its execution process is very slow.
Sample Code #2
DECLARE
ncount NUMBER;
vwhere VARCHAR2(1000) := '';
vselect VARCHAR2(1000) := ' select count(1) from ';
vsearchstr VARCHAR2(1000) := 'abc@abc.com';
BEGIN
FOR k IN (SELECT a.table_name
,a.column_name
FROM user_tab_cols a
WHERE a.data_type LIKE '%VARCHAR%')
LOOP
vwhere := ' where ' || k.column_name || ' = :vsearchstr ';
EXECUTE IMMEDIATE vselect || k.table_name || vwhere
INTO ncount
USING vsearchstr;
IF (ncount > 0)
THEN
dbms_output.put_line(k.column_name || ' ' || k.table_name);
END IF;
END LOOP;
END;
Any recommendations please with a sample code to overcome this issue please.
Regards,
Satyam Reddy
Note : There was a small Edit in the subject . Replaced string with Varchar