0

My databse is oracle 11.1 and Font End application is java

For that application i done like below

Example:

create or replace
PACKAGE TYPES AS
type cursorType is ref cursor;

END TYPES;


CREATE OR REPLACE PROCEDURE EMPInfo
(
   P_EmpNo IN NUMBER(4),
   Get_EmpInfo OUT TYPES.CURSORTYPE
)
AS
BEGIN
OPEN Get_EmpInfo 
FOR
SELECT E.Ename,D.Dname,E.Hiredate
FROM EMP E INNER JOIN DEPT D
ON E.Deptno=D.Deptno;
END EMPInfo;

but this concept is not able to work in multiple sessions(like multiple users not able to accessing that application) we are getting Error is "ORA-01000: maximum open cursors exceeded "

without using Refcursor as a out parameter,How should i pass the Result Set to the my front End application

Thanks

Ragav

Mandar Pandit
  • 2,171
  • 5
  • 36
  • 58
user3797729
  • 87
  • 1
  • 4
  • 11
  • I think you are not closing cursors, that is why you see this error. Try to add statement "CLOSE Get_EmpInfo;" before procedure end. – Magic Wand Aug 19 '14 at 07:45
  • See my [answer](http://stackoverflow.com/a/25324431/3686755) about this error – neshkeev Aug 19 '14 at 07:48
  • @Miljenko this is a bad idea, the author of the question wants to use this cursor outside of the PL/SQL procedure, this is why he/she declared the `Get_EmpInfo` parameter as an `OUT` parameter. – neshkeev Aug 19 '14 at 08:08
  • @zaratustra My bad, sorry! We don't see how this procedure is called, probably from Java, but of course, cursors should be closed after use outside of procedure. – Magic Wand Aug 19 '14 at 08:14
  • @Miljenko I agree with it, you have to close a cursor after you use it, not before. – neshkeev Aug 19 '14 at 08:17

0 Answers0