Oracle Database Discussions

Well i know this question is posted numerous times here but i m complete noob understanding the solution given on the net.

Let me introduce the entire scenario .
We have an application, which keeps on sending emails from an X employees account.

We are using Oracle 11g as database .But the problem is we don't know which table to search for the value.
All we have is Database credentials and x employee's name.
Is there any way we can find out which table contains the name so that we can change it accordingly.

I m using plsql developer to connect to the database.

One solution that we got from internet goes as below.
Ref:
[http://www.club-oracle.com/articles/oracle-find-all-tables-with-column-value-154/ |http://www.club-oracle.com/articles/oracle-find-all-tables-with-column-value-154/ ]


As per the solution i had to create a procedure as below .

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);
type rc is ref cursor;
l_cursor rc;
begin
dbms_application_info.set_client_info( '%' || upper(p_str) || '%' );

for x in (select * from user_tables )
loop
l_query := 'select distinct ''' || x.table_name || ''', $$
from ' || x.table_name || '
where ( 1=0 ';
l_runquery := FALSE;
l_case := NULL;
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 || '||'' ''|| case when upper(' || y.column_name ||
') like userenv(''client_info'') then ''' ||
y.column_name || ''' else NULL end';
end loop;
if ( l_runquery )
then
l_query := replace( l_query, '$$', substr(l_case,8) ) || ')';
begin
open l_cursor for l_query;
loop
fetch l_cursor into l_tname, l_cname;
exit when l_cursor%notfound;
dbms_output.put_line
( 'Found in ' || l_tname || '.' || l_cname );
end loop;
close l_cursor;
end;
end if;

end loop;
end;
/

Well i have copied the code to sql window in plsql and executed .But whenever i compile the procedure it says there is an error .

Not sure what kind of error hidden in the procedure.

If any 1 has any better idea please share.

Edited by: Milind on Apr 12, 2012 8:45 PM