BULK_EXCEPTIONS

[klornpallier]

I am using the standard Oracle BULK_EXCEPTIONS to trap any invalid rows. Unfortunately I only can get the index number of the row. How do I obtain more info from the row? My code is:

CREATE OR REPLACE PROCEDURE PSADMIN.GLI_EXTRACT_KP
AS

bulk_errors EXCEPTION;
PRAGMA EXCEPTION_INIT (bulk_errors, -24381);

CURSOR cost_out(l_taxyear char, l_periodno number, l_grp char) IS

SELECT *
FROM costing_output
--WHERE EMP_NO = '109432'
WHERE tax_year = l_taxyear
AND period_no = l_periodno
AND pay_group = l_grp
AND ABS(cash) > 0;

TYPE cost_out_tab IS TABLE OF cost_out%ROWTYPE
INDEX BY BINARY_INTEGER;

v_data cost_out_tab;

BEGIN

-- get parameters from PSePI table
SELECT taxyear, paygroup , periodno
INTO l_taxyear, l_grp, l_periodno
FROM PSJN_INTERFACE_PROCEDURES
WHERE procname = 'GLI_EXTRACT';

l_payp := l_periodno;

BEGIN

--populate TABLE WITH records FROM costing output
OPEN cost_out(l_taxyear, l_periodno, l_grp);
LOOP
FETCH cost_out BULK COLLECT INTO v_data;
EXIT WHEN cost_out%NOTFOUND;

END LOOP;
CLOSE cost_out;


FORALL recs IN v_data.FIRST .. v_data.LAST
SAVE EXCEPTIONS
INSERT INTO SHA_cost_out_extract
VALUES v_data(recs);


EXCEPTION

WHEN bulk_errors
THEN
FOR j IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE ( 'Error from element #' ||
TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ': ' ||
SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code));
END LOOP;

END;

END;

 

[LKBrwnDBA]

SQL%BULK_EXCEPTIONS(j).error_index is the index to your v_data row.

Or the same as:

V_data(SQL%BULK_EXCEPTIONS(j).error_index) has error SQLERRM(SQL%BULK_EXCEPTIONS(j).error_code)




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[klornpallier]

Thank you for your reply! So how would I get all the values for the selected columns in the column index to output to dbms_output for example?

 

[klornpallier]

Sorry I've worked this out now. I simply need to put the field name at the end to output the values.

v_data(SQL%BULK_EXCEPTIONS(j).ERROR_INDEX).pay_group