Hi Guys, it's Xev.
I have been struggling with this for weeks now. No matter what I do, I cannot get my procedure to see dba_tab_columns inside of my procedure at run-time. It just blows up and says
"cannot see table or view", that generic answer.
Is there really a bug or limitation with how I am using this dba view? is there something else I can do? it simply will not recognize the other custom users also.
I have two basic lines of defense here, but both of them do not what I need them to do.
Can someone please help me on this.
Ok, this is the "first method" that I have tried to use, it works great, but "only" for one user at a time. I cannot get it to "search" all the rest of the "user schema's". It finds what I tell it to,
but only in the schema, tables and fields in the user that is executing it. I've tried to switch out (user_tab_columns) for dba_tab_columns, but it blows up and tells me that it "cannot see the table or view".
This is my preferred method. If you can alter this to make it find other other users in other schema's through-out the entire database, I would do anything for you!!!
create or replace procedure find_str
authid current_user
as
l_query long;
l_case long;
l_runquery boolean;
l_tname varchar2(30);
l_cname varchar2(4000);
l_refcur sys_refcursor;
z_str varchar2(4000);
begin
z_str := '^[0-9]{9}$';
dbms_output.enable (buffer_size => NULL);
dbms_application_info.set_client_info (z_str);
dbms_output.put_line ('Searchword Table Column/Value');
dbms_output.put_line ('---------------------------- ------------------------------ --------------------------------------------------');
for x in (select distinct table_name from all_tables
where owner not in ('SYS','SYSTEM','MDSYS','OUTLN','CTXSYS','OLAPSYS','OWBSYS','FLOWS_FILES','EXFSYS','SCOTT',
'APEX_030200','DBSNMP','ORDSYS','SYSMAN','APPQOSSYS','XDB','ORDDATA','WMSYS'))
loop
l_query := 'select ''' || x.table_name || ''', $$
from ' || x.table_name || '
where 1 = 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 regexp_like (' ||
y.column_name || ', userenv(''client_info'')) ';
l_case := l_case || ' when regexp_like (' ||
y.column_name || ', userenv(''client_info'')) then ' ||
'''<' || y.column_name || '>''||' || y.column_name || '||''</' || y.column_name || '>''';
end loop;
if ( l_runquery )
then
l_case := l_case || ' else NULL end';
l_query := replace( l_query, '$$', l_case ) || ')';
begin
open l_refcur for l_query;
loop
fetch l_refcur into l_tname, l_cname;
exit when l_refcur%notfound;
dbms_output.put_line
(rpad (z_str, 29) ||
rpad (l_tname, 31) ||
rpad (l_cname, 50));
end loop;
exception
when no_data_found then null;
end;
end if;
end loop;
end find_str;
NOW,
This is the second method, it also does a good job finding what i want it to, but still doesn't search the other users and other schema's. If you can alter this to make it find other users and other schema's I'll go crazy! LOL!
For test data simply create a table in your schema and put a "nine digit" number anywhere in the fields and both of these procedures will find them, but only for that "USER".
AND, that's my problem, I have to many custom user's to go on the instances and create procedures for each and every user. it's just not practical.
I really need you guys on this, Happy New Year!
create or replace PROCEDURE find_string
--(p_search_string IN VARCHAR2 DEFAULT '^[0-9]{3}-[0-9]{2}-[0-9]{4}$')
(p_search_string IN VARCHAR2 DEFAULT '^[0-9]{9}$')
IS
e_error_in_xml_processing EXCEPTION;
e_table_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT (e_error_in_xml_processing, -19202);
PRAGMA EXCEPTION_INIT (e_table_not_exist, -942);
BEGIN
DBMS_OUTPUT.PUT_LINE ('Searchword Table Column/Value');
DBMS_OUTPUT.PUT_LINE ('---------------------------- ------------------------------ --------------------------------------------------');
FOR r1 IN
(SELECT table_name, column_name
FROM dba_tab_cols
WHERE table_name IN (select distinct table_name from dba_tab_cols
where owner not in ('MDSYS','OUTLN','CTXSYS','OLAPSYS','FLOWS_FILES','OWBSYS','SYSTEM','EXFSYS','APEX_030200','SCOTT','DBSNMP','ORDSYS','SYSMAN','
APPQOSSYS','XDB','ORDDATA','SYS','WMSYS'))
--WHERE table_name = 'FIND_TEST'
ORDER BY table_name, column_name)
LOOP
BEGIN
FOR r2 IN
(SELECT DISTINCT SUBSTR (p_search_string, 1, 28) "Searchword",
SUBSTR (r1.table_name, 1, 30) "Table",
SUBSTR (t.column_value.getstringval (), 1, 50) "Column/Value"
FROM TABLE
(XMLSEQUENCE
(DBMS_XMLGEN.GETXMLTYPE
( 'SELECT "' || r1.column_name ||
'" FROM "' || r1.table_name ||
'" WHERE REGEXP_LIKE
("' || r1.column_name || '",'''
|| p_search_string || ''')'
).extract ('ROWSET/ROW/*'))) t)
LOOP
DBMS_OUTPUT.PUT_LINE
(RPAD (r2."Searchword", 29) ||
RPAD (r2."Table", 31) ||
RPAD (r2."Column/Value", 50));
END LOOP;
EXCEPTION
WHEN e_error_in_xml_processing THEN NULL;
WHEN e_table_not_exist THEN NULL;
WHEN OTHERS THEN RAISE;
END;
END LOOP;
END find_string;
Happy New Year, if you can get this to find other users!!! GOOD LUCK!