Problem with the csv_util_pkg package

[tretyakovmax]

I noticed a problem with the csv_util_pkg package (function clob_to_csv). When the size of the CSV-file is large (in my case more than 7,000,000 records) an error occurs "ORA-01426: numeric overflow". The reason for the error is that the variables used inside the function are of the PLS_INTEGER type has a range of up to 2147483647.

function clob_to_csv (p_csv_clob in clob,
                      p_separator in varchar2 := g_default_separator,
                      p_skip_rows in number := 0,
                      p_rows_count in number default null) return t_csv_tab pipelined
as
  l_csv_clob               clob;
  l_line_separator         varchar2(2) := chr(13) || chr(10);
  l_last                   pls_integer; -- <<< these variables
  l_current                pls_integer; -- <<< these variables
  l_line                   varchar2(32000);
  l_line_number            pls_integer := 0; -- <<< these variables
  l_from_line              pls_integer := p_skip_rows + 1; -- <<< these variables

Replacing PLS_INTEGER with INTEGER solves this problem. Is this valid? Or is the use of PLS_INTEGER the only correct one?

[mortenbra]

See the PL/SQL data types chapter in the Oracle docs: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/datatypes.htm#i46029

You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2147483648 to 2147483647, represented in 32 bits. PLS_INTEGER values require less storage than NUMBER values and NUMBER subtypes. Also, PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic. For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range. For calculations outside the range of PLS_INTEGER, you can use the INTEGER datatype.