Click here to Skip to main content
15,887,812 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a function and its return type is a nested table type. I have created a nested table type local variable in the function and the variable gets data from cursor with the help of bulk collect .During the bulk collect operation maybe after 20,000+ data were processed,an exception is thrown "ORA-21780: Maximum number of object durations exceeded error".

The function is called multiple times in an iterative way due to more rows and hence bulk collect operation also runs many times.

How to fix this issue by changing the code?

The below code is throwing an error.

NOTE: The function is called many times.


Iam working in Oracle 11g.

What I have tried:

FUNCTION fn_test_join(test_cursor IN SYS_REFCURSOR)
  RETURN test_LARGE_JOIN IS
  lv_data test_LARGE_JOIN := test_LARGE_JOIN();
  c_limit PLS_INTEGER := 100;
BEGIN
  
  LOOP
    FETCH test_cursor
      BULK COLLECT INTO lv_data

    EXIT WHEN test_cursor%NOTFOUND;
  END LOOP;

  RETURN lv_data;
END fn_test_join;
Posted
Comments
Dave Kreskowiak 20-Jun-23 9:16am    
I don't do Oracle and am no expert, but doesn't that means something like "Stack Overflow"? The function has called itself so many times, the call limit has been exceeded, maybe something like 65 thousand times?

1 solution

This typically happens if there is infinite recursion in the PL/SQL function that is being executed.
You should change the condition of recursion to avoid unlimited loop.

In other hand, your issue might be related to memory limitations per session. See: https://dba.stackexchange.com/questions/48845/ora-21780-maximum-number-of-object-durations-exceeded[^]


I'd suggest to register to Oracle support to get more details[^] about your issue.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900