You sure can. There are a few gotchas to be wary of but here is a test case create or replace function testodpRefCursor( uniqueId IN NUMBER ,resultItems OUT NOCOPY SYS_REFCURSOR) RETURN NUMBER IS BEGIN OPEN resultItems for select level from dual connect by level < uniqueId ; return 1; END testodpRefCursor; I have found that … Read more
For that purpose, you might want to take a look at PIPELINED functions. You will have to declare explicit type at PL/SQL level though. That part will set the output column name: CREATE OR REPLACE TYPE my_rec AS OBJECT ( c CHAR, n NUMBER(1) ); CREATE OR REPLACE TYPE my_tbl AS TABLE OF my_rec; Now, … Read more
Oracle function or a stored procedure can be called using EntityManager in the following manner. For Oracle Function Create a function with sys_refcursor as return type CREATE OR REPLACE FUNCTION my_function (p_val IN varchar2) RETURN SYS_REFCURSOR AS my_cursor SYS_REFCURSOR; BEGIN OPEN my_cursor FOR SELECT emp_name FROM employees WHERE lower(emp_name) like lower(p_val||’%’); RETURN my_cursor; END; In … Read more