Oracle Stored Procedure that submits a job to run only once.
Often we need to run a procedure that takes a long time and it is not reasonable to wait for it to finish (not to mention, if it is called from a web page, the page will time out).
In such cases, call a procedure that will submit the request to the server and leave it there to run as long as needed.
Scroll to the bottom to copy the source code from a textarea.
|
1. Procedure Source Code:
CREATE OR REPLACE PROCEDURE DBMS_JOB_SUBMIT(PROC_NAME IN VARCHAR2)
IS
INT_JOB_ID NUMBER := NULL;
BEGIN
DBMS_JOB.SUBMIT (
job => INT_JOB_ID,
what => PROC_NAME || ';', -- What to run
next_date => SYSDATE, -- Start right away
interval => NULL -- Run only once
);
COMMIT;
END;
2. Call from TOAD:
Note: In this example, we are calling a procedure that requres two parameters - varchar2 and date.
EXEC DBMS_JOB_SUBMIT('MY_PACKAGE.MY_PROCEDURE (''MY_VARCHAR_PARAMETER'', ''' || TO_DATE('MY_DATE_VALUE', 'MM/DD/YYYY') || ''')')
|
To copy the source code, click inside the textbox => Ctrl-A => Ctrl-C
|