Oracle find string or number in the database
search stringfind text in database
set serveroutput on size 1000000
accept string char prompt "Enter Char String to search for:"
accept owner_name prompt "Enter Owner Name:"
set term off
set verify off
set lines 80
set feedback off
spool find_string.tmp
prompt spool find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In .."
prompt col string format a70 heading "String Found"
-- prompt col row_id format 99999999999999999999
prompt set term on
prompt
declare
cursor varchar_tables_cur IS
select owner
, table_name
, column_name
from dba_tab_columns
where owner != 'SYS'
and owner != 'SYSTEM'
and owner = upper('&OWNER_NAME')
and data_type in ( 'VARCHAR2','VARCHAR','CHAR');
begin
for v_t_rec in varchar_tables_cur loop
dbms_output.put_line('--');
-- dbms_output.put_line('prompt '||v_t_rec.owner||'.'||v_t_
dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_
dbms_output.new_line;
dbms_output.put(''||v_t_rec.
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_
dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||
dbms_output.put_line('/');
end loop;
end;
/
prompt spool off
prompt prompt ====> created file find_string.out for your review
spool off
@find_string.tmp
===
-- set term off
set verify off
set lines 80
set feedback off
set serveroutput on
spool find_string.tmp
prompt spool find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In .."
prompt col string format a70 heading "String Found"
-- prompt col row_id format 99999999999999999999
prompt set term on
prompt
declare
cursor varchar_tables_cur IS
select table_name,
column_name
from user_tab_columns a, user_objects b
where data_type in ( 'VARCHAR2','VARCHAR','CHAR')
and object_name=table_name
and object_type='TABLE' ;
-- and a.OWNER not in ('SYS','SYSTEM') ;
begin
for v_t_rec in varchar_tables_cur loop
dbms_output.put_line('--');
-- dbms_output.put_line('prompt '||v_t_rec.table_name||'.'||v_
dbms_output.put('select distinct'''||v_t_rec.table_
dbms_output.new_line;
dbms_output.put(''||v_t_rec.
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.table_name||'');
dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)
dbms_output.put_line('/');
end loop;
end;
/
prompt spool off
prompt prompt ====> created file find_string.out for your review
spool off
@find_string.tmp
===
Oracle search string from a database
-- set term off
set verify off
set lines 80
set feedback off
set serveroutput on
spool find_string.tmp
prompt spool find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In .."
prompt col string format a70 heading "String Found"
-- prompt col row_id format 99999999999999999999
prompt set term on
prompt
declare
cursor varchar_tables_cur IS
select table_name,a.owner owner, column_name
-- from user_tab_columns a, user_objects b
from dba_tab_columns a, dba_objects b
where data_type in ( 'VARCHAR2','VARCHAR','CHAR')
and object_name=table_name
and object_type='TABLE'
and a.OWNER not in ('SYS','SYSTEM') ;
begin
for v_t_rec in varchar_tables_cur loop
dbms_output.put_line('--');
-- dbms_output.put_line('prompt '||v_t_rec.table_name||'.'||v_
dbms_output.put('select distinct'''||v_t_rec.table_
dbms_output.new_line;
dbms_output.put(''||v_t_rec.
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_
dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'&string'||chr(39)
dbms_output.put_line('/');
end loop;
end;
/
prompt spool off
prompt prompt ====> created file find_string.out for your review
spool off
@find_string.tmp
====
set serveroutput on size 1000000
accept string char prompt "Enter Char String to search for:"
accept owner_name prompt "Enter Owner Name:"
set term off
set verify off
set lines 80
set feedback off
spool find_string.tmp
prompt spool c:\temp\find_string.out
prompt set feedback off
prompt set pages 1000
prompt set lines 132
prompt set wrap on
prompt break on found_in skip 1
prompt col found_in format a55 heading "Found In
.."
p rompt col string format a70 heading "String Found"
-- prompt col row_id format 99999999999999999999
prompt set term on
prompt
declare
cursor varchar_tables_cur IS
select owner
, table_name
, column_name
from dba_tab_columns
where owner != 'SYS'
and owner != 'SYSTEM'
and owner = '&OWNER_NAME'
and data_type in ( 'VARCHAR2','VARCHAR','CHAR');
begin
for v_t_rec in varchar_tables_cur loop
dbms_output.put_line('--');
-- dbms_output.put_line('prompt '||v_t_rec.owner||'.'||v_t_
dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_
dbms_output.new_line;
dbms_output.put(''||v_t_rec.
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||v_t_
dbms_output.put_line('where '||v_t_rec.column_name||' like '||chr(39)||'%&string%'||chr(
dbms_output.put_line('/');
end loop;
end;
/
prompt spool off
prompt prompt ====> created file find_string.out for your review
spool off
@find_string.tmp
