Week10 - Exception Handling / Assess

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Create a procedure named "insert_route" which has key_id and route_name as the input parameter and status as output parameter. This procedure insert values into the table route. The values are passed as the input parameter to the procedure. Note: If the key_id value does not exists in the table, then set the status parameter as Inserted successfully. If the key_id value already exists in the table then you should handle the exception or warning by displaying the statusas Sorry duplicate key is not allowed. Hints: Procedure name: insert_route Input parameters: key_id(int) , route_name (varchar2) Output parameters: status varchar2

    create or replace procedure insert_route(key_id in number, route_name in varchar2, status out varchar2)
    is
    begin
        insert into route
        values(key_id, route_name);
        status := 'Inserted successfully';
    exception
        when dup_val_on_index then
            status := 'Sorry duplicate key is not allowed';
    end;
    /

2. Create a procedure named "select_metro_trains" which has routeId as the input parameter and status as the output parameter. This procedure displays the id from the table metro_train where routeId passed as the input parameter. Note: If there is a chance of returning the multiple rows then you should handle the exception or warning by displaying the status SORRY PROCEDURE WONT RETURN MULTIPLE ROWS. Hints: Procedure name: select_metro_trains Input parameters: routeId (id)

    create or replace procedure select_metro_trains (routeId in varchar, status out varchar)
    is
    mt int;
    begin
    select id into mt from metro_train where route_id  = routeId;
    status:=mt;
    EXCEPTION 
    when too_many_rows then 
    status:= 'SORRY PROCEDURE WONT RETURN MULTIPLE ROWS';
    END;
    /