Hi Slimdave and stecal,
Thanks for your reply. The procedure is being called by informatica mapping where the parameter is passed.
The error is in the following line

EXECUTE IMMEDIATE tsql INTO arch_count;

Any comments and suggestions.