TITLE:
How to retrigger an external remote Job
Oracle external Job fires only once (an External, Remote, and Recurrent job):

INTRODUCTION:
My External, Remote, and Recurrent Oracle Job fires only once,
and then it remains RUNNING.
Then,
•If I stop the job from SQLDeveloper using STOP_JOB() it fires again,
•but if I try to stop the job on the last job instruction, it remains RUNNING
and doesn’t fire again

DESCRIPTION:
I need to create an Oracle job to create report files, in the future, and recurrently.
For example every day at 10hs.

To do this:
-I created an Oracle 11.2.0.4.0 Job using DBMS_SCHEDULER.create_job()
-that Job triggers a Linux script on another server, where the Weblogic
server is located.

The job is triggered and everything works correctly the FIRST TIME,
but it is never triggered again.


This happens because the Job remains RUNNING and then does not fire again,
since that should be in SCHEDULED status to be fired.

If I stop it by hand, using DBMS_SCHEDULER.stop_job(), on SQLDeveloper, it
works fine and re-shoots (since when Oracle receives the stop then passes it
to SCHEDULED status, and when it arrives the moment it re-shoots the Job)

In this point:
1 - I think it is the programmer who should be in charge of passing the job
to STOP when it finishes, for being a job of external type, remote and recurrent.
2 - But in the case that Oracle is the one that has to pass to it to STOP, which
I am not sure, the fact that Oracle do not do so may be indicating that the Agent
program that runs the Linux script on the other server fails to communicate to the
Oracle server that the job has finished, and therefore it remains RUNNING.

Thinking that the most probable case is 1,
I added the DBMS_SCHEDULER.stop_job() inside the Linux script, executing a sql script
with sqlplus.
Then,
-when I launch this script from a Linux console:
it works perfectly (creates the files, stops the job, and re-triggered when appropriate),
but
-when I trigger the script FROM THE JOB it does everything ok EXCEPT THE STOP of the
Job (creates the files, DOES NOT stop the job, and therefore does NOT fire again when
appropriate).

To verify that there are no errors and that the script really triggers, within the sql
script I added an update on a table before the stop and an update after the stop, and
BOTH work fine, therefore the sql script was executed, the stop_job was executed but it
could not change the status of the Job.
WHY ???
If I stop another Job it works.

With the job I also created the "credential", to connect to the Linux server
and I configured AGENT_NAME in the Agent configuration file
to use it in the "destination" of the Job
to connect Oracle SQL to the Agent running on the Linux server.

In short, the question is:
How to automatically re-trigger this External, Remote, and Recurrent job after
it worked well the first time ?



CODE:
-----------------------------------------------
Oracle version
-----------------------------------------------
select * from v$version;
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


-----------------------------------------------
Agent version
-----------------------------------------------
SELECT sys.DBMS_SCHEDULER.GET_AGENT_VERSION('AGENT_WEBLOGIC_13') FROM DUAL;
11.2.0.3.1


-----------------------------------------------
Linux version
-----------------------------------------------
cat /etc/*release
NAME="Red Hat Enterprise Linux Server"
VERSION="7.2 (Maipo)"

uname –a
Linux weblogic12clinux.org 3.10.0-327.28.3.el7.x86_64 #1 SMP Fri Aug 12 13:21:05 EDT 2016 x86_64 x86_64 x86_64 GNU/Linux

-----------------------------------------------
Create Job and Related Objects
-----------------------------------------------
Create "credential"
BEGIN
--Optional
dbms_scheduler.drop_job(
job_name => 'TEST_JOB'
);
--Optional
dbms_scheduler.drop_credential(
credential_name => 'MY_CREDENTIAL'
);
dbms_scheduler.create_credential(
username => 'oracle',
password => 'mypass123',
database_role => NULL,
windows_domain => NULL,
comments => 'Credential for TEST_JOB',
credential_name => 'MY_CREDENTIAL'
);
END;


Configure Agent on file "schagent.conf"
PORT = 1025
HOST_NAME = 192.168.0.13
AGENT_NAME= AGENT_WEBLOGIC_13
MAX_RUNNING_JOBS= 99
LOGGING_LEVEL= ALL


Create Job
--Optional
dbms_scheduler.drop_job(
job_name => 'TEST_JOB'
);
DBMS_SCHEDULER.create_job(
job_name => 'TEST_JOB',
job_type => 'EXECUTABLE',
number_of_arguments => 0,
job_action => '/u01/projects/MY_SCRIPT.sh',
credential_name => 'MY_CREDENTIAL',
repeat_interval => 'FREQ= DAILY; BYDAY= MON, TUE, WED, THU, FRI; BYHOUR= 10; BYMINUTE= 0; BYSECOND= 0',
auto_drop => false,
enabled => false
);


Assign 'Destination_name' to the Job
DBMS_SCHEDULER.set_attribute(
'TEST_JOB',
'destination_name',
'AGENT_WEBLOGIC_13'
);


Enable Job once completed
DBMS_SCHEDULER.enable(
'TEST_JOB'
);




-----------------------------------------------
Script Linux /u01/projects/MY_SCRIPT.sh
-----------------------------------------------
#Create files
#/u01/projects/MY_EXE
#Stop Job and updates table
sqlplus user_test/user_test @/u01/projects/MY_STOP_JOB.sql
exit 0
-----------------------------------------------


Script sql /u01/projects/MY_STOP_JOB.sql
----------------------------------------
EXEC my_STOP_JOB_proc;
EXIT;
-----------------------------------------------


Create table for updates
------------------------
CREATE TABLE RPTSUB_PARA
(
JOB_NAME VARCHAR2(30 BYTE) NOT NULL
, NO_RPTSUB NUMBER(10, 0) NOT NULL
, PRGQID NUMBER(10, 0)
, RUN_COMMENT VARCHAR2(20 BYTE)
, DEVELOP_COMMENT VARCHAR2(100 BYTE)
) ;

INSERT INTO RPTSUB_PARA
(
JOB_NAME
, NO_RPTSUB
, PRGQID
, RUN_COMMENT
, DEVELOP_COMMENT
)
VALUES (
'TEST_JOB'
, 439
, 97135
, ''
, ''
)
;



Procedure my_STOP_JOB_proc
--------------------------
create or replace PROCEDURE my_STOP_JOB_proc
IS
BEGIN
UPDATE
RPTSUB_PARA
SET
RUN_COMMENT = to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss')
WHERE
RPTSUB_PARA.NO_RPTSUB = 439;

--Try to stop Job
DBMS_SCHEDULER.stop_job('TEST_JOB' /*, force => true*/);


UPDATE
RPTSUB_PARA
SET
DEVELOP_COMMENT = to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') || '-QUEST-'
WHERE
RPTSUB_PARA.NO_RPTSUB = 439;
COMMIT;

END;
-----------------------------------------------



---------------------------------------------------------------
TEST procedure
---------------------------------------------------------------

--Stop job
BEGIN
dbms_scheduler.stop_job(
job_name => 'TEST_JOB'
);
END;

--Check actual time
SELECT to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') FROM DUAL;

--Change job's time to next minute
BEGIN
DBMS_SCHEDULER.set_attribute(
'TEST_JOB',
'repeat_interval',
'FREQ= DAILY; BYDAY= MON, TUE, WED, THU, FRI; BYHOUR= 15; BYMINUTE= 40; BYSECOND= 0'
);
END;

--Check job status BEFORE execution
select state, run_count, job_name, last_start_date, next_run_date,repeat_interval
from user_SCHEDULER_JOBS
where job_name='TEST_JOB';
--SCHEDULED, 3, 15:40

--Wait until the job is triggered
SELECT to_char(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') FROM DUAL;



--Check job status AFTER JOB execution
SELECT job_name, status, error#, additional_info
FROM user_scheduler_job_run_details WHERE job_name='TEST_JOB'
order by log_date desc;
--RUNNING, 3 ==> wrong, not stopped

--Check updates table
SELECT
job_name,
PRGQID,
RUN_COMMENT,
DEVELOP_COMMENT
FROM
MISC.RPTSUB_PARA
WHERE
RPTSUB_PARA.NO_RPTSUB = 439;
--Date was updated before and after STOP_JOB



--ReExecute script manualy
sh /u01/projects/MY_SCRIPT.sh

--ReCheck job status AFTER manual execution
SELECT job_name, status, error#, additional_info
FROM user_scheduler_job_run_details WHERE job_name='TEST_JOB'
order by log_date desc;
--SCHEDULED, 3 ==> Ok !!!

SQL> BEGIN
  2    begin
  3      dbms_scheduler.drop_program(
  4      program_name             => 'win_pgm_test', force=>true);
  5    exception
  6      when others then null;
  7    end;
  8
  9    dbms_scheduler.create_program(
 10      program_name             => 'win_pgm_test',
 11      program_type             => 'executable',
 12      number_of_arguments  => 2,
 13      program_action           => 'c:\windows\system32\cmd.exe');
 14
 15   dbms_scheduler.define_program_argument (
 16      program_name      => 'win_pgm_test',
 17      argument_name     => 'arg1',
 18      argument_position => 1,
 19      argument_type     => 'varchar2',
 20      default_value     => '/c');
 21
 22   dbms_scheduler.define_program_argument (
 23      program_name      => 'win_pgm_test',
 24      argument_name     => 'arg2',
 25      argument_position => 2,
 26      argument_type     => 'varchar2',
 27      default_value     => 'c:\temp\demo.cmd');
 28
 29    
 30  END;
 31  /

PL/SQL procedure successfully completed.


SQL> BEGIN
  2    begin
  3      dbms_scheduler.drop_job(
  4      job_name             => 'win_test');
  5    exception
  6      when others then null;
  7    end;
  8
  9    dbms_scheduler.create_job(
 10      job_name             => 'win_test',
 11      program_name             => 'win_pgm_test',
 12      enabled              => false);
 13
 14    dbms_scheduler.enable('win_test');
 15  END;
 16  /

PL/SQL procedure successfully completed.

SQL> select status,
  2         run_duration
  3  from   dba_scheduler_job_run_details
  4  where  job_name = 'win_test';

STATUS                         RUN_DURATION
------------------------------ ---------------------------------------------------------------------------
SUCCEEDED                      +000 00:00:00

SQL> BEGIN
  2    begin
  3      dbms_scheduler.drop_program(
  4      program_name             => 'win_pgm_test', force=>true);
  5    exception
  6      when others then null;
  7    end;
  8
  9    dbms_scheduler.create_program(
 10      program_name             => 'win_pgm_test',
 11      program_type             => 'executable',
 12      number_of_arguments  => 2,
 13      program_action           => 'c:\windows\system32\cmd.exe');
 14
 15   dbms_scheduler.define_program_argument (
 16      program_name      => 'win_pgm_test',
 17      argument_name     => 'arg1',
 18      argument_position => 1,
 19      argument_type     => 'varchar2',
 20      default_value     => '/c');
 21
 22   dbms_scheduler.define_program_argument (
 23      program_name      => 'win_pgm_test',
 24      argument_name     => 'arg2',
 25      argument_position => 2,
 26      argument_type     => 'varchar2',
 27      default_value     => 'c:\temp\demo.cmd');
 28
 29    dbms_scheduler.set_attribute('win_pgm_test', 'detached', true);   <<<======
 30  END;
 31  /
 

SQL> BEGIN
  2    begin
  3      dbms_scheduler.drop_job(
  4      job_name             => 'win_test');
  5    exception
  6      when others then null;
  7    end;
  8
  9    dbms_scheduler.create_job(
 10      job_name             => 'win_test',
 11      program_name             => 'win_pgm_test',
 12      enabled              => false);
 13
 14    dbms_scheduler.enable('win_test');
 15  END;
 16  /

PL/SQL procedure successfully completed.

SQL> select status,
  2         run_duration
  3  from   dba_scheduler_job_run_details
  4  where  job_name = 'win_test';

STATUS                         RUN_DURATION
------------------------------ ---------------------------------------------------------------------------
SUCCEEDED                      +000 00:00:00

SQL> select state from dba_scheduler_jobs
  2  where job_name = 'WIN_TEST';

STATE
---------------
RUNNING

SQL> exec dbms_scheduler.end_detached_job_run('win_test', 0, null);

PL/SQL procedure successfully completed.

SQL> select state from dba_scheduler_jobs
  2  where job_name = 'WIN_TEST';

no rows selected