exec vam_proc1('AC00007','01-APR-06','28-FEB-07')

You should not pass date as varchar2 data type. I think in 9i/8i, the procedure simply hangs because of mismatch data type.

What's your oracle release?