Search code examples
sqldatabaseoracle-databaseoracle12cnested-table

What is maximum rows count in oracles nested table

CREATE TYPE nums_list AS TABLE OF NUMBER;

What is maximum possible rows count in oracle's nested table ?

UPDATE

CREATE TYPE nums_list  AS TABLE OF NUMBER;

CREATE OR REPLACE  FUNCTION  generate_series(from_n NUMBER, to_n NUMBER)
RETURN nums_list AS
ret_table nums_list := nums_list();
BEGIN

  FOR i IN from_n..to_n LOOP
    ret_table.EXTEND;
    ret_table(i) := i;
  END LOOP;
  RETURN ret_table;

END;


SELECT count(*)   FROM TABLE ( generate_series(1,4555555) );

This gives error: ORA-22813 operand value exceeds system limits, Object or Collection value was too large

Solution

  • The range of subscripts for a nested table is 1..2**31 so you can have 2**31 elements in the collection. That limit hasn't changed since at least 8.1.6 though, of course, it might change in the future.