OraFAQ Forum: SQL & PL/SQL » obtaining column name by knowing table name (user created) and a value in that is present in that t

Home » SQL & PL/SQL » SQL & PL/SQL » obtaining column name by knowing table name (user created) and a value in that is present in that t

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

obtaining column name by knowing table name (user created) and a value in that is present in that t [message #37933]

Thu, 07 March 2002 08:35

kiran
Messages: 503
Registered: July 2000

Senior Member

Is there a way where in by passing the column Value ( not column name ) and the tablename

as parameters into a function and find out if that value is in one of the columns of the

tablename passed and if present return back the corresponding column name of that table

thanks in advance
kiran

Report message to a moderator

Re: obtaining column name by knowing table name (user created) and a value in that is present in th [message #37939 is a reply to message #37933]

Thu, 07 March 2002 10:24

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

create or replace function find_val(tname varchar2,cvalue varchar2)
return varchar2
is
cursor c1 is select column_name from user_tab_columns where table_name=tname;
retcol varchar2(30):='No column';
cnt number;
begin
for crec in c1 loop
execute immediate 'select count(*) from '||tname||
' where '||crec.column_name||'='||''''||cvalue||'''' into cnt;
if cnt>0 then
retcol := crec.column_name;
exit;
end if;
end loop;
return retcol;
end;

Report message to a moderator

Previous Topic:	optimized count
Next Topic:	where can i find the help about function of oracle?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 16:00:10 CDT 2024