We developed a procedure to be used in database-A and in database-A. In this procedure we send an input parameter on the basis of which a select statement searches the data from a table located in remote database-B. The selected columns are set in Output parameters of the stored procedure and that's all. There is some logging as well in the procedure where we log progress of parameter to a table from which no one is ready data. Every row is kind of unique in log table.
The remote database-B is synched using Golden gate from another database-C (oltp). So there are no chances of locking we believe.Below is the stored procedure;
create or replace PROCEDURE prc_live_bvs_check_g2p_data_op (
/*
Procedure Purpose : To handle BVS issue due to patch. Returns MSISDN and CNIC of a G2P transaction.
Created by : Khalid Mehmood
Created date : 2019-Feb-12
Modified at : 2019-Apr-10 20:00 by Khalid
Created for : Saad Saif.
*/
in_trx_order_id IN VARCHAR2,
out_trx_order_id OUT VARCHAR2,
out_trx_receiver_cnic OUT VARCHAR2,
out_trx_receiver_msisdn OUT VARCHAR2,
out_trx_amount OUT VARCHAR2,
out_trx_payment_time OUT VARCHAR2,
out_trx_org_short_code OUT VARCHAR2,
out_trx_collecting_trans_id OUT VARCHAR2,
out_trx_collect_time OUT VARCHAR2,
out_trx_return_code OUT VARCHAR2,
out_prc_execution OUT VARCHAR2
) IS
v_temp VARCHAR(32);
v_success_flag BOOLEAN := true;
v_success VARCHAR2(20);
exp_tid_invalid EXCEPTION;
exp_tid_length EXCEPTION;
exp_tid_null EXCEPTION;
v_tempstring VARCHAR2(2000);
v_sequence_id NUMBER;
v_in_trx_order_id varchar2 (50);
BEGIN
v_in_trx_order_id := in_trx_order_id;
SELECT
mobilink.seq_mfs_cps_prc_logging.nextval
INTO
v_sequence_id
FROM
dual;
prc_mfs_prc_log_output_v2('{Start}','MFS-BVS-G2P-PROC',v_in_trx_order_id,v_sequence_id,'Start');
BEGIN
IF
v_in_trx_order_id IS NULL OR v_in_trx_order_id = ''
THEN
RAISE exp_tid_null;
ELSIF length(v_in_trx_order_id) <> 12 THEN
RAISE exp_tid_length;
ELSIF v_in_trx_order_id IS NOT NULL THEN
-- This to check if the input is numeric or not.
SELECT
to_number(v_in_trx_order_id)
INTO
v_temp
FROM
dual;
END IF;
SELECT
order_id,
receiver_cnic,
nvl(receiver_msisdn,'') receiver_msisdn,
amount / 100 amount,
payment_time,
g2porg_shortcode,
collecting_trans_id,
collect_time
INTO
out_trx_order_id,out_trx_receiver_cnic,out_trx_receiver_msisdn,out_trx_amount,out_trx_payment_time,out_trx_org_short_code,out_trx_collecting_trans_id
,out_trx_collect_time
FROM
lbi_ogg_mk.t_o_g2ppayment_record@cps_live_rpt_db
WHERE
order_id = v_in_trx_order_id;
out_prc_execution := 'RESULT:0000: Completed Successfully.';
out_trx_return_code := '0000';
-- prc_mfs_prc_log_output_v2(out_prc_execution
-- || ' :: '
-- || out_trx_order_id
-- || ' : '
-- || out_trx_receiver_cnic
-- || ' : '
-- || out_trx_receiver_msisdn
-- || ' : '
-- || out_trx_amount
-- || ' : '
-- || out_trx_payment_time
-- || ' : '
-- || out_trx_org_short_code
-- || ' : '
-- || out_trx_collecting_trans_id
-- || ' : '
-- || out_trx_collect_time,'MFS-BVS-G2P-PROC',v_in_trx_order_id,v_sequence_id);
EXCEPTION
WHEN value_error THEN
out_prc_execution := 'RESULT:1001: Invalid input parameter. should be numeric only.';
v_success_flag := false;
out_trx_return_code := '1001';
WHEN no_data_found THEN
out_prc_execution := 'RESULT:1002: No record found against this TID.';
v_success_flag := false;
out_trx_return_code := '1002';
WHEN exp_tid_invalid THEN
out_prc_execution := 'RESULT:1003: TID is invalid.';
v_success_flag := false;
out_trx_return_code := '1003';
WHEN exp_tid_length THEN
out_prc_execution := 'RESULT:1004: TID length is invalid. should be 12.';
v_success_flag := false;
out_trx_return_code := '1004';
WHEN exp_tid_null THEN
out_prc_execution := 'RESULT:1005: TID is null.';
v_success_flag := false;
out_trx_return_code := '1005';
WHEN OTHERS THEN
out_prc_execution := 'RESULT:2000: Unknown error occurred';
v_success_flag := false;
out_trx_return_code := '2000';
END;
IF
v_success_flag = false
THEN
v_success := 'Failed';
out_trx_receiver_cnic := NULL;
out_trx_receiver_msisdn := NULL;
out_trx_amount := NULL;
out_trx_payment_time := NULL;
out_trx_org_short_code := NULL;
out_trx_collecting_trans_id := NULL;
out_trx_collect_time := NULL;
ELSE
v_success := 'Success';
END IF;
v_tempstring := '{End..} TID: '
|| v_in_trx_order_id
|| ' : Out : Exception: '
|| out_prc_execution
|| ' : CNIC: '
|| out_trx_receiver_cnic
|| ' : MSISDN: '
|| out_trx_receiver_msisdn
|| ' : ShortCode : '
|| out_trx_org_short_code
|| ' : Collect-TID : '
|| out_trx_collecting_trans_id
|| ' : Collect-Time : '
|| out_trx_collect_time
|| ' : Result Code : '
|| out_trx_return_code
|| ' : Success Flag: '
|| CASE v_success_flag
WHEN true THEN 'true'
WHEN false THEN 'false'
END;
prc_mfs_prc_log_output_v2(v_tempstring,'MFS-BVS-G2P-PROC',v_in_trx_order_id,v_sequence_id,v_success);
END;
I would try this. Edit the procedure to enable a trace, ie
BEGIN
dbms_monitor.session_trace_enable(waits=>true);
v_in_trx_order_id := in_trx_order_id;
..
..
..
prc_mfs_prc_log_output_v2(v_tempstring,'MFS-BVS-G2P-PROC',v_in_trx_order_id,v_sequence_id,v_success);
dbms_monitor.session_trace_disable;
END;
and then wait for the next occurrence that takes longer than expected. Run tkprof on that trace file and see where the time was lost. This will let you hone in on precisely where the issue is.