DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: No_data_found Exception Help

  1. #1
    Join Date
    Mar 2005
    Posts
    1

    No_data_found Exception Help

    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

  2. #2
    Join Date
    May 2005
    Posts
    31

    does this help

    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;
    /
    Last edited by Bonker; 05-05-2005 at 04:59 AM.
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

  3. #3
    Join Date
    May 2005
    Posts
    1

    Cool New Solution Of ur Problem

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width