Calling a pipelined function and populating a complex type

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
atamata77
Posts: 1
Joined: Wed 12 Feb 2020 08:00

Calling a pipelined function and populating a complex type

Post by atamata77 » Wed 12 Feb 2020 12:06

Hi,

I'm needing to populate an entity from a pipelined function and am getting an OracleException: ORA-01036: illegal variable name/number . This is *most likely* because of the return parameter type

My code is below

Code: Select all

public async Task<List<OutstandingTrade>> GetOutstandingTradesAsync(string instCode)
{
	var x = await this.Database.SqlQuery<OutstandingTrade>("SELECT  * FROM TABLE( f_outstanding_orders(p_inst_code)",
		new OracleParameter("p_inst_code", OracleDbType.VarChar, instCode,ParameterDirection.Input)).ToListAsync();

	return x;
}
I have tried overriding OnModelCreating without success

Code: Select all

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
	base.OnModelCreating(modelBuilder);
	modelBuilder.ComplexType<OutstandingTrade>();
	modelBuilder.ComplexType<OutstandingTrade>().Property(x => x.ShortName).HasColumnName("SHORT_NAME");
	modelBuilder.ComplexType<OutstandingTrade>().Property(x => x.PfoCode).HasColumnName("PFO_CODE");
	modelBuilder.ComplexType<OutstandingTrade>().Property(x => x.IconCode).HasColumnName("ICON_CODE");
	...etc etc
}
As an alternative to OnModelCreating I've also tried the below within my Dbcontext

Code: Select all

public virtual DbSet<OutstandingTrade> OutstandingTrades { get; set; }
...but I get the same oracle exception as above.

Is there something I'm missing that's causing the output to not be recognised? Is there an oracle return type which I could be using?

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

Re: Calling a pipelined function and populating a complex type

Post by Shalex » Sat 15 Feb 2020 13:38

You should use ':' prefix followed by name of the parameter in command text:

Code: Select all

    var databaseParameter = new Devart.Data.Oracle.OracleParameter("p_deptno", OracleDbType.Number) { Value = 10 };
    var parameters = new OracleParameter[] { databaseParameter };
    var commandText = "SELECT * FROM DEPT WHERE DEPTNO = :p_deptno";
            
    var result = context.Database.SqlQuery<DEPT>(commandText, parameters).ToList();
Refer to https://www.devart.com/dotconnect/oracl ... eters.html.

Post Reply