Call Oracle Stored Procedure / Map to Class

[PeterThomasMichael]

Hello,

is it possible to call an Oracle stored procedure using DotConnect and EfCore 1.1 or EF6? How?
(EF6 and Core are both configured with Fluent Api))
I would like to have an opportunity to do so just like you can do it using Microsoft database and EfProvider. Particularly I would like to map the results to the same class (in this sample TestNstrIdInstId)
(The sample code is using EfCore)
using (var sut = CreateContext(connectionString, dbType, efVersion))
{
var userType = sut.Set<TestNstrIdInstId>().FromSql("[icsys].[SP_InstNstrIds] {0}", "104,2447,2316").ToList();


public class TestNstrIdInstId
{
public decimal InstId { get; set; }
[Key]
public decimal NstrId { get; set; }
}

CREATE procedure icsys.SP_PersNstrIds (@P_NSTRIDs varchar(8000)) As ….


I’m currently using DotConnect Pro 9.2.162.
The solution should enable to use both a MSSQL or an Oracle Database, e.g. the entities, the results are mapped to, should be Independent from the way the call to DBContext is made. Different calls to the context would be okay.
I couldn’t find any suitable description yet.

Thank you

Thomas.

[Shalex]

We are processing your request.

[Shalex]

Please use an Oracle pipelined function:

Code: Select all

--DROP TABLE DEPT;
--CREATE TABLE DEPT (
--  DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
--  DNAME VARCHAR2(14) ,
--  LOC VARCHAR2(13)
--);
--INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
--INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
--INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
--INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

--DROP TYPE DEPT_TBL;
--DROP TYPE DEPT_T;
CREATE TYPE DEPT_T IS OBJECT(DEPTNO NUMBER(4), DNAME VARCHAR2(14), LOC VARCHAR2(13));
/
CREATE TYPE DEPT_TBL IS TABLE OF DEPT_T;
/

CREATE OR REPLACE FUNCTION GET_DEPT_PIPELINED (P_DEPTNO NUMBER) RETURN DEPT_TBL
PIPELINED IS
  P_CURSOR SYS_REFCURSOR;
  CURR_ROW DEPT_T := DEPT_T(NULL, NULL, NULL);
BEGIN
    OPEN P_CURSOR FOR SELECT DEPTNO, DNAME, LOC FROM DEPT WHERE DEPTNO > P_DEPTNO;
    LOOP
      FETCH P_CURSOR
      INTO  CURR_ROW.DEPTNO,
            CURR_ROW.DNAME,
            CURR_ROW.LOC;
      EXIT WHEN P_CURSOR%NOTFOUND;
      PIPE ROW (CURR_ROW);
    END LOOP;
    CLOSE P_CURSOR;
   RETURN;
END GET_DEPT_PIPELINED;
/

select * from TABLE(GET_DEPT_PIPELINED(10));

Code: Select all

var context = new ModelContext();
var result = context.Set<Dept>().FromSql("select * from TABLE(GET_DEPT_PIPELINED(10))").ToList();