1

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.

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
      LIMIT c_limit;

    EXIT WHEN test_cursor%NOTFOUND;
  END LOOP;

  RETURN lv_data;
END fn_test_join;
William Robertson
  • 15,273
  • 4
  • 38
  • 44
Maithri Shenoy
  • 21
  • 2
  • Do you get the same error if you remove the loop? I'm guessing the error is because you are trying to initialise more than 64K `test_LARGE_JOIN()` collections from SQL and is not related to the ref cursor input. I couldn't reproduce it in a quick test on 19c though, so wondering if you are hitting an 11g limitation. – William Robertson Jun 18 '23 at 10:04
  • Yes iam working innoracle 11g and I have not tried removing the loops but I tried commenting the whole loop code and added code like lv_data.extend(); lv_data(1):='test'; and it worked – Maithri Shenoy Jun 18 '23 at 11:40

0 Answers0