stored procedure returning object collection

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

stored procedure returning object collection

Post by wgkwvl » 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 ?

wgkwvl
Posts: 39
Joined: Tue 20 Jul 2010 15:13

Re: stored procedure returning object collection

Post by wgkwvl » Mon 05 Nov 2012 10:41

when i connect in OCI mode ( previous was direct mode ),
then i get the following error :

Unable to cast object of type 'Devart.Data.Oracle.t' to type 'Devart.Data.Oracle.cu'.

Helen
Devart Team
Posts: 127
Joined: Wed 07 Sep 2011 11:54

Re: stored procedure returning object collection

Post by Helen » Tue 06 Nov 2012 11:06

Thank you for the detailed explanation. We have reproduced the described behaviour.
We will investigate it and notify you about the results as soon as possible.

Post Reply