Page 1 of 1
Require best approach to get cursor from oracle
Posted: Fri 15 Jan 2016 15:09
by 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
Re: Require best approach to get cursor from oracle
Posted: Fri 15 Jan 2016 17:04
by Shalex
Re: Require best approach to get cursor from oracle
Posted: Sun 17 Jan 2016 06:39
by Vipul.Pujari
Thanks Shalex for reply
We are using fluent api with poco classes so cannot use devart entity developer
Thanks
Re: Require best approach to get cursor from oracle
Posted: Mon 18 Jan 2016 09:45
by 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();