Oracle Database Discussions

Can anyone tell me how to remove the DBMS_JOB submitted by user1 from SYSTEM user?

What are privileges needed for this to happen?

Our application submits few jobs, before we submit we wanted to delete the existing job and submit the job.
The application first connects to “SYSTEM” and alters the session by setting the current_schea=user1 and submits the job.

During our application upgrade process again we are connected to the DB with SYSTEM and try to remove the job but I am not able to remove the existing job, rather it creates the second job (I can remove this job latter by connecting to user1). I like to know how to delete from SYSTEM account. I feel I am missing some grants.

Following is the procedure i am using to remove the job.

create or replace procedure job_remove (vwhatif in varchar2)
is

cursor cujobs(whatis varchar2) is
select job from dba_jobs
where what like whatis;

whatif varchar2(100);
reccujobs cujobs%ROWTYPE;

begin

whatif :='%'||vwhatif||'%';
for reccujobs in cujobs(whatif)
loop
dbms_job.remove(reccujobs.job);

end loop;

end;
/

This is the error i am getting

ORA-23421: job number 161 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 171
ORA-06512: at line 1


Thanks for your time.

Regards
Sarathi