Home » SQL & PL/SQL » SQL & PL/SQL » How to find table based on data in a column (oracle 10g,windows)
How to find table based on data in a column [message #416001] Thu, 30 July 2009 02:23 Go to next message
raavi_13
Messages: 12
Registered: June 2009
Location: MUMBAI
Junior Member
Hi,

I have created procedure to find the table names based on a data

in a column name.
create or replace
procedure find_tables_ravi(P_search varchar) as

c_cnt number;
C_DATA VARCHAR2(40);


begin

for c_get in (select table_name,column_name from user_tab_columns)
loop
EXECUTE IMMEDIATE
'select count(*) from ' || c_get.table_name || ' where ' || lower(c_get.column_name) || ' like ' || '''' || '%' || lower(p_search) || '%' || '''' into c_cnt;


IF C_CNT>0 THEN
dbms_output.put_line(c_get.table_name||''''||c_get.column_name|| ''''||c_cnt);
END IF;
end loop;
end find_tables_ravi;

But iam getting the below error.Please help me.
ORA-00933: SQL command not properly ended
ORA-06512: at "RMSOWNER.FIND_TABLES_RAVI", line 11
ORA-06512: at line 2

Regards
Ravi


Re: How to find table based on data in a column [message #416004 is a reply to message #416001] Thu, 30 July 2009 02:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Probably a quoting problem nobody can see because you didn't format the code properly.
Re: How to find table based on data in a column [message #416008 is a reply to message #416001] Thu, 30 July 2009 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First, do not concatenate your value, use bind variable.
Then, you can always debug a dynamic statement displaying it before executing it.
In the end, always use SQL*PLus and copy and paste your session to show your problem.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: How to find table based on data in a column [message #416019 is a reply to message #416001] Thu, 30 July 2009 03:28 Go to previous messageGo to next message
raavi_13
Messages: 12
Registered: June 2009
Location: MUMBAI
Junior Member
Hi,

I formatted the code...please help me.
CREATE OR REPLACE PROCEDURE Find_tables_ravi 
     (p_search  VARCHAR) 
AS 
  c_cnt   NUMBER; 
  c_data  VARCHAR2(40); 
BEGIN 
  FOR c_get IN (SELECT table_name, 
                       column_name 
                FROM   user_tab_columns) 
  LOOP 
    EXECUTE IMMEDIATE 'select count(*) from ' 
                      ||c_get.table_name 
                      ||' where ' 
                      ||Lower(c_get.column_name) 
                      ||' like ' 
                      ||'''' 
                      ||'%' 
                      ||Lower(p_search) 
                      ||'%' 
                      ||'''' INTO c_cnt; 
     
    IF c_cnt > 0 THEN 
      dbms_output.Put_line(c_get.table_name 
                           ||'''' 
                           ||c_get.column_name 
                           ||'''' 
                           ||c_cnt); 
    END IF; 
  END LOOP; 
END find_tables_ravi; 

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 30 July 2009 04:45] by Moderator

Report message to a moderator

Re: How to find table based on data in a column [message #416024 is a reply to message #416001] Thu, 30 July 2009 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Generally formatting involves using indentation.
In this forum it also involves using code tags.
Have a look at the forum guide Michel linked to.

Put your dynamic statement in a varchar variable and display it.
Re: How to find table based on data in a column [message #416084 is a reply to message #416024] Thu, 30 July 2009 07:17 Go to previous messageGo to next message
vishubms
Messages: 10
Registered: October 2006
Location: Bangalore
Junior Member
You may try this...

Declare

Str Varchar2(20000);
Ctr Number := 0;
Fnd Number := 0;
P_Str Varchar2(20) := 'post';

Cursor All_Tab Is
Select Distinct (Table_Name)
From All_Tab_Cols
Where Owner = 'PDD';

Cursor All_Tab_Col(T_Name Varchar2) Is
Select Column_Name
From User_Tab_Cols
Where Table_Name = T_Name
Order By Column_Name;

Begin

For J In All_Tab Loop
For I In All_Tab_Col(J.Table_Name) Loop
Str := 'select Count(*) From '||J.Table_Name||' Where Lower(' || I.Column_Name || ') Like ''%'||P_Str|| '%''';
--Dbms_Output.Put_Line(Str);
Execute Immediate Str Into Ctr;
If Ctr > 0 Then
Fnd := 1;
End If;
End Loop;

If Fnd > 0 Then
Dbms_Output.Put_Line('table Name--->'||J.Table_Name);
End If;
End Loop;

End;

-- Regards
Vishwanath
Re: How to find table based on data in a column [message #416088 is a reply to message #416001] Thu, 30 July 2009 07:22 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Unformatted and overcomplicated - impressive.
You only need 1 loop.
Previous Topic: Beginner SQL Query Question
Next Topic: complation error in function
Goto Forum:
  


Current Time: Sun Dec 11 06:06:40 CST 2016

Total time taken to generate the page: 0.05398 seconds