Search This Blog

Total Pageviews

Saturday 15 November 2014

Search string in database


Oracle find string or number in the database

search string
find 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_rec.table_name||'.'||v_t_rec.column_name||'');
dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
dbms_output.new_line;
dbms_output.put(''||v_t_rec.column_name||' String ');
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||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


===


-- 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_t_rec.column_name||'');
dbms_output.put('select distinct'''||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
dbms_output.new_line;
dbms_output.put(''||v_t_rec.column_name||' String ');
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_t_rec.column_name||'');
dbms_output.put('select distinct'''||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
dbms_output.new_line;
dbms_output.put(''||v_t_rec.column_name||' String ');
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||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

====
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_rec.table_name||'.'||v_t_rec.column_n ame||'');
dbms_output.put('select '''||v_t_rec.owner||'.'||v_t_rec.table_name||'.'||v_t_rec.column_name||''' Found_In ,');
dbms_output.new_line;
dbms_output.put(''||v_t_rec.column_name||' String ');
dbms_output.new_line;
-- dbms_output.put_line('rowid Row_id ');
dbms_output.put_line('from '||v_t_rec.owner||'.'||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

sqlplus set vi editor



vi editor in sqlplus   ..



vi editor in sqlplus 

EDITOR=vi; export EDITOR    in .profile file 

Oracle DBA

anuj blog Archive