0

I have parent table and child table, with 1 to 0..N mapping, i.e. one parent record can have 0 or many child records.

Query:

select parent_Table.*  , cursor (select * from child_Table child where child.id = parent.id) child_data
from parent_Table parent

On the Java side, i am using rowMapper to convert this into POJO of parent and child classes. But here I may get following error, if the number of parent record for which child record exists, exceeds number of allowed open cursors with Oracle.

ORA-01000: maximum open cursors exceeded

Is there any alternative to above approach ?

Sohan Soni
  • 1,207
  • 21
  • 35
  • you could try to change sql to use LEFT JOIN, may be even with analytical function like 'row_number()/count() over(partition by parent.id)' to distinguish parent records. – hahn Mar 21 '16 at 08:53
  • most probably you application leaks resources. you can either fix you app or you can use xml to convert child's data into single XML document. – ibre5041 Mar 21 '16 at 09:18
  • Maybe read through this for some pointers on why you may be hitting this wall.http://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded – Michael Broughton Mar 21 '16 at 13:43

0 Answers0