We just upgraded a few test databases from 12.1.0.2 to 19.8 and unfortunately realized that Tom technique for DIY paralellelizing is not working anymore.
--
Out parameter "JOB" from dbms_job.submit is not working as it should. "v_job" and "JOB" returns different sequences so procedure which is called from dbms_job.submit "Proc(JOB)" could not find record in our log-table.
--
I know, that dbms_job is in 19c just for backward compatibility and dbms_job now invokes DBMS_SHEDULER job, but anyway this sounds like a bug to me.
--
Do you know ... is there any workaround for this ?
Regards Dejan
TEST Case
===========
drop table my_job_parameters;
drop procedure my_lock_sleep;
create table my_job_parameters(job_id number, start_time date, start_note varchar2(30), end_time date, end_note varchar2(50));
--
create or replace procedure my_lock_sleep(p_sec in int)
is
begin
insert into my_job_parameters(job_id, start_time, start_note) values (0,sysdate, 'parameter '||p_sec);
update my_job_parameters set start_time = sysdate, start_note='start'||p_sec where job_id = p_sec;
dbms_lock.sleep(30);
update my_job_parameters set end_time = sysdate where job_id = p_sec;
end;
--
set serveroutput on
declare
l_job number;
begin
dbms_job.submit( l_job, 'my_lock_sleep(JOB);', sysdate);
--
dbms_output.put_line('my_lock_sleep('||l_job||');' );
dbms_output.put_line('Job Submitted - '||l_job);
--
insert into my_job_parameters(job_id) values( l_job );
commit;
end;
/
That is indeed an interesting behaviour - I will ask around internally to see if that was the intended/expected behaviour. It seems a bug to me.
But we can work around it without much trouble. Here's a similar version to yours
SQL> create table my_job_parameters
2 (job_id number,
3 param1 date,
4 param2 varchar2(30),
5 param3 int
6 );
Table created.
SQL> create table captured_details(msg varchar2(200));
Table created.
SQL> --
SQL>
SQL> create or replace
2 procedure my_program(p_job in int)
3 is
4 begin
5 --
6 -- select * into ... from my_job_parameters where job_id = p_job;
7 --
8 -- then processing continues
9 --
10 insert into captured_details values ('p_job='||p_job);
11 insert into captured_details values ('BG_JOB_ID='||sys_context('USERENV','BG_JOB_ID'));
12 insert into captured_details values ('FG_JOB_ID='||sys_context('USERENV','FG_JOB_ID'));
13 commit;
14 --
15 -- just we can look at dba_jobs_running / dba_scheduler_running_Jobs
16 --
17 dbms_session.sleep(60);
18 end;
19 /
Procedure created.
SQL>
SQL> declare
2 l_job number;
3 begin
4 dbms_job.submit( l_job, 'my_program(JOB);', sysdate);
5 insert into captured_details values ('DBMS_JOB returned job='||l_job);
6 insert into my_job_parameters values (l_job,sysdate,'x',1);
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from captured_details;
MSG
--------------------------------------------------------------------------------
DBMS_JOB returned job=110
p_job=99469
BG_JOB_ID=99469
FG_JOB_ID=
So you can see that the only info we have to *start* with is '99469' which we'd like to tie back to the job id of 110. It doesn't appear in the scheduler views
SQL> select * from dba_jobs_running
2 @pr
==============================
SID : 1228
JOB : 110
FAILURES : 0
LAST_DATE : 31-AUG-20 02.01.53.309000 PM +08:00
LAST_SEC : 14:01:53
THIS_DATE : 31-AUG-20 02.01.53.309000 PM +08:00
THIS_SEC : 14:01:53
INSTANCE : 0
PL/SQL procedure successfully completed.
SQL>
SQL> select * from dba_scheduler_running_Jobs
2 @pr
==============================
OWNER : MCDONAC
JOB_NAME : DBMS_JOB$_110
JOB_SUBNAME :
JOB_STYLE : REGULAR
DETACHED : FALSE
SESSION_ID : 1228
SLAVE_PROCESS_ID : 82
SLAVE_OS_PROCESS_ID : 26572
RUNNING_INSTANCE : 1
RESOURCE_CONSUMER_GROUP : OTHER_GROUPS
ELAPSED_TIME : +000 00:00:16.02
CPU_USED : +000 00:00:00.00
DESTINATION_OWNER :
DESTINATION :
CREDENTIAL_OWNER :
CREDENTIAL_NAME :
LOG_ID : 294592
PL/SQL procedure successfully completed.
SQL>
SQL> select * from dba_scheduler_Jobs
2 where job_name like 'DBMS%'
3 @pr
==============================
OWNER : MCDONAC
JOB_NAME : DBMS_JOB$_110
JOB_SUBNAME :
JOB_STYLE : REGULAR
JOB_CREATOR : MCDONAC
CLIENT_ID :
GLOBAL_UID :
PROGRAM_OWNER :
PROGRAM_NAME :
JOB_TYPE : PLSQL_BLOCK
JOB_ACTION : my_program(JOB);
NUMBER_OF_ARGUMENTS : 0
SCHEDULE_OWNER :
SCHEDULE_NAME :
SCHEDULE_TYPE : ONCE
START_DATE : 31-AUG-20 02.01.53.000000 PM +08:00
REPEAT_INTERVAL :
EVENT_QUEUE_OWNER :
EVENT_QUEUE_NAME :
EVENT_QUEUE_AGENT :
EVENT_CONDITION :
EVENT_RULE :
FILE_WATCHER_OWNER :
FILE_WATCHER_NAME :
END_DATE :
JOB_CLASS : DEFAULT_JOB_CLASS
ENABLED : TRUE
AUTO_DROP : TRUE
RESTART_ON_RECOVERY : FALSE
RESTART_ON_FAILURE : FALSE
STATE : RUNNING
JOB_PRIORITY : 3
RUN_COUNT : 0
UPTIME_RUN_COUNT :
MAX_RUNS :
FAILURE_COUNT : 0
UPTIME_FAILURE_COUNT :
MAX_FAILURES :
RETRY_COUNT : 0
LAST_START_DATE : 31-AUG-20 02.01.53.309000 PM +08:00
LAST_RUN_DURATION :
NEXT_RUN_DATE : 31-AUG-20 02.01.53.289000 PM +08:00
SCHEDULE_LIMIT :
MAX_RUN_DURATION :
LOGGING_LEVEL : OFF
STORE_OUTPUT : TRUE
STOP_ON_WINDOW_CLOSE : FALSE
INSTANCE_STICKINESS : TRUE
RAISE_EVENTS :
SYSTEM : FALSE
JOB_WEIGHT : 1
NLS_ENV : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA...
SOURCE :
NUMBER_OF_DESTINATIONS : 1
DESTINATION_OWNER :
DESTINATION :
CREDENTIAL_OWNER :
CREDENTIAL_NAME :
INSTANCE_ID :
DEFERRED_DROP : FALSE
ALLOW_RUNS_IN_RESTRICTED_MODE : FALSE
COMMENTS :
FLAGS : 9007199254880304
RESTARTABLE : FALSE
HAS_CONSTRAINTS : FALSE
CONNECT_CREDENTIAL_OWNER :
CONNECT_CREDENTIAL_NAME :
FAIL_ON_SCRIPT_ERROR : FALSE
PL/SQL procedure successfully completed.
However, because a scheduler job is a genuine database object, we can see where that number comes from
SQL> select * from user_objects
2 where object_type = 'JOB'
3 @pr
==============================
OBJECT_NAME : DBMS_JOB$_110
SUBOBJECT_NAME :
OBJECT_ID : 99469 <==============!!!!!
DATA_OBJECT_ID :
OBJECT_TYPE : JOB
CREATED : 31-AUG-20
LAST_DDL_TIME : 31-AUG-20
TIMESTAMP : 2020-08-31:14:01:53
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
NAMESPACE : 1
EDITION_NAME :
SHARING : NONE
EDITIONABLE :
ORACLE_MAINTAINED : N
APPLICATION : N
DEFAULT_COLLATION :
DUPLICATED : N
SHARDED : N
CREATED_APPID :
CREATED_VSNID :
MODIFIED_APPID :
MODIFIED_VSNID :
PL/SQL procedure successfully completed.
Armed with that, a small change to your proc lets you resume as per normal
SQL> create or replace
2 procedure my_program(p_job in int) is
3 l_job int;
4 begin
5 select replace(object_name,'DBMS_JOB$_')
6 into l_job
7 from user_objects
8 where object_id = p_job;
9
10 --
11 -- select * into ... from my_job_parameters where job_id = p_job;
12 --
13 -- then processing continues
14 --
15 insert into captured_details values ('p_job='||p_job);
16 insert into captured_details values ('l_job='||l_job);
17 commit;
18 --
19 -- just we can look at dba_jobs_running / dba_scheduler_running_Jobs
20 --
21 dbms_session.sleep(60);
22 end;
23 /
Procedure created.
SQL>
SQL> delete captured_details;
4 rows deleted.
SQL>
SQL> declare
2 l_job number;
3 begin
4 dbms_job.submit( l_job, 'my_program(JOB);', sysdate);
5 insert into captured_details values ('DBMS_JOB returned job='||l_job);
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from captured_details;
MSG
-------------------------------------------------------
DBMS_JOB returned job=111
p_job=99471
l_job=111
SQL>
But I'll find out if this is a bug and update the question