In PL/SQL, I need to add error handling or an exception block to the following i

Question

In PL/SQL, I need to add error handling or an exception block to the following insert_items procedure that writes errors to the nc_error table:

/* Create draft insert_items procedure. */
CREATE PROCEDURE insert_items
( pv_items ITEM_TAB ) IS

BEGIN
/* Read the list of items and call the insert_item procedure. */
FOR i IN 1..pv_items.COUNT LOOP
insert_item( pv_item_barcode => pv_items(i).item_barcode
, pv_item_type => pv_items(i).item_type
, pv_item_title => pv_items(i).item_title
, pv_item_subtitle => pv_items(i).item_subtitle
, pv_item_rating => pv_items(i).item_rating
, pv_item_rating_agency => pv_items(i).item_rating_agency
, pv_item_release_date => pv_items(i).item_release_date );
END LOOP;
END;
/

How do I do this?

Explanation / Answer

CREATE OR REPLACE PROCEDURE insert_items
( pv_items ITEM_TAB ) IS

BEGIN
/* Read the list of items and call the insert_item procedure. */

FOR i IN 1..pv_items.COUNT LOOP
insert_item( pv_item_barcode => pv_items(i).item_barcode
, pv_item_type => pv_items(i).item_type
, pv_item_title => pv_items(i).item_title
, pv_item_subtitle => pv_items(i).item_subtitle
, pv_item_rating => pv_items(i).item_rating
, pv_item_rating_agency => pv_items(i).item_rating_agency
, pv_item_release_date => pv_items(i).item_release_date );
END LOOP;

/*Now onwords exception will take place to handling errors.
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('insert_items: exception - ' || SQLCODE || ' : ' ||
SQLERRM);
RAISE;
END insert_items;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.