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 CSMS_PROCESS.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

^ Top ^

List of all ORACLE Codes