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

[Wain123]

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]

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]

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]

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;

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]

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]

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

[Shalex]

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]

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