SQL & PL/SQL

I have searched on the forum for this topic and didn't find enough information to solve my problem. So I seek more inputs.

I am running Oracle 10.2.0.4 on Windows server.

I have a big table with 76 columns and 74 million records. The table has been partitioned and indexed. As a part of monthly ETL process, I need to delete around 1500 records from this huge table first. Then rebuild up new monthly records inserted into this table. To speed up this deletion, I use FORALL to do BULK DELETE the records. However, it didn't work or say it takes long time to process. If I use single SQL, it takes about 30 minutes to delete 1500 records. I post the code here. Please help me to point out my problems.

Declare

n_limit constant number := 100000;

TYPE t_id IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
v_id t_id;

CURSOR c IS
select distinct id FROM staging_table WHERE id_type = 'H';

BEGIN

OPEN c;
LOOP
FETCH c BULK COLLECT INTO v_id limit vn_limit;
exit when v_id.COUNT = 0;

FORALL i IN v_id.FIRST .. v_id.LAST
DELETE FROM BIG_TABLE WHERE id = v_id(i);

END LOOP;

END;
/

Above PL/SQL block runs into long time without error and result. Is there anyting wrong?

Some say that if the work can be done by single SQL, don't use BULK DELETE. So I tried to use this:

Declare

sql_stmt varchar2(500);

Begin

sql_stmt := 'delete from big_table where id in (select id from staging_table where id_type = 'H');

EXECUTE IMMEDIATE sql_stmt;

commit;

end;
/

This single SQL also run into ORA-00904 error " Invalid Identifier" -- 'H'. I haven't figured out the reason to cause this. Please help me out.