CODEWITHSUNDEEP
oracle-databaseplsqlplsql-package
Neha pandey
Neha pandey

Reputation: 3

How to print cursor records?

I have tried to print cursor records, but I can't. Below code, I have tried.

DECLARE
   CURSOR CUR_EMP IS
      SELECT *
        FROM EMP
       WHERE Deptno = &DNO;

   V_REC  CUR_EMP%ROWTYPE;
BEGIN
   OPEN CUR_EMP;

   LOOP
      FETCH CUR_EMP INTO V_REC;

      EXIT WHEN CUR_EMP%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE (
            'Record No:'
         || CUR_EMP%ROWCOUNT
         || ' '
         || V_REC.ENAME
         || ' 
                         '
         || V_REC.SAL);
   END LOOP;

   CLOSE CUR_EMP;
END;

Upvotes: 0

Views: 800

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

If you run it as you did:

SQL> declare
  2    cursor cur_emp is select * from emp
  3      where deptno = &dno;
  4    v_rec  cur_emp%rowtype;
  5  begin
  6    open cur_emp;
  7    loop
  8      fetch cur_emp into v_rec;
  9      exit when cur_emp%notfound;
 10
 11      dbms_output.put_line('Record no: ' || cur_emp%rowcount ||' '|| v_rec.ename);
 12    end loop;
 13    close cur_emp;
 14  end;
 15  /
Enter value for dno: 10
old   3:     where deptno = &dno;
new   3:     where deptno = 10;

PL/SQL procedure successfully completed.            --> there's no output

But, if you enable output ...

SQL> set serveroutput on
SQL>

... and run that code again:

SQL> /
Enter value for dno: 10
old   3:     where deptno = &dno;
new   3:     where deptno = 10;
Record no: 1 CLARK                             --> oh yes, here they are!
Record no: 2 KING
Record no: 3 MILLER

PL/SQL procedure successfully completed.

SQL>

So, set serveroutput on in SQL*Plus (or SQL Developer, maybe even some more tools which support it), or enable it in your GUI. Because, that code should run properly (of course, if WHERE condition is met).

Also, consider using cursor FOR loop which is easier to maintain as Oracle does most of the dirty job (declaring cursor variable, opening cursor, taking care about exiting the loop, closing the cursor) for you:

SQL> begin
  2    for cur_emp in (select rownum rn, e.* from emp e where deptno = &dno) loop
  3      dbms_output.put_line('Record no: ' || cur_emp.rn ||' '|| cur_emp.ename);
  4    end loop;
  5  end;
  6  /
Enter value for dno: 10
old   2:   for cur_emp in (select rownum rn, e.* from emp e where deptno = &dno) loop
new   2:   for cur_emp in (select rownum rn, e.* from emp e where deptno = 10) loop
Record no: 1 CLARK
Record no: 2 KING
Record no: 3 MILLER

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions