Please tell me how can I find all the values in Oracle database if I don't know the specific schema\table\column?
I am writing technical documentation and, for example, I want to know in which table and in which column is the value "Cat". How can i do this? A python loop over all columns and tables takes a very long time....
Well, anything will take a long time if you want to scan all tables and all columns (unless it's a tiny schema as Scott's).
Anyway: you'll need dynamic SQL. Here's an example: I'm searching through all CHAR-like datatype columns in my own schema (line #8), looking for KING (line #15):
SQL> DECLARE
2 l_str VARCHAR2 (500);
3 l_cnt NUMBER := 0;
4 BEGIN
5 FOR cur_r IN (SELECT u.table_name, u.column_name
6 FROM user_tab_columns u JOIN user_tables t
7 ON u.table_name = t.table_name
8 WHERE u.data_type LIKE '%CHAR%')
9 LOOP
10 l_str :=
11 'SELECT COUNT(*) FROM '
12 || cur_r.table_name
13 || ' WHERE '
14 || cur_r.column_name
15 || ' like (''%KING%'')';
16
17 EXECUTE IMMEDIATE (l_str)
18 INTO l_cnt;
19
20 IF l_cnt > 0
21 THEN
22 DBMS_OUTPUT.put_line (l_cnt || ' : ' || cur_r.table_name);
23 END IF;
24 END LOOP;
25 END;
26 /
1 : TABLE22
1 : EMP
PL/SQL procedure successfully completed.
SQL>
The result says that KING appears in two tables (TABLE22 and EMP), once in each of them.
