Tom,

Here is an example...that i want to change one function to avoid redundant information.

create or replace package p_ref_cursor is
type ret_ref_cursor is ref cursor;
end p_ref_cursor;
/


drop table "tab1";

create table "tab1" (no number, city varchar2(20));

insert into "tab1" values (1, 'Boston');
insert into "tab1" values (2, 'Chicago');
insert into "tab1" values (3, 'Atlanta');


drop table "tab2";

create table "tab2" (no number, city varchar2(20));

insert into "tab2" values (1, 'London');
insert into "tab2" values (2, 'Paris');


create or replace function "fun_tab1"(p_no in number)
return p_ref_cursor.ret_ref_cursor is
l_cursor p_ref_cursor.ret_ref_cursor;
begin
open l_cursor for select * from "tab1" where no <= p_no;
return l_cursor;
end;
/


create or replace function "fun_tab2"(p_no in number)
return p_ref_cursor.ret_ref_cursor is
l_cursor p_ref_cursor.ret_ref_cursor;
begin
open l_cursor for select * from "tab2" where no <= p_no;
return l_cursor;
end;
/

create or replace function get_data(p_table_name in varchar2, p_no in number)
return p_ref_cursor.ret_ref_cursor is
l_cursor p_ref_cursor.ret_ref_cursor;
begin
if p_table_name = 'tab1' then
return "fun_tab1"(p_no);
end if;
if p_table_name = 'tab2' then
return "fun_tab2"(p_no);
end if;
end;
/


var r refcursor
exec :r := get_data('tab1', 10);
print r

exec :r := get_data('tab2', 10);
print r

and the output is...

NO CITY
---------- --------------------
1 Boston
2 Chicago
3 Atlanta


NO CITY
---------- --------------------
1 London
2 Paris




For some reasons...we used to create tables and functions as a case sensitive and also creates seperate set of independant functions and procedure for each tables. now there is a master function(get_data)... that gets table name and other values as a input parameter and calls corresponding functions. i am allowed to change the master function to remove all redundant information(if..thens...). i thought of using native dynamic sql but i couldnt. Is it possible to use native dynamic sql...if so...could you please give me the suggestions.

thanks,
senthil.