Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    17

    Unanswered: How To A No Data Found In This Scenario

    HI,

    Following is the data in the table
    C1 C2
    1 ABC
    2 CDE
    3 A11
    3 B11

    CREATE OR REPLACE PROCEDURE P1(MY_C1 T1.C1%TYPE) AS
    CURSOR C1 IS SELECT * FROM T1 WHERE C1 = MY_C1;
    BEGIN
    FOR Z IN C1 LOOP
    DBMS_OUTPUT.PUT_LINE(Z.C1||':'||Z.C2);
    END LOOP;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('NO ROWS FETCHED FOR THE QUERY');
    END P1;

    I have no probs with this procedure when the parameter passed exists in the database. But I do not know as to how would I gracefuly display a message 'NO ROWS FETCHED FOR THE QUERY' when the passed parameter doesnt exist in the database.

    Because it seems that NO_DATA_FOUND does not trap such kind of error and I do not want to use the CURSOR-OPEN-FETCH method.

    rgds
    Cyberbob

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How To A No Data Found In This Scenario

    You would need to set a variable to indicate whether you entered the loop or not, e.g.
    PHP Code:
    CREATE OR REPLACE PROCEDURE P1(MY_C1 T1.C1%TYPE) AS 
          
    CURSOR C1 IS SELECT FROM T1 WHERE C1 MY_C1;
          
    v_found BOOLEAN := FALSE:
    BEGIN
       
    FOR Z IN C1 LOOP  
             DBMS_OUTPUT
    .PUT_LINE(Z.C1||':'||Z.C2);
             
    v_found := TRUE;
       
    END LOOP;
       IF 
    NOT v_found THEN
         DBMS_OUTPUT
    .PUT_LINE('NO ROWS FETCHED FOR THE QUERY');  
       
    END IF;
    END P1

  3. #3
    Join Date
    Mar 2004
    Posts
    17

    Re: How To A No Data Found In This Scenario

    Many thanks.
    Actually I am after any known facility in PL/SQL like 'NO_DATA_FOUND' OR C1%FOUND etc which would solve my problem.

    rgds
    Cyberbob

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How To A No Data Found In This Scenario

    There is none. NO_DATA_FOUND only applies to SELECT INTO, and c1%FOUND is only available within the loop. Once you exit the loop, the cursor is closed and there is no information about how many rows were fetched, if any.

Posting Permissions

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