Devart Forums

Posted: **Tue 14 Aug 2012 06:14**

Dear devart,

We need to execute query in our Silverlight application. The query involves TABLE function (pipelined, if it matters).

The query runs well:

Code: Select all

SQL> 
desc payman.paycal_dedicated_items_pkg.fdkr_select_aggregated

Parameter Type                                                   Mode Default? 
--------- ------------------------------------------------------ ---- -------- 
(RESULT)  PAYMAN.PAYCAL_DEDICATED_ITEMS_PKG.FDKR_AGGVALUES_TABLE               
P_PERIOD  DATE                                                   IN 

SQL> 
select    m_day,
          m_D_total,
          m_flow_total,
          m_C_ded,
          m_remnant_evening
 from table(payman.paycal_dedicated_items_pkg.fdkr_select_aggregated(date'2010-10-01'))
 where rownum<5
 order by m_day;
 
M_DAY        M_D_TOTAL M_FLOW_TOTAL    M_C_DED M_REMNANT_EVENING
----------- ---------- ------------ ---------- -----------------
01.10.2010       40001       -40001                     99959999
02.10.2010                        0                     99959999
03.10.2010                        0                     99959999
04.10.2010         102         -102                     99959897

SQL>

Then we followed the steps suggested in the document http://www.devart.com/dotconnect/oracle ... tions.html. Because it is required to pass different values to the date parameter P_PERIOD, we took the second path, titled "Creating Function Import Based on Oracle Pipelined Function".

Now we are getting the error during query execution:

"Unexpected store type 'TABLE'."

Is something wrong with our code or is there bug?

Additional information:

We at ver.6.80.
Oracle server is 10.2.0.5

FDKRSELECTAGGREGATED method definition from 'DataModelCredit.Designer.cs':

Code: Select all

.............

    public partial class CreditResourceEntities : ObjectContext
    {
    .............

        #region Methods

        /// <summary>
        /// There are no comments for FDKRSELECTAGGREGATED in the schema.
        /// </summary>
        public ObjectResult<FDKRSELECTAGGREGATEDResult> FDKRSELECTAGGREGATED (global::System.Nullable<System.DateTime> P_PERIOD)
        {
            ObjectParameter P_PERIODParameter;
            if (P_PERIOD.HasValue)
            {
                P_PERIODParameter = new ObjectParameter("P_PERIOD", P_PERIOD);
            }
            else
            {
                P_PERIODParameter = new ObjectParameter("P_PERIOD", typeof(global::System.DateTime));
            }
            return base.ExecuteFunction<FDKRSELECTAGGREGATEDResult>("FDKRSELECTAGGREGATED", P_PERIODParameter);
        }
    .............

The calling statement in 'DomainService1.cs' was:

Code: Select all


............

[EnableClientAccess()]
public class DomainService1 : LinqToEntitiesDomainService<CreditResourceEntities>
{

............

public IEnumerable<decimal?> GetFDKRSELECTAGGREGATEDResult(DateTime period)
{
  IEnumerable<decimal?> pp = 
   this.ObjectContext.FDKRSELECTAGGREGATED(period).Select(e => e.m_C_ded).ToList();
  return pp;
}
............

(In fact, we need more columns, but restrained ourselves to m_C_ded, cause we have no idea how to include more).

Snippet of ORACLE package spec.:

Code: Select all

create or replace package payman.paycal_dedicated_items_pkg is

.................

type  fdkr_aggvalues_rec is record (
          m_day         date,
          m_C_total     number,
          m_D_total     number,
          m_flow_total  number,
          m_C_ded       number,
          m_D_ded       number,
          m_C_KP        number,
          m_D_KP        number,
          m_remnant_morning   number,
          m_remnant_evening   number
      );
type  fdkr_aggvalues_table is table of fdkr_aggvalues_rec;

function  fdkr_select_aggregated (p_period date)  return fdkr_aggvalues_table pipelined;

.................

end paycal_dedicated_items_pkg;

Complex type definition from EDML:

Code: Select all

        <ComplexType Name="FDKRSELECTAGGREGATEDResult" ed:Guid="72558fab-690f-42e7-b50b-46b5f8d4dd18">
          <Property Name="m_day" Type="DateTime" Nullable="false" ed:ValidateRequired="True" ed:Guid="544f366e-3474-416e-884d-820b01444715" />
          <Property Name="m_C_total" Type="Decimal" ed:ValidateRequired="False" ed:Guid="ab9e15a0-d607-4190-8020-41bb1935518c" />
          <Property Name="m_D_total" Type="Decimal" ed:ValidateRequired="False" ed:Guid="761ca32f-4d5f-4b0a-b84d-f5183e06548e" />
          <Property Name="m_flow_total" Type="Decimal" ed:ValidateRequired="False" ed:Guid="280b9ffa-1832-4af5-a085-8aca009ba030" />
          <Property Name="m_C_ded" Type="Decimal" ed:ValidateRequired="False" ed:Guid="5fcc1fc3-4ad4-4b3c-b53c-a7ce47310164" />
          <Property Name="m_D_ded" Type="Decimal" ed:ValidateRequired="False" ed:Guid="75d938fb-86c7-4fb6-8a23-18d6ab77445c" />
          <Property Name="m_remnant_morning" Type="Decimal" ed:ValidateRequired="False" ed:Guid="7417396d-6d5b-4cb5-a5ac-63c20a5043e6" />
          <Property Name="m_remnant_evening" Type="Decimal" ed:ValidateRequired="False" ed:Guid="527574ab-23aa-4605-b2d4-1e800978c4c9" />
        </ComplexType>

Best regards,
Max

Posted: **Fri 17 Aug 2012 14:35**

Thank you for your report. We have reproduced and are investigating the issue.

Posted: **Wed 22 Aug 2012 08:48**

Meanwhile, could you suggest any workaround?

We could create a dummy Entity class FDKRSELECTAGGREGATED, based on dummy view, which is created in the Oracle as, for example:
create or replace view payman.FDKRSELECTAGGREGATED_VIEW
as
select * from table(
payman.paycal_dedicated_items_pkg.fdkr_select_aggregated (date'2010-10-01'))
where 1=0

Then, having compatible (with pipelined function output type) Entity class, is there a way to populate the list of entities via plain Oracle SELECT execution?

So, we still want to query the database with the SQL:

Code: Select all

select    m_day,
          m_D_total,
          m_flow_total,
          m_C_ded,
          m_remnant_evening
 from table(payman.paycal_dedicated_items_pkg.fdkr_select_aggregated(date'2010-10-01'))
 where rownum<5
 order by m_day;

but then push raw rows into collection of FDKRSELECTAGGREGATED's.
I believe it should be possible, otherwise how the defining query feature would exist?

Posted: **Wed 22 Aug 2012 12:32**

The bug with using pipelined functions which are pushing out the RECORD objects is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Posted: **Thu 23 Aug 2012 01:12**

Oh, thanks!

Posted: **Fri 07 Sep 2012 08:56**

New version of dotConnect for Oracle 7.2 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=24845 .

Devart Forums

Can't query Pipelined function in LINQ

Can't query Pipelined function in LINQ

Re: Can't query Pipelined function in LINQ

Re: Can't query Pipelined function in LINQ

Re: Can't query Pipelined function in LINQ

Re: Can't query Pipelined function in LINQ

Re: Can't query Pipelined function in LINQ