Home » SQL & PL/SQL » SQL & PL/SQL » how to find column name having particular value (oracle 10g)
how to find column name having particular value [message #438187] Thu, 07 January 2010 07:22 Go to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
I have a value of a column.I dont know which table or column does have that value.for example i have 'ABC' i need toa search in database which table's column contain the value as 'ABC' .can anybody provide the query for the same.I am not able to write anything without using dynamic sql.

regards,
Navneet
Re: how to find column name having particular value [message #438189 is a reply to message #438187] Thu, 07 January 2010 07:39 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
navneet_sharma
I am not able to write anything without using dynamic sql.

I agree, you'll need dynamic SQL to do that.

It would be much easier if you knew column name; in that case, code might have looked like this ('SIFRA' is column name, and I'm looking for '1010240' string within that column):
DECLARE
  l_str VARCHAR2(500);
  l_cnt NUMBER := 0;
BEGIN
  FOR cur_r IN (SELECT u.table_name
                FROM user_tab_columns u, user_tables t
                WHERE u.table_name = t.table_name
                  AND u.column_name = 'SIFRA'
               )
  LOOP
    l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||
             ' WHERE sifra = ''1010240''';
													
    EXECUTE IMMEDIATE (l_str) INTO l_cnt;

    IF l_cnt > 0 THEN
       dbms_output.put_line(l_cnt ||' : ' || cur_r.table_name);
    END IF;
  END LOOP;
END;

As you don't know column name, you'll have to iterate through all columns found in USER_TAB_COLUMNS and take care about columns' datatypes; as you are looking for 'ABC', it is probably a CHARACTER datatype column.

Try to tweak the code I posted; perhaps you'll succeed.
Re: how to find column name having particular value [message #438190 is a reply to message #438187] Thu, 07 January 2010 07:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Well, static SQL needs to know what column to select from what table, so In a single query seems rather impossible. My first idea would be to write a procedure/function for it. And yes, that would most probably use dynamic SQL.

MHE
Re: how to find column name having particular value [message #438196 is a reply to message #438187] Thu, 07 January 2010 08:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use find_string procedure from Tom Kyte:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5203164092530

Regards
Michel
Re: how to find column name having particular value [message #438197 is a reply to message #438196] Thu, 07 January 2010 08:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Laughing That goes to show that I am a bit rusty in my replies. Thanks Michel, I should have known that Mr. Kyte had already written such a procedure. And I wouldn't be too surprised if the code of that (or a similar) procedure dwells this forum too.

MHE
Re: how to find column name having particular value [message #438636 is a reply to message #438187] Mon, 11 January 2010 05:50 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
It is FAQ.
Please search Before Posting.

Search_col.

Even On this

sriram Smile

[Updated on: Mon, 11 January 2010 05:51]

Report message to a moderator

Previous Topic: XMLTYPE to CLOB
Next Topic: hard parsing
Goto Forum:
  


Current Time: Thu Dec 08 06:25:48 CST 2016

Total time taken to generate the page: 0.10774 seconds