CREATE OR REPLACE PROCEDURE AMATSON.RELOAD_AE (p_array_size IN PLS_INTEGER DEFAULT 100)
IS

TYPE ARRAY IS TABLE OF FSADM.ft_acct_xtract@admprd8i.tceq.state.tx.us%ROWTYPE;
ae_data ARRAY;

--CURSOR C_FY IS
--SELECT DISTINCT(FY)
--FROM FSADM.ft_acct_xtract@admprd8i.tceq.state.tx.us
--WHERE FY > '2008';


CURSOR C_AE IS
SELECT *
FROM FSADM.ft_acct_xtract@admprd8i.tceq.state.tx.us;

BEGIN

--FOR v_fy in c_fy LOOP

    OPEN C_AE;

        LOOP
        FETCH C_AE BULK COLLECT INTO ae_data LIMIT p_array_size;

            FORALL i IN 1..ae_data.COUNT
            INSERT /*+ APPEND */ INTO AMATSON.ft_acct_xtract_temp VALUES ae_data(i);

            EXIT WHEN C_AE%NOTFOUND;
            
        END LOOP;
        DBMS_OUTPUT.PUT_LINE( 'I am outside the loop.');
        COMMIT; 
           
    CLOSE C_AE;

--END LOOP;
--DBMS_OUTPUT.PUT_LINE( 'I am outside the outer loop.');
    
END RELOAD_AE;
/