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 ?

0

There are 0 best solutions below