DB 11g
I would like to know how to get an anonymous block to continue processing a cursor loop once a NO_DATA_FOUND error occurs.
I have an anonymous block that is throwing a NO_DATA_FOUND message.
The anonymous block is inserting values from one table into many tables, and contains a cursor.
The original table has a column that contains varchar values, the NEW table contains a number column for this information and is a foriegn key value. I have created a SELECT INTO statement to grab the number ID. It is this SELECT INTO that returns a NO_DATA_FOUND error.
I have added an EXCEPTION line in to catch the error but it seems to bounce out of the code as soon as it hits a NO_DATA_FOUND error. I was hoping that it would continue to process the loop.
Thank You
Ben
This is my anonymous block;
DECLARE
NUM_HARVEST_PLANNER_ID NUMBER;
num_assessment_id NUMBER;
num_blk_id NUMBER;
BEGIN
FOR c1 IN
(SELECT compartments
, INTERPTEAM
, COMMENTS
, PHOTO_DATE
, interpreted_area
, forest_block
FROM X_CELL_REPORT)
LOOP
num_blk_id := 1;
IF c1.forest_block is not null THEN
SELECT DISTINCT blk_id
INTO num_blk_id
FROM x_cell_report A
, sfm_common.sct_forest_blocks B
WHERE B.NAME = C1.FOREST_BLOCK;
END IF;
INSERT
INTO DAD_HARVEST_PLANNER (COMPARTMENTS, COUPE_ID, TENURE_ID, CLIENT_ID, ASSESSMENT_TYPE_ID, ASSESSMENT_PURPOSE_ID, blk_id)
VALUES (c1.compartments, 1, 1, 1, 1, 1, NUM_BLK_ID);
SELECT dad_harvest_planner_seq.currval INTO num_harvest_planner_id FROM dual;
INSERT
INTO DAD_ASSESSMENTS (HARVEST_PLANNER_ID, WORKS_COMPLETED_BY, comments, photography_date, ASSESSMENT_METHOD_ID, ASSESSOR_ID)
VALUES (num_harvest_planner_id, c1.interpteam, c1.comments, c1.photo_date, 1, 1);
SELECT dad_assessments_seq.currval INTO num_assessment_id FROM dual;
INSERT
INTO DAD_ASSESSMENT_AREA (ASSESSMENT_ID, ASSESSMENT_CATEGORY_ID, assessment_area)
VALUES (NUM_ASSESSMENT_ID, 1, C1.INTERPRETED_AREA);
END LOOP;
EXCEPTION WHEN NO_DATA_FOUND THEN
NUM_BLK_ID := 1;
END;
/
COMMIT;