Tuesday, November 30, 2004

Will dbms_job work with policies?

Yes, they will!
DBMS_JOB executes in the context of a user.

Use the following to test:

Create a table:
create table out_ctx (usr_ctx number);

Create a procedure:
create procedure test_ctx
as
usr_ctx number;
begin
select sys_context('MY_CONTEXT','USER_ID') into usr_ctx from dual;

insert into out_ctx values (usr_ctx);
commit;
end;


Spool the job:
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'test_ctx;',
SYSDATE, 'SYSDATE + 1/720');
COMMIT;
END;
/
PRINT jobno


Check the result:
select * from out_ctx;

Delete the job:

BEGIN
DBMS_JOB.REMOVE(&job_number);
END;
/

No comments: