SQL & PL/SQL

Hi,

This is my situation, I have an emp table that has active status column in it. Say when a employee enters wrong info thrice to log in to the company's website, I would like to lock employee's account and activate it only after 24 hours.

So far, I have locked the account and reactivated that by using DBMS_JOB.SUBMIT in SQL PLUS. But when I put the same code in a stored procedure within a package the job fails. Any idea why?

Here is the part of my code inside the stored procedure....this is to execute in 30 secs

UPDATE emp_table SET active_status = 'N' WHERE emp_id = P1 ; -- account locked
DBMS_JOB.SUBMIT(l_v_job_no ,' UPDATE emp_table SET active_status = ''Y'' WHERE emp_id = :p1;', SYSDATE + 30/86400); --activate account after 30 secs

I would really appreciate if any of you can help me with this.

Thanks.