Devart Forums

Hi, i couldn't invoke function which return Table (array).

Exception is always raised either I use ADO.net or Linq

Is it bug or i just don't understand something?

SQL :

CREATE OR REPLACE FUNCTION GetTicketMainFake(niClass IN dd.reference) RETURN dd.ClobType_Nested
IS
arr dd.ClobType_Nested := dd.ClobType_Nested();
BEGIN
RETURN arr;
END GetTicketMainFake;



//////Linq code

DataContext1 dataContext = new DataContext1.DataContext1();

//Exception here
//Could not build reader for node type Devart.Data.Linq.Provider.Query.au.

var r = dataContext.Getticketmainfake(null);

//////ADO.NET
OracleConnection oraCon = new OracleConnection(Properties.Settings.Default.ConnectionString);

OracleCommand oraCommand = new OracleCommand("NB.GetTicketMainFake", oraCon);

oraCommand.CommandType = System.Data.CommandType.StoredProcedure;

oraCommand.Parameters.Add(new OracleParameter("niClass", OracleDbType.Double)).Value = System.DBNull.Value;

OracleParameter oracleParam = oraCommand.Parameters.Add("RETURN", OracleDbType.Table);

oracleParam.Direction = System.Data.ParameterDirection.ReturnValue;

oracleParam.ObjectTypeName = "DD.CLOBTYPE_NESTED";

oraCon.Open();

//Exception here
// OCI-22303: тип "DD"."CLOBTYPE_NESTED" не найден
oraCommand.ExecuteNonQuery();

oraCon.Close();



Thanks!

As for using this function with LINQ, the problem is that LinqConnect does not support object types, including Oracle arrays and nested tables.

As for calling GetTicketMainFake from ADO.NET, could you please specify if "dd" is a package or schema? In the first case, the problem should be that the UDT you are using cannot be seen outside the package; to resolve the problem, please try declaring it globally. If "dd" is a schema, please ensure that the user under which you are connecting to the server has proper privileges to access it.

Please tell us if this helps.

Hi.

Yes DD is a package. But i connect to db using user who is owner of the package and this package has been declared globally.

And this still doesn't work!!!

Thanks.

Types declared inside a package cannot be used outside of it. To resolve the problem, you can either declare the CLOBTYPE_NESTED type globally or include the GetTicketMainFake function into the DD package.

Types declared inside a package cannot be used outside of it. To resolve the problem, you can either declare the CLOBTYPE_NESTED type globally or include the GetTicketMainFake function into the DD package.

I presume it is restriction of your library, not the Oracle?

You are right, this is a limitation of dotConnect for Oracle. We are analyzing the possibility of implementing such functionality and will inform you about the results of our investigation. At the moment, you can declare the type globally as a workaround.