Hi guys,
I have a problem when I try to start a stored procedure with DBMS_JOB.SUBMIT.
I have the package "dafneMultithread":
CREATE OR REPLACE PACKAGE DAFNE.dafneMultithread AS
PROCEDURE start_job(p_procedure_name IN VARCHAR2);
END dafneMultithread;
/
and the package body:
CREATE OR REPLACE PACKAGE BODY DAFNE.dafneMultithread AS
PROCEDURE start_job(p_procedure_name IN VARCHAR2) IS
v_job NUMBER; -- ID of the job
BEGIN
DBMS_JOB.SUBMIT(v_job, p_procedure_name);
COMMIT;
END start_job;
END dafneMultithread;
/
The issue is that when I try to execute the procedure with this script:
DECLARE
BEGIN
dafneMultithread.start_job('cmpHpfmsPostAMDService;');
END;
/
works well but the procedure doesn't start (TOAD shows a succesfull message).
However, in a version Oracle 11 works fine (the procedure starts).
So, What is the problem?
The job_queue_processes is greater than 0 and if I check the table user_jobs I see that appears the procedures that I execute with the script:
1 DAFNE DAFNE DAFNE 26/09/2018 12:34:10 12:34:10 0 N null cmpHpfmsPostAMDAccount; NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' 0102000000000000 0
2 DAFNE DAFNE DAFNE 26/09/2018 12:34:28 12:34:28 0 N null cmpHpfmsPostAMDContact; NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' 0102000000000000 0
So, it seems that there are a problem with the starting of the jobs, isn't it?
Thanks!
Regards.
Are you it *never* starts? Or just starts later than expected ?
I'm seeing some latency in my *first* submissions after startup, eg
SQL> create table t
2 ( submitted timestamp,
3 started timestamp );
Table created.
SQL>
SQL> create or replace
2 procedure prc is
3 begin
4 update t set started = systimestamp;
5 commit;
6 end;
7 /
Procedure created.
SQL>
SQL> declare
2 j int;
3 begin
4 dbms_job.submit( j,'prc;');
5 insert into t(submitted) values (systimestamp);
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
SUBMITTED
---------------------------------------------------------------------------
STARTED
---------------------------------------------------------------------------
27-SEP-18 01.01.58.280000 PM
SQL> /
SUBMITTED
---------------------------------------------------------------------------
STARTED
---------------------------------------------------------------------------
27-SEP-18 01.01.58.280000 PM
27-SEP-18 01.02.21.150000 PM
but after that, it is snappy
SQL> create table t
2 ( submitted timestamp,
3 started timestamp );
Table created.
SQL>
SQL> create or replace
2 procedure prc is
3 begin
4 update t set started = systimestamp;
5 commit;
6 end;
7 /
Procedure created.
SQL>
SQL> declare
2 j int;
3 begin
4 dbms_job.submit( j,'prc;');
5 insert into t(submitted) values (systimestamp);
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
SUBMITTED
------------------------------------------------------------
STARTED
------------------------------------------------------------
27-SEP-18 01.03.25.529000 PM
SQL> /
SUBMITTED
------------------------------------------------------------
STARTED
------------------------------------------------------------
27-SEP-18 01.03.25.529000 PM
27-SEP-18 01.03.26.146000 PM
Having said that, delays like that have been seen across versions in the past, typically due to issues with the job coordinator process - so I'd raise a Support call.