Require best approach to get cursor from oracle

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Vipul.Pujari
Posts: 4
Joined: Mon 04 Jan 2016 13:05

Require best approach to get cursor from oracle

Post by Vipul.Pujari » Fri 15 Jan 2016 15:09

Hello,

Can someone advise me best approach to fetch cursor from oracle function.

Right now I am trying to achieve it by below code

Code: Select all

_dbContext.Database.SqlQuery<T>(queryText);
queryText will contain my query to oracle database

sample

Code: Select all

SELECT testFunc('test', 'test', 1, 0, 0) FROM DUAL
Thanks,
Vipul

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

Re: Require best approach to get cursor from oracle

Post by Shalex » Fri 15 Jan 2016 17:04

Please refer to https://www.devart.com/dotconnect/oracl ... rsors.html. Is that what you need?

Vipul.Pujari
Posts: 4
Joined: Mon 04 Jan 2016 13:05

Re: Require best approach to get cursor from oracle

Post by Vipul.Pujari » Sun 17 Jan 2016 06:39

Thanks Shalex for reply

We are using fluent api with poco classes so cannot use devart entity developer

Thanks

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

Re: Require best approach to get cursor from oracle

Post by Shalex » Mon 18 Jan 2016 09:45

Try this way:

Code: Select all

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');

CREATE FUNCTION GET_DEPT_FUNC RETURN SYS_REFCURSOR
AS
cur SYS_REFCURSOR;
BEGIN
  OPEN cur FOR
  SELECT *
    FROM DEPT
    ORDER BY DEPTNO;
  RETURN cur;
END;

Code: Select all

var result = _dbContext.Database.SqlQuery<DEPT>("begin :p := GET_DEPT_FUNC(); end;", new OracleParameter("p", OracleDbType.Cursor, System.Data.ParameterDirection.Output)).ToList();

Post Reply