1600 PMP mock questions 1400 CAPM mock questions 800 SCJP 6 mock questions 600 OCAJP 7 mock questions 590 OCPJP 7 mock questions 556 SCWCD 5 mock questions 500 OCEJWCD 6 mock questions pdfDownload (java,struts, hibernet etc)

ORACLE Tutorials

Execute jobs in parallel in Oracle:

CREATE TABLE parallel_executions
(
jobid NUMBER(13, 0) ,
processid NUMBER(13, 0) ,
parent_name VARCHAR2(50)
)
/

grant execute on dbms_lock to ... -- only for testing the procedure bellow
/

/*************************** Create procedure *************************/

CREATE OR REPLACE PROCEDURE aggregate_in_parallel
AS
v_job NUMBER;
v_parent_name parallel_executions.parent_name%TYPE := 'LOCK_SLEEP';
v_cnt NUMBER;
BEGIN
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(30);');

INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 1, v_parent_name
);

COMMIT;
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(20);');

INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 2, v_parent_name
);

COMMIT;
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(10);');

INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 3, v_parent_name
);

COMMIT;

LOOP
DBMS_LOCK.sleep (20);

DELETE FROM parallel_executions pe
WHERE NOT EXISTS (SELECT 1
FROM user_jobs uj
WHERE uj.job = pe.jobid)
AND parent_name = v_parent_name;

COMMIT;

SELECT COUNT (*)
INTO v_cnt
FROM parallel_executions
WHERE parent_name = v_parent_name;

EXIT WHEN (v_cnt = 0);
END LOOP;
END;
/

/*************************** Test it *************************/
SET serveroutput on

BEGIN
aggregate_in_parallel;
DBMS_OUTPUT.put_line
('Finish .... continue with others serial procedures ...');
END;
/

/*************************** Create syncronize table *************************/

CREATE TABLE parallel_executions
(
jobid NUMBER(13, 0) ,
processid NUMBER(13, 0) ,
parent_name VARCHAR2(50)
)
/

grant execute on dbms_lock to ... -- only for testing the procedure bellow
/

/*************************** Create procedure *************************/

CREATE OR REPLACE PROCEDURE aggregate_in_parallel
AS
v_job NUMBER;
v_parent_name parallel_executions.parent_name%TYPE := 'LOCK_SLEEP';
v_cnt NUMBER;
BEGIN
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(30);');

INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 1, v_parent_name
);

COMMIT;
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(20);');

INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 2, v_parent_name
);

COMMIT;
DBMS_JOB.submit (v_job, 'dbms_lock.sleep(10);');

INSERT INTO parallel_executions
(jobid, processid, parent_name
)
VALUES (v_job, 3, v_parent_name
);

COMMIT;

LOOP
DBMS_LOCK.sleep (20);

DELETE FROM parallel_executions pe
WHERE NOT EXISTS (SELECT 1
FROM user_jobs uj
WHERE uj.job = pe.jobid)
AND parent_name = v_parent_name;

COMMIT;

SELECT COUNT (*)
INTO v_cnt
FROM parallel_executions
WHERE parent_name = v_parent_name;

EXIT WHEN (v_cnt = 0);
END LOOP;
END;
/

/*************************** Test it *************************/
SET serveroutput on

BEGIN
aggregate_in_parallel;
DBMS_OUTPUT.put_line
('Finish .... continue with others serial procedures ...');
END;
/
This script creates a procedure to run in parallel others procedures.

In the given sql code, the following three jobs are executed in parallel (replace them with your own tasks):
- dbms_lock.sleep(30);
- dbms_lock.sleep(20);
- dbms_lock.sleep(10);

Reviews and Comments


PMP, CAPM, PMI is a registered certification mark of the Project Management Institute, Inc

Copyright © www.techfaq360.com 2016


About US | Contact US | Privacy Policy | Terms and Conditions  | Website disclaimer  | Cancellation and Refund Policy  | Shipping & Delivery Policy