No_data_found Exception Help

[caro1216]

CAn anybody please help me? I have to do this exercise with NO_DATA_FOUND but is not working.

ThiS one works great (EXIT WHEN NOTFOUND!!) but my exercise has to user NOT_DATA_FOUND! like the one at the END but is NOT WORKING, please little help?



SET SERVEROUTPUT ON
DECLARE
v_emp_id garciac_table.id%TYPE;
v_firstname garciac_table.firstname%TYPE;
v_lastname garciac_table.lastname%TYPE;
CURSOR c_employee IS
SELECT id,firstname,lastname
FROM garciac_table
WHERE State = 'Delaware';
BEGIN
OPEN c_employee;
LOOP
FETCH c_employee INTO v_emp_id, v_firstname,v_lastname ;
EXIT WHEN c_employee%NOTFOUND;
INSERT INTO temp_table (id_temp, firstname_temp,lastname_temp)
VALUES (v_emp_id, v_firstname,v_lastname);
END LOOP;
CLOSE c_employee;
END;


****************************************************************

THIS ONE IS NOT WORKING

****************************************************************



SET SERVEROUTPUT ON
DECLARE
v_emp_id garciac_table.id%TYPE;
v_firstname garciac_table.firstname%TYPE;
v_lastname garciac_table.lastname%TYPE;

CURSOR c_infemployee IS
SELECT id,firstname,lastname
FROM garciac_table
WHERE State = 'Delaware';
BEGIN

OPEN c_infemployee;

LOOP

FETCH c_infemployee INTO V_EMP_ID, v_firstname,v_lastname ;

INSERT INTO temp_table (id_temp, firstname_temp,lastname_temp)
VALUES (v_emp_id, v_firstname,v_lastname);

END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Nobody lives in that State');

CLOSE c_infemployee;

END;




ANY HELP WILL BE APRECIATE!!!

THANKS

[Bonker]

begin
INSERT INTO temp_table (id_temp, firstname_temp,lastname_temp)
SELECT id,firstname,lastname
FROM garciac_table
WHERE State = 'Delaware';

if sql%rowcount=0 then
raise no_data_found;
else
dbms_output.put_line('Inserted '||sql%rowcount||' records');
end if;


EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Nobody lives in that State');
END;
/

[Furqan]

Dear Here I send u the solution of Ur Problem.

SET SERVEROUTPUT ON
DECLARE
CURSOR c_infemployee IS
SELECT id,firstname,lastname
FROM garciac_table
WHERE State = 'Delaware';

BEGIN
For I In c_infemployee Loop
INSERT INTO temp_table (id_temp, firstname_temp,lastname_temp)
VALUES (I.id,I.firstname,I.lastname);
END LOOP;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Nobody lives in that State');
END;


This Solution solve ur Problem.