Submitting DBMS JOB with Store Procedure having both IN and OUT Parameter.

1) Create Procedure

create or replace
PROCEDURE SP_JOB_TEST_PR
(
INPUTDATA IN NUMBER ,OUTPUTDATA OUT VARCHAR2
) AS
BEGIN
— Do what ever you want here
DBMS_OUTPUT.PUT_LINE (‘Procedure Executed’);
END SP_JOB_TEST_PR;

2) Submit the Job
BEGIN
DECLARE
v_JobNum NUMBER;
BEGIN
DBMS_JOB.SUBMIT(v_JobNum,’
declare
INPUTDATA NUMBER;
OUTPUTDATA VARCHAR2(200);
begin
PERIOD := 10;
SP_JOB_TEST_PR(INPUTDATA, OUTPUTDATA);
end;
‘,sysdate,’trunc(sysdate)+1+9/24’); — Runs every day 9 AM
COMMIT;
END;
END;

3) SQL to Check Job Details
select * from DBA_JOBS;

4) SQL to Check for Running Jobs
select * from dba_jobs_running;

5) STOP a Job
EXEC DBMS_JOB.BROKEN(job#,TRUE);
ex:
EXEC DBMS_JOB.BROKEN(223,TRUE); — 223 is your job id

After this command in DBA_JOBS table the BROKEN Field should be marked as Y