I want to pass a nested table ( OracleTable in dotconnection ? ) as a parameter to call the stored procedure in a package. The type test002_table is defined in the package. Codes of stored procedure are below:
create or replace package testPro is
type test002_table is table of test002%rowtype;
procedure testInsert(tbs test002_table);
end testPro;
create or replace package body testPro is
procedure testInsert(tbs test002_table) is
i int;
begin
delete from test002;
for i in 1..tbs.count loop
insert into test002 values tbs(i);
end loop;
end;
end;
A test written in PL\SQL runs sucessfully :
declare
tab testpro.test002_table := testpro.test002_table();
item test002%rowtype;
i integer;
begin
tab.extend();
item.id:=1;
item.name:='a';
item.lev:=5;
item.age:=55;
item.address:='das';
tab(tab.count) := item;
testPro.testInsert(tab);
commit;
end;
But I don't know how to call this procedure using dotConnect. I've tried the following way but without sucess:
OracleCommand cmd = new OracleCommand();
cmd.Connection = con; //OracleConnection con
cmd.CommandType = System.Data.CommandType.StoredProcedure;
OracleType tp = OracleType.GetObjectType("testPro.test002_table", con);
OracleTable table = new OracleTable(tp);
Dotconnect couldn't find the type. How could I get the required object of OracleType? Or could this problem be solved in other ways? Thanks a lot.
User-defined types declared in a package cannot be used outside of this package. Please define globally the type used with the OracleTable class:
JIC: ROWTYPE is a PL/SQL construct and not recognized in the SQL create type statement.