Its an interesting problem, specifically because of the need to use both .NET to consume the data and stored procedures across dblinks. The limitation of not using cursors across dblinks led you to pl/sql tables, but you cannot use these easily in .NET (without going through the pain of setting up and maintaining global objects).
So, I propose using both ref cursor functions and procedures with output table params. You'll be able to call the ref cursor functions from .NET easily (where you don't need to select across dblinks), and for dblink database work, use the corresponding procedures. For example:
create table test1 (
col1 number,
col2 varchar2(10),
col3 date default sysdate not null
);
insert into test1(col1,col2) values (1,'A');
insert into test1(col1,col2) values (1,'X');
insert into test1(col1,col2) values (2,'B');
commit;
CREATE OR REPLACE package TEST_PKG as
type t_test1_tab is table of test1%rowtype;
-- weak ref cursor
function get_test1_cur (i_num in number) return sys_refcursor;
-- uses rowtype for table
procedure get_test1_tab(i_num in number, o_tab out t_test1_tab);
end;
CREATE OR REPLACE package body TEST_PKG as
function get_test1_cur (i_num in number) return sys_refcursor is
l_cur sys_refcursor;
begin
open l_cur for select * from test1 where col1=i_num;
return l_cur;
end;
procedure get_test1_tab(i_num in number, o_tab out t_test1_tab) is
l_rec test1%rowtype;
l_tab t_test1_tab := t_test1_tab();
l_cur sys_refcursor;
begin
l_cur := get_test1_cur(i_num);
loop
fetch l_cur into l_rec;
exit when l_cur%notfound;
l_tab.extend;
l_tab(l_tab.last) := l_rec;
end loop;
close l_cur;
o_tab := l_tab;
end;
end;
Put any logic needed inside of your ref cursor function. The procedure simply calls the function and creates the table (using rowtype).
Use the procedure for db calls across dblinks:
declare
l_tab test_pkg.t_test1_tab@dblinkA;
begin
test_pkg.get_test1_tab@dblinkA(1, l_tab);
-- show count
dbms_output.put_line('Table has ' || l_tab.count || ' rows.');
end;
Use the function for odp.net calls. For example (code snippet):
OracleConnection con = new OracleConnection(connStr);
// create the command object and set attributes
OracleCommand cmd = new OracleCommand("test_pkg.get_test1_cur", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = false;
// create parameter object for the cursor
OracleParameter p_refcursor = new OracleParameter();
// create any input parameters to the function
OracleParameter p_num = new OracleParameter();
p_refcursor.OracleDbType = OracleDbType.RefCursor;
p_refcursor.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p_refcursor);
// add any input parameters
p_num.OracleDbType = OracleDbType.Int32;
p_num.Direction = ParameterDirection.Input;
p_num.Value = 1;
cmd.Parameters.Add(p_num);
// create a data adapter to use with the data set
OracleDataAdapter da = new OracleDataAdapter(cmd);
// create the data set
DataSet ds = new DataSet();
// fill the data set
da.Fill(ds);
This approach should be easy to maintain, since not only are you using rowtype, but the procedure is simply calling the function.
