Using Oracle SQL

1. Create a procedure that accepts product ID as a parameter and returns the name of the product from ProductTable table. Add exception handling to catch if product ID is not in the table.

BELOW IS THE TABLE

CREATE TABLE ProductTable(

ProductID INTEGER NOT NULL primary key,

ProductName VARCHAR(50) NOT NULL,

ListPrice NUMBER(10,2),

Category INTEGER NOT NULL

);

/

INSERT INTO ProductTable VALUES(299,'Chest',99.99,10);

INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11);

INSERT INTO ProductTable VALUES(301,'Megaland Play Tent',59.99,11);

INSERT INTO ProductTable VALUES(302,'Wind-Up Water Swimmers',2.00,11);

INSERT INTO ProductTable VALUES(303,'Garmin Pocket or Vehicle GPS Navigator',609.99,12);

Below is what i have but its not working!!!

CREATE OR REPLACE PROCEDURE p_product_information (ID IN INTEGER, Name OUT VARCHAR) IS BEGIN SELECT ProductName INTO Name FROM ProductTable WHERE ProductID=ID; dbms_output.put_line (ID); EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line ('Product Id not found'); END; / set SERVEROUTPUT ON; execute p_product_information(302); execute p_product_information(299); execute p_product_information(0);

SET SERVEROUTPUT oan (302): execute p_product_information( 302) execute p_product_information( 299): execute p_product information(0): Saript Output Query Result x 4 anonymous block completed Product name for product Id 302 is Wind-Up Water Swimmers | Task completed in 0.008 seconds anonymous block completed Product name for product Id 299 is Chest anonymous block completed No Data found for SELECT on product Id 0