DBMS_OUTPUT not fetching any rows in output using cursor

Question

DBMS_OUTPUT.PUT_LINE is not returning data while using cursor as in below code

I used boolean to compare amount from the table

SET SERVEROUTPUT ON; 
DECLARE
    --declaration of variable
     x_id        test.saa.id%TYPE;
     x_acctname   test.saa.acctname%TYPE;
     x_curbal      test.saa.balamt%TYPE;
     x_sid       test.saa.sid%TYPE;

--setting of the boolean value default to null
     b_lowamount   BOOLEAN := false;

    --declaration of cursor
    CURSOR custbal IS
    SELECT id,acctname,bal_amt,sid
    FROM
        test.saa WHERE ROWNUM <= 1000;
   BEGIN 
                  --checking cursor is open or not
                     IF NOT ( custbal%isopen ) THEN
                           OPEN custbal;
                      END IF; 
    LOOP
    FETCH custbal INTO
                  x_id,
                  x_acctname,
                  x_curbal,
                 x_sid;
EXIT WHEN custbal%notfound;
CONTINUE WHEN custbal%found;
--begin another 
BEGIN
    b_lowamount   := ( x_curbal < 10 );
    IF b_lowamount THEN
        dbms_output.put_line('The customer having '|| x_acctname|| ' with sol_id '|| x_sid|| 'with balance RS. '|| x_curbal);

    ELSE
        dbms_output.put_line('The customer having '|| x_acctname|| ' with sol_id '|| x_sid|| 'with balance RS. '|| x_curbal);
        END IF;
      END;
 END loop;
 end;

didn't returned anything although the procedure completed successfully

Boneist · Accepted Answer · 2019-04-25 07:43:02Z

1

The reason why your output is not being shown is because of the CONTINUE - that means go to the start of the next loop.

Instead, your procedure could be written as:

DECLARE
  --declaration of variable
  x_id       test.saa.id%TYPE;
  x_acctname test.saa.acctname%TYPE;
  x_curbal   test.saa.balamt%TYPE;
  x_sid      test.saa.sid%TYPE;

  --setting of the boolean value default to null
  b_lowamount BOOLEAN := FALSE;

  --declaration of cursor
  CURSOR custbal IS
    SELECT id,
           acctname,
           bal_amt,
           sid
    FROM   test.saa
    WHERE  rownum <= 1000;
BEGIN
  OPEN custbal;

  LOOP
    FETCH custbal
      INTO x_id,
           x_acctname,
           x_curbal,
           x_sid;
    EXIT WHEN custbal%NOTFOUND;

    b_lowamount := (x_curbal < 10);
    IF b_lowamount
    THEN
      dbms_output.put_line('The customer having ' || x_acctname || ' with sol_id ' || x_sid || 'with balance RS. ' || x_curbal);

    ELSE
      dbms_output.put_line('The customer having ' || x_acctname || ' with sol_id ' || x_sid || 'with balance RS. ' || x_curbal);
    END IF;
  END LOOP;

  CLOSE custbal;
END;
/

N.B. I have removed the extra BEGIN and END that you had inside the loop; there is no need for starting a new block inside the current one!

Also, I have removed your check to see whether the cursor is open or not (it will always be closed at the start of the anonymous block, since the cursor is declared entirely within the scope of the anonymous block, and Oracle closes variables once the scope has ended. I have, however, added an explicit CLOSE statement, to close the cursor. This isn't strictly needed (since the cursor will automatically be closed once the block has finished), but it's good practice to include it if you're manually opening the cursor.

However, your entire procedure could be simplified to just:

BEGIN
  FOR rec IN (SELECT id,
                     acctname,
                     bal_amt,
                     sid
              FROM   test.saa
              WHERE  rownum <= 1000)
  LOOP
    IF rec.bal_amt < 10
    THEN
      dbms_output.put_line('The customer having ' || rec.acctname || ' with sol_id ' || rec.sid || 'with balance RS. ' || rec.curbal);
    ELSE
      dbms_output.put_line('The customer having ' || rec.acctname || ' with sol_id ' || rec.sid || 'with balance RS. ' || rec.curbal);
    END IF;
  END LOOP;
END;
/

(I have left the IF statement as-is, even though both branches are outputting the same string - I assume this was a mistake, and you meant different text to be output, depending on the balance being less than 10 or not? If it doesn't matter, you can just get rid of the IF statement, and instead just output the result.)

The nice thing about the cursor for loop is that you don't need to declare variables to return the values into (the record is implicitly created as part of the FOR <record> in (<cursor>) statement), and you don't need to handle opening and closing of the cursor. It also makes your code much simpler and therefore - IMO - easier to understand and maintain.

edited Apr 25, 2019 at 7:43

answered Apr 25, 2019 at 7:37

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Suman Over a year ago

Thank you After removing the EXIT WHEN condition it worked but it is returning the same record of the table in all of the lines of dbms_output .Alternative way is working Properly .

Boneist Over a year ago

Why on earth would you remove the exit when clause? That would mean you’d enter an infinite loop!

Suman Over a year ago

After doing So i entered into the indefinite loop but all of the records of the dbms_output is returning same

Boneist Over a year ago

That’s unsurprising; if you’re not fetching rows but you’re still looping because you don’t have a n exit condition, the values in the variables won’t change after the last row is fetched. In short, put the exit when clause back in!!!

Suman Over a year ago

Great Man !

Collectives™ on Stack Overflow

DBMS_OUTPUT not fetching any rows in output using cursor

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related