How to print detail of all employee '%rowtype' in oracle dbms using pl/sql cursor?-CodePudding

here is my code,

declare
  cursor c_emp is
    select ename, sal, mgr from emp;
  emp_detail emp%rowtype;
begin
    open c_emp;
        loop    
            fetch c_emp into emp_detail;
            exit when c_emp%NOTFOUND;
            dbms_output.put_line(emp_detail);
        end loop;
    close c_emp;
end;
/

getting this error,

ERROR at line 8: ORA-06550: line 8, column 13: PLS-00394: wrong number of values in the INTO list of a FETCH statement ORA-06550: line 8, column 13: PL/SQL: SQL Statement ignored ORA-06550: line 10, column 13: PLS-00306: wrong number or types of arguments in call to 'PUT_LINE' ORA-06550: line 10, column 13: PL/SQL: Statement ignored

For this error i got wasted much of time time but finally i got my answer by own, now question is why i am posting this, answer do not want other to wasted time like me in that so,

CodePudding user response：

dbms_output.put_line prints a string. emp_detail is not a string, it is a %ROWTYPE, you can't print that as such. What you can do is print one (or more) columns from the rowtype. Example:

koen>set serveroutput on size 999999
koen>
  1  declare
  2    cursor c_emp is
  3      select * from emp;
  4    emp_detail emp%rowtype;
  5  begin
  6      open c_emp;
  7          loop    
  8              fetch c_emp into emp_detail;
  9              exit when c_emp%NOTFOUND;
 10              dbms_output.put_line(emp_detail.ename);
 11          end loop;
 12      close c_emp;
 13* end;

/

KING
BLAKE
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER

CodePudding user response：

Now that you know what went wrong, to save some time & space, switch to a cursor FOR loop because - doing so - you won't have to declare cursor variable, open the cursor, take care about exiting the loop, close the cursor - Oracle does all that for you:

SQL> set serveroutput on
SQL> begin
  2    for cur_r in (select ename, sal, mgr from emp) loop
  3      dbms_output.put_line(cur_r.ename ||', '|| cur_r.sal ||', '|| cur_r.mgr);
  4    end loop;
  5  end;
  6  /
SMITH, 800, 7902
ALLEN, 1600, 7698
WARD, 1250, 7698
JONES, 2975, 7839
MARTIN, 1250, 7698
BLAKE, 2850, 7839
CLARK, 2450, 7839
SCOTT, 3000, 7566
KING, 5000,
TURNER, 1500, 7698
ADAMS, 1100, 7788
JAMES, 950, 7698
FORD, 3000, 7566
MILLER, 1300, 7782

PL/SQL procedure successfully completed.

SQL>