Page 1 of 1
"ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle
Posted: Wed 07 Jun 2017 08:10
by 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();
Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle
Posted: Fri 09 Jun 2017 19:59
by 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.
Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle
Posted: Mon 12 Jun 2017 08:32
by 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?
Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle
Posted: Tue 13 Jun 2017 09:25
by Shalex
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?
Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle
Posted: Tue 13 Jun 2017 10:22
by 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.
Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle
Posted: Wed 21 Jun 2017 10:08
by Wain123
This works without making the type global using ODP, can you please explain why Devart can't do it?
Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle
Posted: Thu 29 Jun 2017 16:35
by 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();
}
}
}
Re: "ObjectTypeName must be specified" error when using a stored procedure with a varray parameter using EFCore and Oracle
Posted: Fri 30 Jun 2017 09:00
by Wain123
Apologies, I found a mistake in my ODP example. It doesn't work with ODP after all.