Home » SQL & PL/SQL » SQL & PL/SQL » Search the whole user (9i)
Search the whole user [message #355512] Fri, 24 October 2008 10:19 Go to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Hi,

Is there a quicker method to search all the varchar2 columns of all tables of a user, to find out a particular string in it say a IP address.

What's there in my mind is to loop through all the tables of user_tables and then each column of these table.

Thanks,
Kumar
Re: Search the whole user [message #355517 is a reply to message #355512] Fri, 24 October 2008 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no faster way.

But if you just want to know if a string is there you can export the database and grep on the dump file (praying that the string was not splitted).

Regards
Michel
Re: Search the whole user [message #355531 is a reply to message #355517] Fri, 24 October 2008 13:21 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
there are several solution posted here. This is one of them http://www.orafaq.com/forum/t/47926/0/
Re: Search the whole user [message #355565 is a reply to message #355531] Sat, 25 October 2008 07:27 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Thanks all for your time
Re: Search the whole user [message #355580 is a reply to message #355565] Sat, 25 October 2008 12:11 Go to previous messageGo to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
This helps me out to get the id in a specific user:


DECLARE
	CURSOR T_EMAIL IS
	select table_name,column_name from user_tab_cols where data_type='VARCHAR2' AND TABLE_NAME NOT IN('BONUS');
	v_stmt varchar2(3000);
	
	R_DATA	USER_TAB_COLS.COLUMN_NAME%TYPE;
	TYPE Ref_crs IS REF CURSOR;
	DATA_CV	Ref_crs;
	v_DATA  VARCHAR2(3000);
BEGIN
	FOR R_RECORD IN T_EMAIL LOOP
		BEGIN

		V_STMT:='SELECT '||R_RECORD.COLUMN_NAME||' FROM '||R_RECORD.TABLE_NAME||' WHERE lower('||R_RECORD.COLUMN_NAME||') LIKE ''%@%.com''';		
		--dbms_output.put_line(v_stmt);
		OPEN DATA_CV FOR v_stmt;
		LOOP 	
			FETCH DATA_CV INTO v_DATA;
			EXIT WHEN DATA_CV%NOTFOUND;
			DBMS_OUTPUT.PUT_LINE(R_RECORD.TABLE_NAME||'-->'||V_DATA);
		END LOOP;
		CLOSE DATA_CV;
		EXCEPTION
			WHEN others THEN
				--dbms_output.put_line(v_stmt);
				NULL;
		END;
	END LOOP;
END;
/


Thought to share with all.

Thanks,
Kumar
Re: Search the whole user [message #355582 is a reply to message #355512] Sat, 25 October 2008 13:13 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Consider this reading too:

Kevin Meade's blog

The Phoenix Rises: a How-To for SQL FROM SQL

Quote:
desc user_tab_columns
desc user_tables
desc user_views

The more you do it the easier it gets. Here is an example. This would need more tweaking for large scale use but you should get the idea of what reading the above will try to teach you.

SQL> drop table temp1
  2  /

Table dropped.

SQL> 
SQL> create table temp1
  2  (
  3   a number not null primary key
  4  ,b varchar2(20)
  5  ,c varchar2(20)
  6  ,d varchar2(20)
  7  )
  8  /

Table created.

SQL> 
SQL> insert into temp1 values (1,'abc.def.ghi.jkl','123.456.789.jkl','jkl')
  2  /

1 row created.

SQL> 
SQL> commit
  2  /

Commit complete.

SQL> 
SQL> select count(*)
  2  from temp1
  3  where b like '%jkl%'
  4  or c like '%jkl%'
  5  or d like '%jkl%'
  6  /

  COUNT(*)
----------
         1

1 row selected.

SQL> 
SQL> select 'select count(*)' TEXT from user_tables where table_name = 'TEMP1'
  2  union all
  3  select 'from '||table_name TEXT from user_tables where table_name = 'TEMP1'
  4  union all
  5  select 'where '||column_name||' like ''%jkl%''' from user_tab_columns where table_name = 'TEMP1' and data_type = 'VARCHAR2'
  6  union all
  7  select '/' from user_tables where table_name = 'TEMP1'
  8  /

TEXT
-------------------------------------------------
select count(*)
from TEMP1
where B like '%jkl%'
where C like '%jkl%'
where D like '%jkl%'
/

6 rows selected.

SQL> 
SQL> with
  2      first_column as (
  3                        select min(column_id) column_id
  4                        from user_tab_columns
  5                        where table_name = 'TEMP1'
  6                        and data_type = 'VARCHAR2'
  7                      )
  8  select 'select count(*)' TEXT from user_tables where table_name = 'TEMP1'
  9  union all
 10  select 'from '||table_name TEXT from user_tables where table_name = 'TEMP1'
 11  union all
 12  select case when first_column.column_id = user_tab_columns.column_id then 'where ' else 'or ' end
 13          ||column_name||' like ''%jkl%''' from user_tab_columns,first_column where table_name = 'TEMP1' and data_type = 'VARCHAR2'
 14  union all
 15  select '/' from user_tables where table_name = 'TEMP1'
 16  /

TEXT
-------------------------------------------------
select count(*)
from TEMP1
where B like '%jkl%'
or C like '%jkl%'
or D like '%jkl%'
/

6 rows selected.

SQL> select count(*)
  2  from TEMP1
  3  where B like '%jkl%'
  4  or C like '%jkl%'
  5  or D like '%jkl%'
  6  /

  COUNT(*)
----------
         1

1 row selected.


Good luck, Kevin
Previous Topic: procedures (merged 2 topics by bb)
Next Topic: Getting The Mail Id
Goto Forum:
  


Current Time: Fri Dec 09 04:08:28 CST 2016

Total time taken to generate the page: 0.07992 seconds