Devart Forums

Posted: **Tue 04 May 2010 16:38**

Hello,
I'm using devart dot connect for oracle. I got the following exception after executing a function returning entities:

Code: Select all

System.InvalidOperationException
"Cannot convert object of type 'Devart.Data.Oracle.OracleNumber' to object of type 'System.DateTime'."

I can't figure out what entity field casue this exception and there is no such information in exception message. There are several datetime fields in the entity.
This is the function that i'm using to return data, it just call get_variables_for stored procedure.

Code: Select all

create or replace FUNCTION "GetVariablesFor"
  (in_rootDepartment in number,
   in_modelVarId in number,
   in_period in TIMESTAMP,
   in_kind in integer,
   in_time in TIMESTAMP)
  RETURN  types_pkg.refcur_VARIABLES_FOR IS
cur types_pkg.refcur_VARIABLES_FOR;
new_in_time TIMESTAMP;
BEGIN
    new_in_time := nvl(in_time, SYSTIMESTAMP);
    get_variables_for(in_rootDepartment, in_modelVarId, in_period, in_kind, new_in_time);
    open cur for
        select VF.variable_id "VariableId", VF.Id "Id", VF.Department_Id "DepartmentId",
               VF.Region_Id "RegionId", VF.TheDate "TheDate", VF.Kind "Kind",
               VF.ModifyTime "ModifyTime", VF.TheType "TheType", VF.IsRecomended "IsRecomended",
               VF.UserId "UserId", VF.stringvalue "StringValue", VF.ScalarValue "ScalarValue",
               VF.IntValue "IntValue", VF.comment_ "Comment"
        from getvariablesfor_tbl VF;
    return cur;


END;

get_variables_for stored procedure fills the global table

Code: Select all

  CREATE GLOBAL TEMPORARY TABLE "RETAILSOLUTIONS2"."GETVARIABLESFOR_TBL" 
   (	"VARIABLE_ID" NUMBER(*,0), 
	"ID" NUMBER(*,0), 
	"DEPARTMENT_ID" NUMBER(*,0), 
	"REGION_ID" NUMBER(*,0), 
	"THEDATE" TIMESTAMP (3), 
	"KIND" NUMBER(*,0), 
	"MODIFYTIME" TIMESTAMP (3), 
	"THETYPE" NUMBER(*,0), 
	"ISRECOMENDED" NUMBER(1,0), 
	"USERID" NUMBER(*,0), 
	"STRINGVALUE" NVARCHAR2(256), 
	"SCALARVALUE" NUMBER(38,20), 
	"INTVALUE" NUMBER(*,0), 
	"COMMENT_" VARCHAR2(2000 BYTE)
   ) ON COMMIT PRESERVE ROWS ;

... and here are some parts of SSDL file:

Code: Select all

  
    
      
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
  
. . . . . . . . . .

The functions return correct recordset in Oracle SQL developer. Are there any limitations related to temporary tables?

Posted: **Wed 05 May 2010 12:48**

The problem is not related to temporary tables. The reason is the order of columns returned from function. It must exactly match the entity fields order. I was thinking that it is not important becasue of mapping schema.

Devart Forums

Type conversion after stored procedure execution

Type conversion after stored procedure execution