Hi Tom,

We are encountering the ORA-01044 Error - ORA-01044: size 300000000 of buffer bound to variable exceeds maximum 268431360

Basically we are building the LIST of 50K records in Python and passing the LIST to the Stored Procedure. The Stored Procedure will accept the LIST and insert values into the table.

Below are the Stored Procedure details.

TYPE data_array IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;

PROCEDURE pkg_bulk(p_val_list IN OUT NOCOPY data_array)
IS
BEGIN
IF p_val_list IS NOT NULl
THEN
FORALL in 1..p_val_list.COUNT
INSERT INTO XYZ VALUES p_val_list(I);
END;
EXCEPTION:
...
END;

Python Code
---------

Step1: Execute the sql query
Step2: For Each result item in the Cursor, Put the result in the LIST (ie LIST.APPEND(cursor_result) . Once the result count reaches 50K call the Stored Procedure pkg_bulk.
Step3: Continue step2 until DATA is available.

We are getting the error in Step2. When we reduce the records limit from 50K to 25K records, it runs fine.

We would like to know root cause of the issue - ORA-01044: size 300000000 of buffer bound to variable exceeds maximum 268431360.

Database has sufficient PGA memory - 3GB

Which database parameter 268431360 the oracle complain about?

Please advise.

Thanks,
Bala

SQL> select 256 * 1024 * 1024 from dual;

256*1024*1024
-------------
    268435456