It is very strange issue. Bellow is the code

create or replace PROCEDURE SP_JLD_T_MTH_VehicleAccessory
(
    DBOwner varchar2,--"GlassesGesDev."
    ModifiedByUser varchar2,--"someone"
    ud_low_date varchar2,--"yyyy-mm-dd"
    ud_high_date varchar2,--"yyyy-mm-dd"
    Abort_Level_Immediate decimal,
    inRun_Number decimal,--
    Source_View varchar2,--"LD_VEHICLEACCESSORYDETAILS_VW"
    Target_Table varchar2,--"VEHICLEACCESSORYDETAILS"
    Prior_Details_View varchar2,--"VEHICLEACCESSORY_DATES_VW"
    Process_Control_Table varchar2--"REF_GG_PROCESS_CONTROL"
) AS
    CURSOR c_trcqr_lurn_zone_update IS select * from GlassesGesDev.LD_VEHICLEACCESSORYDETAILS_VW where rownum < 10000; 
-- if I put 1000 instead, the fetch bulk collect will be passed without any issue
    
    TYPE t_emp IS TABLE OF GlassesGesDev.LD_VEHICLEACCESSORYDETAILS_VW%ROWTYPE;--REC_DATA;
    recs t_emp;
 
BEGIN
    dbms_output.enable();
    --ds_veh_acc_det_data := T_VEHICLEACCESSORIES();
   
    OPEN c_trcqr_lurn_zone_update;
    Loop
        FETCH c_trcqr_lurn_zone_update BULK COLLECT INTO recs LIMIT 1000;
        EXIT WHEN recs.COUNT = 0;
        v_tmp := v_tmp + 1;
    end LOOP;
    close c_trcqr_lurn_zone_update;
    dbms_output.put_line('V_tmp is: ' || v_tmp);
    
END SP_JLD_T_MTH_VehicleAccessory;

If I put 10000 above there will be some errors.
Connecting to the database Glasses - uatb.
Debugger attempting to connect to database.
Executing PL/SQL: DECLARE
                    id VARCHAR2( 30 );
                  BEGIN
                    id := DBMS_DEBUG.initialize( '*.*.*.*:1738887031655', 0 );
                    DBMS_DEBUG.debug_on( TRUE );
                  END;
Debugger connected to database.
Source breakpoint: SP_JLD_T_MTH_VEHICLEACCESSORY.pls:92
Debugger connection to debuggee process has been lost.
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-00604: error occurred at recursive SQL level 1
ORA-01013: user requested cancel of current operation
ORA-06512: at "GLASSESGESDEV.SP_JLD_T_MTH_VEHICLEACCESSORY", line 94
ORA-06512: at line 24
Executing PL/SQL: BEGIN
                    DBMS_DEBUG.debug_off();
                  END;
Process exited.
Disconnecting from the database Glasses - uatb.
Debugger disconnected from database.

The dev tool is SQL Developer Version 23.1.1.345 Build 345.2114

Oracle is running in AWS version_full 19.25.0.0.0

Can someone shed light on me? I suspect it is something to do with cache or tablespace, but not sure is it true or not and how to solve it. thanks.