PL SQL sys.ODCIObjectList size limit-CodePudding

I made Pl/Sql function like this:

create or replace function F$RETURN_ARRAY(vp_str varchar2, vp_count number) return 

sys.ODCIObjectList
as
  ret sys.ODCIObjectList := sys.ODCIObjectList();
begin
  FOR i IN 1 .. vp_count LOOP
    ret.extend;
    ret(ret.last) := sys.odciobject(CONCAT(vp_str, i), i);
  END LOOP;
  return ret;
end;

It works fine when I call it like this:

select objectschema no, objectname place  
from table(F$RETURN_ARRAY('no', 32))

But when I call it like this:

select objectschema no, objectname place  
from table(F$RETURN_ARRAY('no', 33))

I get error: ORA-06532: Subscript outside of limit. Obviously 32 is the size limit for sys.ODCIObjectList. How do I maybe change this limit? Or maybe I am doing this wrong way and there is better way for me to achieve desired result? I need to return result that contains maybe thousands or rows.

Thanks in advance

CodePudding user response：

How do I maybe change this limit?

You do not. It is a built-in data type in the SYS schema; you do not touch the definition of the type in case you inadvertently break the database.

Or maybe I am doing this wrong way and there is better way for me to achieve desired result?

Define your own data type:

CREATE TYPE object_list AS TABLE OF SYS.ODCIOBJECT;

Then use it in your function:

create function F$RETURN_ARRAY(
  vp_str   VARCHAR2,
  vp_count PLS_INTEGER
) return object_list
AS
  ret object_list := object_list();
BEGIN
  IF vp_count > 0 THEN
    ret.EXTEND(vp_count);
    FOR i IN 1 .. vp_count LOOP
      ret(i) := sys.odciobject(CONCAT(vp_str, i), i);
    END LOOP;
  END IF;
  RETURN ret;
END;
/

Then:

select objectschema no, objectname place  
from table(F$RETURN_ARRAY('no', 33))

Works.

db<>fiddle here