Home » SQL & PL/SQL » SQL & PL/SQL » search a whole table (10g)
search a whole table [message #394022] Wed, 25 March 2009 06:57 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Hi,

I need to see if a value is present in any one column of a table.I don't know if the value exists. If it exists i don't know in which column it exists. Is there any way of knowing in which column the value exists(if it does)?

Thnx.
maheshmhs
Re: search a whole table [message #394025 is a reply to message #394022] Wed, 25 March 2009 07:26 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
If you search this forum or do a google search using appropriate terms you should be able to find a variety of solutions.
Since you supply no useful info really, I would suggest that you query the table using multiple predicates combined with the OR operator.

[Updated on: Wed, 25 March 2009 07:27]

Report message to a moderator

Re: search a whole table [message #394027 is a reply to message #394025] Wed, 25 March 2009 07:40 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Please see this.i think this may help you...

http://it.toolbox.com/blogs/david/search-for-a-given-string-in-all-fields-of-an-entire-schema-24074?rss=1


please search in google or forum before posting.

thanks &regards
Sriram
Re: search a whole table [message #394082 is a reply to message #394022] Wed, 25 March 2009 11:59 Go to previous messageGo to next message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Thanks ramoradba for your link.

I have looked at it and find it does only search the name of columns. It uses varray. However, for that purpose, I guess use ref cursor might be easier to traverse teh data dictionary.

In addtion, orginal question seems not very clear. He/she may ask for full text search.

Re: search a whole table [message #394216 is a reply to message #394082] Thu, 26 March 2009 03:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you've misread the code.

The 3 lines like this one:
execute immediate 'select count(*) from ' || v_tab_arr1(i) || ' where ' || lower(v_col_arr1(j)) || ' like ' || '''' || '%' || lower(p_search) || '%' || '''' into v_search_result;
are the key to how it works - they look for how many rows in the table whose name is in v_tab_arr1(i) have a column whose name is in v_col_arr1(j) which contains a value like p_search.

It's looking at the contents of the columns, not the column names.

I'd have written it slightly differently - it would be somewhat faster to build a single query for each table that returned a value for each column of the required type listing how many ows in that column matched the required value, as you'd only hit each table once, rather than once per column of the required type.

Re: search a whole table [message #394295 is a reply to message #394216] Thu, 26 March 2009 07:09 Go to previous message
registereduser
Messages: 52
Registered: June 2008
Location: Toronto
Member
Yes, yes, I messed up v_tab_arr1(i) with v_tab_arr1. Indeed, I am not familiar with varray. Thanks a lot.

Previous Topic: How to turn image files into blob
Next Topic: Encrypting an 9 position field
Goto Forum:
  


Current Time: Sat Dec 10 22:37:06 CST 2016

Total time taken to generate the page: 0.03900 seconds