Hi,

I have a Oracle Chain, which calls multiple Jobs and internally Job calls SP to update a table Flag.

Oracle Chain
-------------
BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
CHAIN_NAME => 'CCR_CHAIN',
RULE_SET_NAME => NULL,
EVALUATION_INTERVAL => NULL,
COMMENTS => NULL);
END;
/

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CCR_CHAIN', 'stepA', 'JB_CCR_DELTA_CHANGES');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('CCR_CHAIN', 'stepB', 'JB_CCR_DELTA_ERROR_NOTIFICATION');
END;
/

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('CCR_CHAIN', 'TRUE', 'START stepA');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
'CCR_CHAIN', 'stepA COMPLETED', 'Start stepB');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE('CCR_CHAIN');
END;
/

Oracle Job 1:
-------------

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'JB_CCR_DELTA_CHANGES_DAILY',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'CCRBT.SP_CCR_PRODUCT_FETCH_PER_DAY;',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL=> 'FREQ=DAILY; BYSECOND=0;',
END_DATE => NULL,
ENABLED => TRUE,
AUTO_DROP => FALSE,
COMMENTS => 'Job defined for CCR daily job notification');
END;
/
-- Added the failure notification if job fails.
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'JB_CCR_DELTA_CHANGES_DAILY',
recipients => 'debabrata.b@prodapt.com,pabusalih.contractor@libertyglobal.com,pmurugaiyan@libertyglobal.com,VRAGUPATHI.contractor@libertyglobal.com,PJANAKIRAMAN.contractor@libertyglobal.com',
subject => 'CCR FEED JOB NOTIFICATION-.%JOB_NAME%-%EVENT_TYPE%-@%EVENT_TIMESTAMP%',
events => 'JOB_FAILED'
);
END;
/

Oracle Job 2:
------------
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'JB_CCR_DELTA_ERROR_NOTIFICATION_DAILY',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'CCRBT.SP_CCR_ERROR_LOG_UPDATE;',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL=> 'FREQ=DAILY; BYSECOND=0;',
END_DATE => NULL,
ENABLED => TRUE,
AUTO_DROP => FALSE,
COMMENTS => 'Job defined for CCR daily job notification');
END;
/

Now, once updated, my email notification block picks the records (Flags) updated by the above procedure and sends an email. I have also written a Function whose resultant will be used to show the count.

BEGIN
DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
JOB_NAME => 'JB_CCR_DELTA_ERROR_NOTIFICATION_DAILY',
RECIPIENTS => email_addresses,
SUBJECT => 'CCR FEED JOB NOTIFICATION-.%JOB_NAME%-%EVENT_TYPE%-@%EVENT_TIMESTAMP%',
BODY => 'Job is Successfull.The total error count during the current job is '|| CCRBT.FN_CCR_ERR_COUNT,
EVENTS => 'JOB_SUCCEEDED'
);
END;
/

However; i am facing problem as each time the count shows same in email even though the Flag table has different values. If I run the Function standalone i get correct counts. But from Job/Email notification, the count is always static.

Function:
-----------

CREATE OR REPLACE FUNCTION CCRBT.FN_CCR_ERR_COUNT_deb RETURN VARCHAR2
IS
ERROR_COUNT NUMBER;
BEGIN
ERROR_COUNT:=0;
SELECT COUNT(*) INTO ERROR_COUNT FROM CCRBT.TABLE_ERROR_LOG WHERE FLAG IS NULL AND SOURCE_OBJECT_NAME IN ('CCR_EDSSYNC_FETCH','CCR_EDSSYNC_ADDRESS_FETCH','CCR_EDSSYNC_PRODUCT_FETCH','SP_CCR_CUSTOMER_ADDRESS_FEED','SP_CCR_CUSTOMER_PRODUCT_FEED');


RETURN ERROR_COUNT;
END;
/




Could you please help me in debugging this scenario or what can be the issue.

Regards
Debabrata