"ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Wain123
Posts: 12
Joined: Wed 07 Jun 2017 07:25

"ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Post by Wain123 » Wed 07 Jun 2017 08:10

I am trying to call a stored procedure in an Oracle DB from EFCore and receiving a System.ArgumentException: 'ObjectTypeName must be specified.'

Exact versions:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
EFCore 1.1.2
dotConnect 9.4.280 Professional

There is an overload of OracleParameterCollection.Add() that specifies the ObjectTypeName, but it is marked as "for internal use" and ObjectTypeName is not documented.

Here's the relevant part of my code which throws the exception in the last line. I get the same exception if the varray is a function parameter instead of the return value. The code works if I replace "return str.vArr" with "return str.vc2" and "OracleDbType.Array" with "OracleDbType.VarChar".

Code: Select all

create or replace package STR as
subtype vc2 is varchar2(4000);
type vArr is varray(1000000) of vc2;
end STR;

Code: Select all

create or replace function testproc
return str.vArr
is
begin
   return null;
end;

Code: Select all

OracleConnection Connection = new OracleConnection();
Connection.ConnectionString = "<data hidden>";
Connection.Open();
OracleCommand command = Connection.CreateCommand();
command.CommandText = "testproc";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("x", OracleDbType.Array, ParameterDirection.ReturnValue);
command.ExecuteNonQuery();

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Post by Shalex » Fri 09 Jun 2017 19:59

Try setting ObjectTypeName to "STR.vArr". Does it work now?

JIC: you can call stored procedure via EF Core like described at viewtopic.php?t=35509.

Wain123
Posts: 12
Joined: Wed 07 Jun 2017 07:25

Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Post by Wain123 » Mon 12 Jun 2017 08:32

No. The last line throws Devart.Data.Oracle.OracleException: 'OCI-22303: type "STR"."VARR" not found'.

Code: Select all

command.CommandText = "testproc";
command.CommandType = CommandType.StoredProcedure;
var parameter = command.CreateParameter();
parameter.OracleDbType = OracleDbType.Array;
parameter.Direction = ParameterDirection.ReturnValue;
parameter.ObjectTypeName = "STR.vArr";
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
How would I use the FromSQL method when entities (and thus also DbSets) aren't directly involved?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Post by Shalex » Tue 13 Jun 2017 09:25

Wain123 wrote:No. The last line throws Devart.Data.Oracle.OracleException: 'OCI-22303: type "STR"."VARR" not found'.
User-defined types declared in a package cannot be used outside of this package. We suggest defining these types globally:

Code: Select all

create type vArr_global is varray(1000000) OF varchar2(4000);

create or replace function testproc2
RETURN vArr_global
is
begin
   return null;
end;
Wain123 wrote:How would I use the FromSQL method when entities (and thus also DbSets) aren't directly involved?
Please refer to https://stackoverflow.com/questions/426 ... re-1-1-1-0. Is that what you mean?

Wain123
Posts: 12
Joined: Wed 07 Jun 2017 07:25

Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Post by Wain123 » Tue 13 Jun 2017 10:22

Making it global fixes the issue, thanks, but I don't understand why. I can have testproc return str.vc2, which is not global, and it works fine, so your claim "User-defined types declared in a package cannot be used outside of this package" confuses me.

That stackoverflow post answers my question: "SQL queries can only be used to return entity types that are part of your model." Thanks.

Wain123
Posts: 12
Joined: Wed 07 Jun 2017 07:25

Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Post by Wain123 » Wed 21 Jun 2017 10:08

This works without making the type global using ODP, can you please explain why Devart can't do it?

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Post by Shalex » Thu 29 Jun 2017 16:35

Please tell us how to modify the ODPUse method to make it work with a package type:

Code: Select all

using System;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static string connString = @"user id=c##scott;password=tiger;data source=  (DESCRIPTION =
                                            (ADDRESS_LIST =
                                              (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL12C)(PORT = 1521))
                                            )
                                            (CONNECT_DATA =
                                              (SERVER = DEDICATED)
                                              (SERVICE_NAME = orcl)
                                            )
                                          )";

        static void Main(string[] args)
        {

            //create or replace package STR as
            //    subtype vc2 is varchar2(4000);
            //    type vArr is varray(1000000) of vc2;
            //end STR;

            //create or replace function testproc
            //return str.vArr
            //is
            //begin
            //       return null;
            //end;

            ODPUse();       // OCI-22303: type "STR"."vArr" not found

            DevartUse();    // OCI-22303: type "STR"."VARR" not found

        }

        static void ODPUse()
        {
            var connection = new Oracle.DataAccess.Client.OracleConnection(connString);
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = "testproc";
            command.CommandType = CommandType.StoredProcedure;
            var parameter = command.CreateParameter();
            parameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Array;
            parameter.Direction = ParameterDirection.ReturnValue;
            parameter.UdtTypeName = "STR.vArr";
            command.Parameters.Add(parameter);
            command.ExecuteNonQuery();
        }

        static void DevartUse()
        {
            var connection = new Devart.Data.Oracle.OracleConnection(connString);
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = "testproc";
            command.CommandType = CommandType.StoredProcedure;
            var parameter = command.CreateParameter();
            parameter.OracleDbType = Devart.Data.Oracle.OracleDbType.Array;
            parameter.Direction = ParameterDirection.ReturnValue;
            parameter.ObjectTypeName = "STR.vArr";
            command.Parameters.Add(parameter);
            command.ExecuteNonQuery();
        }
    }
}

Wain123
Posts: 12
Joined: Wed 07 Jun 2017 07:25

Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle

Post by Wain123 » Fri 30 Jun 2017 09:00

Apologies, I found a mistake in my ODP example. It doesn't work with ODP after all.

Post Reply