stored procedure returning object collection
Posted: Sun 04 Nov 2012 19:59
Hi,
i have following code :
OracleConnection conn = new OracleConnection("User Id=xxx;Password=xxxx;Server=xxx;Direct=True;Sid=xxx");
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "pck$repos_dl.test2";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("ReturnValue", Devart.Data.Oracle.OracleDbType.Object, System.Data.ParameterDirection.ReturnValue));
(cmd.Parameters["ReturnValue"] as OracleParameter).ObjectTypeName = "dl_rondes";
cmd.ExecuteNonQuery();
When i execute this code, it returns this error :
ORA-01948: Naamlengte van ID (64) overschrijdt de maximumlengte (30).
ORA-06512: in "SYS.DBMS_PICKLER", regel 18
ORA-06512: in "SYS.DBMS_PICKLER", regel 58
ORA-06512: in regel 1
the oracle code being called :
FUNCTION test2
return dl_rondes
is
response dl_rondes;
begin
select cast( multiset(
( select dl_ronde(2, sysdate , 'VM', 1)
from dual r)
)
as dl_rondes
)
into response from dual;
return response;
end test2;
with objects :
type dl_rondes is table of dl_ronde;
type dl_ronde is object
( ronde_id NUMBER(19,0) ,
datum DATE,
dagschijf_cd VARCHAR2(2 BYTE),
dienst_id NUMBER(19,0)
);
when i return a single object as :
FUNCTION test
return dl_ronde
is
begin
return dl_ronde(2, sysdate , 'VM', 1);
end test;
then there is no error executing the procedure ( after adjusting the c# code for the objecttypename);
what am i doing wrong ?
i have following code :
OracleConnection conn = new OracleConnection("User Id=xxx;Password=xxxx;Server=xxx;Direct=True;Sid=xxx");
conn.Open();
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "pck$repos_dl.test2";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new OracleParameter("ReturnValue", Devart.Data.Oracle.OracleDbType.Object, System.Data.ParameterDirection.ReturnValue));
(cmd.Parameters["ReturnValue"] as OracleParameter).ObjectTypeName = "dl_rondes";
cmd.ExecuteNonQuery();
When i execute this code, it returns this error :
ORA-01948: Naamlengte van ID (64) overschrijdt de maximumlengte (30).
ORA-06512: in "SYS.DBMS_PICKLER", regel 18
ORA-06512: in "SYS.DBMS_PICKLER", regel 58
ORA-06512: in regel 1
the oracle code being called :
FUNCTION test2
return dl_rondes
is
response dl_rondes;
begin
select cast( multiset(
( select dl_ronde(2, sysdate , 'VM', 1)
from dual r)
)
as dl_rondes
)
into response from dual;
return response;
end test2;
with objects :
type dl_rondes is table of dl_ronde;
type dl_ronde is object
( ronde_id NUMBER(19,0) ,
datum DATE,
dagschijf_cd VARCHAR2(2 BYTE),
dienst_id NUMBER(19,0)
);
when i return a single object as :
FUNCTION test
return dl_ronde
is
begin
return dl_ronde(2, sysdate , 'VM', 1);
end test;
then there is no error executing the procedure ( after adjusting the c# code for the objecttypename);
what am i doing wrong ?