I want to take count of a table inside a parent procedure while the table is being populated by another/child procedure. This second/chile procedure is started as a job from inside the first/parent. The above is a simplistic description of certain decisions I need to take inside the first/parent based on the count. Example, If the row-count value of the table read inside the first/parent reaches a certain value I want to kill/remove the second/child job. However, I am always getting the count(*) = 0 inside the first/parent, even though a select * on the table shows that the child procedure is populating the table. What could be the reason and what is the way of getting a count ? Below is a test script :
create table test_j(col1 number);
create or replace PROCEDURE parent_job_sp
as
g_jobid_child number := -1;
v_child_cnt number := -1;
begin
DBMS_OUTPUT.PUT_LINE ('START - parent_job_sp');
DBMS_JOB.SUBMIT(g_jobid_child,'GFF_LOAD.child_job_sp;');
COMMIT;
DBMS_OUTPUT.PUT_LINE ('g_jobid_child='||g_jobid_child);
while (v_child_cnt<=8)
loop
DBMS_OUTPUT.PUT_LINE ('v_child_cnt='||v_child_cnt);
execute immediate 'select count(*) from test_j' into v_child_cnt;
if v_child_cnt >= 8
then
BEGIN
DBMS_JOB.remove(g_jobid_child);
COMMIT;
DBMS_OUTPUT.PUT_LINE ('REMOVED JOB '||g_jobid_child);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE=-23421
THEN DBMS_OUTPUT.PUT_LINE('THEN');
ELSE DBMS_OUTPUT.PUT_LINE('ELSE');
END IF;
END;
end if;
DBMS_OUTPUT.PUT_LINE ('OUTSIDE IF');
end loop;
DBMS_OUTPUT.PUT_LINE ('OUTSIDE WHILE');
exception --- proc-level exception
when others then
DBMS_OUTPUT.PUT_LINE('parent_job_sp - '||SQLERRM);
end; --- end proc
create or replace PROCEDURE child_job_sp
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
for cur_test in
(
select rownum row_num from dual connect by level<=1000
)
loop
insert into test_j values (cur_test.row_num);
commit;
end loop;
COMMIT;
exception --- proc-level exception
when others then
DBMS_OUTPUT.PUT_LINE('child_job_sp - '||SQLERRM);
end; --- end proc
--- then run from SQL*Plus : exec parent_job_sp
--- I always get v_child_cnt=0 even though the result of the select count(*) from test_j is 1000.
What could be the reason and what is the way of getting a count ?