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
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 Go to next message
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
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 Go to previous message
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;
Previous Topic: optimized count
Next Topic: where can i find the help about function of oracle?
Goto Forum:
  


Current Time: Wed Oct 27 05:56:05 CDT 2021