Calling a pipelined function and populating a complex type

[atamata77]

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]

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.