Results 1 to 3 of 3

Thread: Report Error

  1. #1
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Report Error

    The following cursor was written to find sum of issues for a given date. It is working if it returns more than one record. The value should be zero if no record was found. But it is not working for zero records. What is wrong with the following code?

    OPEN C2;
    SRW.MESSAGE (1,'CURSOR 2 AFTER OPEN');
    FETCH C2 INTO V_ISS;
    SRW.MESSAGE (2,'CURSOR 2 AFTER FETCH');
    IF C2%NOTFOUND THEN
    V_ISS := 0;
    SRW.MESSAGE (3,'CURSOR 2 INSIDE IF');
    END IF;
    CLOSE C2;


    I tried using C2%ROWCOUNT = 0 also. Could anyone suggest where is the mistake in the above code?


  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Report Error

    If no data found(other exception) occur control goes to exception block so ur code
    (IF C2%NOTFOUND THEN
    V_ISS := 0;
    SRW.MESSAGE (3,'CURSOR 2 INSIDE IF');
    END IF; )
    is not executed u need to include the above code in
    Exception block i.e...
    OPEN C2;
    SRW.MESSAGE (1,'CURSOR 2 AFTER OPEN');
    FETCH C2 INTO V_ISS;
    SRW.MESSAGE (2,'CURSOR 2 AFTER FETCH');
    .
    .
    .
    Exception
    When others then --(or when no_data_found then)
    V_ISS := 0;
    .....
    SRW.MESSAGE (3,'CURSOR 2 INSIDE IF');
    END IF;
    .......
    CLOSE C2;


  3. #3
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Report Error

    Thank you for your reply.

    I tried to rectify the above problem using exception no_data_found. But it does not work.

    I think in cursors, if no data found then it will not raise exception. It will return NULL value. I rectified above problem in the following way
    IF V_ISS IS NULL THEN
    V_ISS := 0;
    END IF;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact