OraFAQ Forum: SQL & PL/SQL » %rowtype error 06512

Home » SQL & PL/SQL » SQL & PL/SQL » %rowtype error 06512

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

%rowtype error 06512 [message #635744]

Wed, 08 April 2015 01:56

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

Hi Experts.

please tell me where iam wrong?

DECLARE
TEST SCOTT.EMP%ROWTYPE;
BEGIN
SELECT E.* INTO TEST FROM SCOTT.EMP E
WHERE E.DEPTNO=10;

dbms_output.put_line('Customer ID: ' ||TEST.EMPNO);
dbms_output.put_line('Customer NAME: ' ||TEST.ENAME);
dbms_output.put_line('Customer JOB: ' ||TEST.JOB);
dbms_output.put_line('Customer MGR: ' ||TEST.MGR);
dbms_output.put_line('Customer HIREDATE: ' ||TEST.HIREDATE);
dbms_output.put_line('Customer SAL: ' ||TEST.SAL);
dbms_output.put_line('Customer COMM: ' ||TEST.COMM);
dbms_output.put_line('Customer DEPTNO: ' ||TEST.DEPTNO);
END;

Report message to a moderator

Re: %rowtype error 06512 [message #635745 is a reply to message #635744]

Wed, 08 April 2015 02:05

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Because, exact fetch returns more than requested number of rows

You will get the error - ORA-01422: exact fetch returns more than requested number of rows

%ROWTYPE attribute can store an entire row of data selected from the table. But you are trying to store multiple rows.

For example, if you have a single row, it would work fine:

SQL> SET serveroutput ON
SQL>
SQL> DECLARE
  2  TEST SCOTT.EMP%ROWTYPE;
  3  BEGIN
  4  SELECT E.* INTO TEST FROM SCOTT.EMP E
  5  WHERE E.empno=7369;
  6
  7  dbms_output.put_line('Customer ID: ' ||TEST.EMPNO);
  8  dbms_output.put_line('Customer NAME: ' ||TEST.ENAME);
  9  dbms_output.put_line('Customer JOB: ' ||TEST.JOB);
 10  dbms_output.put_line('Customer MGR: ' ||TEST.MGR);
 11  dbms_output.put_line('Customer HIREDATE: ' ||TEST.HIREDATE);
 12  dbms_output.put_line('Customer SAL: ' ||TEST.SAL);
 13  dbms_output.put_line('Customer COMM: ' ||TEST.COMM);
 14  dbms_output.put_line('Customer DEPTNO: ' ||TEST.DEPTNO);
 15  END;
 16  /
Customer ID: 7369
Customer NAME: SMITH
Customer JOB: CLERK
Customer MGR: 7902
Customer HIREDATE: 17-DEC-80
Customer SAL: 800
Customer COMM:
Customer DEPTNO: 20

PL/SQL procedure successfully completed.

SQL>

Regards,
Lalit

Report message to a moderator

Re: %rowtype error 06512 [message #635746 is a reply to message #635745]

Wed, 08 April 2015 02:09

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

Thank You Sir

Report message to a moderator

Re: %rowtype error 06512 [message #635747 is a reply to message #635744]

Wed, 08 April 2015 02:12

Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Or if you want all rows from department 10, use a CURSOR FOR loop:

SQL> DECLARE
  2  -- TEST SCOTT.EMP%ROWTYPE;
  3  BEGIN
  4
  5    for test in (SELECT E.* FROM SCOTT.EMP E WHERE E.DEPTNO=10) loop
  6
  7      dbms_output.put_line('Customer ID: ' ||TEST.EMPNO);
  8      dbms_output.put_line('Customer NAME: ' ||TEST.ENAME);
  9      dbms_output.put_line('Customer JOB: ' ||TEST.JOB);
 10      dbms_output.put_line('Customer MGR: ' ||TEST.MGR);
 11      dbms_output.put_line('Customer HIREDATE: ' ||TEST.HIREDATE);
 12      dbms_output.put_line('Customer SAL: ' ||TEST.SAL);
 13      dbms_output.put_line('Customer COMM: ' ||TEST.COMM);
 14      dbms_output.put_line('Customer DEPTNO: ' ||TEST.DEPTNO);
 15      dbms_output.put_line('----------------------------');
 16    end loop;
 17  END;
 18  /
Customer ID: 7782
Customer NAME: CLARK
Customer JOB: MANAGER
Customer MGR: 7839
Customer HIREDATE: 09/06/1981 00:00:00
Customer SAL: 2450
Customer COMM:
Customer DEPTNO: 10
----------------------------
Customer ID: 7839
Customer NAME: KING
Customer JOB: PRESIDENT
Customer MGR:
Customer HIREDATE: 17/11/1981 00:00:00
Customer SAL: 5000
Customer COMM:
Customer DEPTNO: 10
----------------------------
Customer ID: 7934
Customer NAME: MILLER
Customer JOB: CLERK
Customer MGR: 7782
Customer HIREDATE: 23/01/1982 00:00:00
Customer SAL: 1300
Customer COMM:
Customer DEPTNO: 10
----------------------------

PL/SQL procedure successfully completed.

Report message to a moderator

Re: %rowtype error 06512 [message #635748 is a reply to message #635744]

Wed, 08 April 2015 02:19

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Forgot the moderator bit..

@Nasir, please use code tags when you post your code. Please read How to use [code] tags.

See the difference in the code posted by me and Michel, looks neat and readable. And it clearly differentiates between the message text and the code part. So please keep in mind from next time.

[Updated on: Wed, 08 April 2015 02:20]

Report message to a moderator

Re: %rowtype error 06512 [message #635749 is a reply to message #635748]

Wed, 08 April 2015 02:27

Nasir.azeem
Messages: 40
Registered: September 2014
Location: Karachi

Member

Thanks everybody

Report message to a moderator

Previous Topic:	how to retreive the second word using substring
Next Topic:	need help for oracle sql

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 26 05:56:13 CDT 2026