Home » SQL & PL/SQL » SQL & PL/SQL » How do I find a value anywhere in a Oracle schema ? (12c)
| How do I find a value anywhere in a Oracle schema ? [message #647038] |
Mon, 18 January 2016 11:12  |
 |
gorants
Messages: 85
Registered: May 2014
Location: ATL
|
Member |
|
|
Hell experts,
How you all doing. i have question How do I find a value anywhere in a Oracle schema ?. when debugging issues sometimes i dont know where the value is being pulled from ..
is there any useful db query that helps to search database based on given values.
Appreciate your suggestions.
i found this query in internet but running into issues
SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
SUBSTR (table_name, 1, 14) "Table",
SUBSTR (column_name, 1, 14) "Column"
FROM cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
|| column_name
|| ' from '
|| table_name
|| ' where upper('
|| column_name
|| ') like upper(''%'
|| :val
|| '%'')' ).extract ('ROWSET/ROW/*') ) ) t
ORDER BY "Table"
/
Errors
ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected CHAR got BLOB
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
thanks,
[Updated on: Mon, 18 January 2016 11:12] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: How do I find a value anywhere in a Oracle schema ? [message #647050 is a reply to message #647038] |
Mon, 18 January 2016 12:48  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The first method that you tried was getting the error that you got because it was trying to analyze BLOB data. You can search only certain data types, like CHAR or VARCHAR2, by modifying the code as shown below. I found it necessary to use both a WITH clause and a MATERIALIZE hint to get it to materialize the limited subset and not use a plan that will still try to access other data types. I also modified the code to put double quotes around the table and column names to avoid problems with any names that were created that way. Please try the following code and let us know if it works for you or not.
WITH char_cols AS
(SELECT /*+materialize */ table_name, column_name
FROM cols
WHERE data_type IN ('CHAR', 'VARCHAR2'))
SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
SUBSTR (table_name, 1, 14) "Table",
SUBSTR (column_name, 1, 14) "Column"
FROM char_cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
|| column_name
|| '" from "'
|| table_name
|| '" where upper("'
|| column_name
|| '") like upper(''%'
|| :val
|| '%'')' ).extract ('ROWSET/ROW/*') ) ) t
ORDER BY "Table"
/
The following is an example of execution of the code above:
SCOTT@orcl> COLUMN "Searchord" FORMAT A11
SCOTT@orcl> COLUMN "Table" FORMAT A14
SCOTT@orcl> COLUMN "Column" FORMAT A14
SCOTT@orcl> VARIABLE val VARCHAR2(100)
SCOTT@orcl> EXEC :val := 'CLERK'
PL/SQL procedure successfully completed.
SCOTT@orcl> WITH char_cols AS
2 (SELECT /*+materialize */ table_name, column_name
3 FROM cols
4 WHERE data_type IN ('CHAR', 'VARCHAR2'))
5 SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
6 SUBSTR (table_name, 1, 14) "Table",
7 SUBSTR (column_name, 1, 14) "Column"
8 FROM char_cols,
9 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
10 || column_name
11 || '" from "'
12 || table_name
13 || '" where upper("'
14 || column_name
15 || '") like upper(''%'
16 || :val
17 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
18 ORDER BY "Table"
19 /
Searchword Table Column
-------------------------------------------- -------------- --------------
CLERK EMP JOB
1 row selected.
[Updated on: Mon, 18 January 2016 12:54] Report message to a moderator |
|
|
|
| Re: How do I find a value anywhere in a Oracle schema ? [message #647052 is a reply to message #647046] |
Mon, 18 January 2016 13:08 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
For the second method that you tried, I think you accidentally altered something while removing the line numbers. Also, it does not have double quotes around the table and column names. Please try the modified code below and let us know if it works for you or not.
create or replace
procedure find_string( p_str in varchar2 )
authid current_user
as
l_query long;
l_case long;
l_runquery boolean;
l_tname varchar2(30);
l_cname varchar2(30);
begin
dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
for x in (select * from user_tables )
loop
l_query := 'select ''' || x.table_name || ''', $$
from "' || x.table_name || '"
where rownum = 1 and ( 1=0 ';
l_case := 'case ';
l_runquery := FALSE;
for y in ( select *
from user_tab_columns
where table_name = x.table_name
and data_type in ( 'VARCHAR2', 'CHAR' )
)
loop
l_runquery := TRUE;
l_query := l_query || ' or upper("' || y.column_name ||
'") like userenv(''client_info'') ';
l_case := l_case || ' when upper("' || y.column_name ||
'") like userenv(''client_info'') then ''' ||
y.column_name || '''';
end loop;
if ( l_runquery )
then
l_case := l_case || ' else NULL end';
l_query := replace( l_query, '$$', l_case ) || ')';
begin
execute immediate l_query into l_tname, l_cname;
dbms_output.put_line
( 'Found in ' || l_tname || '.' || l_cname );
exception
when no_data_found then
dbms_output.put_line
( 'No hits in ' || x.table_name );
end;
end if;
end loop;
end;
/
The following is an example of execution of the code above.
SCOTT@orcl> create or replace
2 procedure find_string( p_str in varchar2 )
3 authid current_user
4 as
5 l_query long;
6 l_case long;
7 l_runquery boolean;
8 l_tname varchar2(30);
9 l_cname varchar2(30);
10 begin
11 dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );
12
13 for x in (select * from user_tables )
14 loop
15 l_query := 'select ''' || x.table_name || ''', $$
16 from "' || x.table_name || '"
17 where rownum = 1 and ( 1=0 ';
18 l_case := 'case ';
19 l_runquery := FALSE;
20 for y in ( select *
21 from user_tab_columns
22 where table_name = x.table_name
23 and data_type in ( 'VARCHAR2', 'CHAR' )
24 )
25 loop
26 l_runquery := TRUE;
27 l_query := l_query || ' or upper("' || y.column_name ||
28 '") like userenv(''client_info'') ';
29 l_case := l_case || ' when upper("' || y.column_name ||
30 '") like userenv(''client_info'') then ''' ||
31 y.column_name || '''';
32 end loop;
33 if ( l_runquery )
34 then
35 l_case := l_case || ' else NULL end';
36 l_query := replace( l_query, '$$', l_case ) || ')';
37 begin
38 execute immediate l_query into l_tname, l_cname;
39 dbms_output.put_line
40 ( 'Found in ' || l_tname || '.' || l_cname );
41 exception
42 when no_data_found then
43 dbms_output.put_line
44 ( 'No hits in ' || x.table_name );
45 end;
46 end if;
47
48 end loop;
49 end;
50 /
Procedure created.
SCOTT@orcl> show errors
No errors.
SCOTT@orcl> exec find_string ('CLERK')
No hits in BONUS
Found in EMP.JOB
No hits in DEPT
PL/SQL procedure successfully completed.
|
|
|
|
| Re: How do I find a value anywhere in a Oracle schema ? [message #647056 is a reply to message #647050] |
Mon, 18 January 2016 13:47 |
|
gorants
Messages: 85
Registered: May 2014
Location: ATL
|
Member |
|
|
i am seeing this error in my environment.
SCOTT@orcl> WITH char_cols AS
2 (SELECT /*+materialize */ table_name, column_name
3 FROM cols
4 WHERE data_type IN ('CHAR', 'VARCHAR2'))
5 SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
6 SUBSTR (table_name, 1, 14) "Table",
7 SUBSTR (column_name, 1, 14) "Column"
8 FROM char_cols,
9 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
10 || column_name
11 || '" from "'
12 || table_name
13 || '" where upper("'
14 || column_name
15 || '") like upper(''%'
16 || :val
17 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
18 ORDER BY "Table"
19 /
ORA-31186: Document contains too many nodes
31186. 00000 - "Document contains too many nodes"
*Cause: Unable to load the document because it has exceeded
the maximum allocated number of DOM nodes.
*Action: Reduces the size of the document.
and for other approach
exec find_string('ASC')
Error report -
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "NIWA46.FIND_STRING", line 35
ORA-06512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
Any thing need to setup in my environment to work this
[Updated on: Mon, 18 January 2016 13:50] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: How do I find a value anywhere in a Oracle schema ? [message #647070 is a reply to message #647050] |
Mon, 18 January 2016 15:31 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Barbara, it is quite inefficient to select all rows from a table where column value matches and then use distinct. We can add rownum = 1 condition. Also, exctract is deprecated and search value may contain quotes:
variable column_value varchar2(4000)
exec :column_value := 'CLERK'
with t1 as (
select /*+ materialize */
table_name,
column_name
from cols
where data_type in ('CHAR','VARCHAR2')
),
t2 as (
select table_name,
column_name,
xmlcast(
xmlquery(
'/ROWSET/ROW'
passing dbms_xmlgen.getxmltype(
'select count(*) from "' || table_name ||
'" where "' || column_name || '" = ''' ||
replace(:column_value,q'[']',q'['']') ||
''' and rownum = 1'
)
returning content
)
as number
) flag
from t1
)
select table_name,
column_name,
:column_value column_value
from t2
where flag = 1
order by table_name,
column_name
/
TABLE_NAME COLUMN_NAME COLUMN_VALUE
----------- ------------ -------------
EMP JOB CLERK
EMP1 JOB CLERK
EMP_VW JOB CLERK
MV1 JOB CLERK
MV1_EMP JOB CLERK
V1 JOB CLERK
6 rows selected.
SQL>
Also cols includes tables and views and tables can be MV log tables, external tables IOT overflow tables, text index tables and many more OP might need to exclude.
SY.
[Updated on: Mon, 18 January 2016 15:32] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 01:12:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
