Exception when function with Table return type is invoked

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Exception when function with Table return type is invoked

Post by maxcpr » Fri 30 Jul 2010 12:09

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!

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 30 Jul 2010 17:20

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.

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Post by maxcpr » Mon 02 Aug 2010 10:08

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.

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Mon 02 Aug 2010 14:42

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.

maxcpr
Posts: 33
Joined: Wed 10 Dec 2008 14:46

Post by maxcpr » Tue 03 Aug 2010 09:35

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?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Tue 03 Aug 2010 13:07

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.

Post Reply