I have a cursor as part of a package, which compares the counts of 2 tables. If the counts match, rest of the package executes. But if it fails, it should update the counts in a log table. The cursor code is
CURSOR C_CNT IS
SELECT
lnd.sml_batchrun_id batch_id,
lnd.lnd_count,
dwh.dwh_count
FROM
(
SELECT
sml_batchrun_id,
COUNT(*) lnd_count
FROM
iva_landing.lnd_sml_t
GROUP BY
sml_batchrun_id
) lnd
LEFT JOIN (
SELECT
batchrun_id,
sent_records_count dwh_count,
dwh_sending_table
FROM
dwh.dwh_idh_to_iva_metadata_t
) dwh ON dwh.batchrun_id = lnd.sml_batchrun_id
WHERE dwh.dwh_sending_table = 'DWH_SML_T'
ORDER BY
1 DESC;
The comparison code is:
FOR L_COUNT IN C_CNT LOOP --0001,0002
IF L_COUNT.lnd_count = L_COUNT.dwh_count THEN
UPDATE DWH.DWH_IDH_TO_IVA_METADATA_T idh
SET idh.IVA_RECEIVING_TABLE = 'LND_SML_T',
idh.RECEIVED_DATE = SYSDATE,
idh.RECEIVED_RECORDS_COUNT = L_COUNT.lnd_count,
idh.status = 'Verified'
WHERE L_COUNT.batch_id = idh.batchrun_id
AND idh.dwh_sending_table = 'DWH_SML_T';
COMMIT;
ELSE
RAISE EXCPT_SML_MISSDATA; -- Throw error and exit process
END IF;
END LOOP;
Now, in the Exception Handling part, I want to display the counts in the error column of the log table
logger.log_error('IVA-MSG 200-010 - COUNT MISMATCH! - Aborting', p_log_id=>l_job_log.log_id);
l_job_log.end_date := systimestamp;
l_job_log.error_mesg := cur_cnt.dwh_count||' '|| cur_cnt.lnd_count;
l_job_log.status := iva_log.iva_job_log_pck.c_str_statusfailed;
iva_log.iva_job_log_pck.change_joblog_prc(pi_rec_joblog => l_job_log);
RAISE;
Here, cur_cnt is a variable defined as cur_cnt c_cnt%rowtype;
and l_job_log as l_job_log iva_log.iva_job_log_t%rowtype;
where iva_job_log_t is the log table name. But after triggering the package the count is not visible in the error column. Also, if I put something in single quotes for the iva_job_log_t then it gets displayed in the log table.
Please suggest how to display the counts from the cursor.
Thanks
If you are still doing
FOR L_COUNT IN C_CNT LOOP
then your cur_cnt variable is never going to be populated; it will be the empty record it was declared as, so referring to any of its fields will always give you null.
You can just refer to the l_count values in the error log:
l_job_log.error_mesg := l_count.dwh_count ||' '|| l_count.lnd_count;
... as you do elsewhere inside the loop.
If the exception handler is currently later in the process, so l_count is out of scope (as the re-raise possibly suggests) then you could move it so it's within the else instead of being separated:
FOR L_COUNT IN C_CNT LOOP --0001,0002
IF L_COUNT.lnd_count = L_COUNT.dwh_count THEN
UPDATE DWH.DWH_IDH_TO_IVA_METADATA_T idh
SET idh.IVA_RECEIVING_TABLE = 'LND_SML_T',
idh.RECEIVED_DATE = SYSDATE,
idh.RECEIVED_RECORDS_COUNT = L_COUNT.lnd_count,
idh.status = 'Verified'
WHERE L_COUNT.batch_id = idh.batchrun_id
AND idh.dwh_sending_table = 'DWH_SML_T';
COMMIT;
ELSE
logger.log_error('IVA-MSG 200-010 - COUNT MISMATCH! - Aborting', p_log_id=>l_job_log.log_id);
l_job_log.end_date := systimestamp;
l_job_log.error_mesg := l_count.dwh_count||' '|| l_count.lnd_count;
l_job_log.status := iva_log.iva_job_log_pck.c_str_statusfailed;
iva_log.iva_job_log_pck.change_joblog_prc(pi_rec_joblog => l_job_log);
RAISE EXCPT_SML_MISSDATA; -- Throw error and exit process
END IF;
END LOOP;
Otherwise you would have to change the cursor loop handling to fetch into the variable, refer to that within the loop instead, and rely on that having the right values later:
OPEN C_CNT
LOOP
FETCH C_CNT INTO CUR_CNT;
EXIT WHEN C_CNT%NOTFOUND;
IF CUR_CNT.lnd_count = CUR_CNT.dwh_count THEN
UPDATE DWH.DWH_IDH_TO_IVA_METADATA_T idh
SET idh.IVA_RECEIVING_TABLE = 'LND_SML_T',
idh.RECEIVED_DATE = SYSDATE,
idh.RECEIVED_RECORDS_COUNT = CUR_CNT.lnd_count,
idh.status = 'Verified'
WHERE CUR_CNT.batch_id = idh.batchrun_id
AND idh.dwh_sending_table = 'DWH_SML_T';
COMMIT;
ELSE
RAISE EXCPT_SML_MISSDATA; -- Throw error and exit process
END IF;
END LOOP;
Your later exception handler should then work as it is.