Require best approach to get cursor from oracle

[Vipul.Pujari]

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]

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

[Vipul.Pujari]

Thanks Shalex for reply

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

Thanks

[Shalex]

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