I build an inheritance tree of object views in Oracle. The tree includes the root view Person and its three children: student, professor, manager.
then I have this query
select value(p) from person p where <some predicates>;
If I execute this query in SQL-Plus, it runs well
But if I execute this via JDBC API and have ResultSet rs hold the result, then the rs.next() operation thows this Exception
ORA-04031: unable to allocate 8192 bytes of shared memory ("large pool","unknown object","sort subheap","sort key")
I try to refine the result and find out that if the result set contains just one type of person (either student or professor or manager) then there is no exception even though the result size is so large.
But if the result contains more than one person type, then the exception occurs even though the result size is much smaller. I dont know how to solve this problem. Please help me! Thanks in advance!
Note: This exception just occurs when I execute the query via JDBC API. With SQL-Plus, there is no error :((