Home » SQL & PL/SQL » SQL & PL/SQL » What is Error in this code
What is Error in this code [message #324467] Mon, 02 June 2008 10:41 Go to next message
mahran419
Messages: 4
Registered: March 2008
Location: egypt
Junior Member

please help me
i want show department_id and department_name
with all employees in every department

hr/hr@orcl
-----------------------------------------------------

declare
cursor dept_cursor is
select department_name, department_id
from departments;
fname employees.first_name%type;
sal employees.salary%type;
begin
for i in (select * from departments)
loop
dbms_output.put_line('Department_id ::'||' '||i.department_id);
dbms_output.put_line('Department_name ::'||' '||i.department_name);
select first_name, salary into fname, sal
from employees
where department_id = i.department_id;
dbms_output.put_line('Name of Employee ::'||' '||fname);
dbms_output.put_line('Salary of Employee ::'||' '||sal);
end loop;
end;./fa/4388/0/
  • Attachment: untitled2.jpg
    (Size: 253.92KB, Downloaded 240 times)
Re: What is Error in this code [message #324470 is a reply to message #324467] Mon, 02 June 2008 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You get the error when
select first_name, salary into fname, sal from employees where department_id = i.department_id;
statement above returns 2 or more rows because it has only a single variable to accept the returned value.
the current implementation is flawed & must be change to achieve proper execution.
Re: What is Error in this code [message #324471 is a reply to message #324467] Mon, 02 June 2008 11:05 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-01422: exact fetch returns more than requested number of rows
Cause: The number specified in exact fetch is less than the rows returned.
Action: Rewrite the query or change number of rows requested

Your query line 11 returns several values and of course several values can't be stored in a scalar variables.

Next time, use SQL*Plus and copy and paste your session with line numbers.
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: How to provide access on procedure/functions to other users
Next Topic: index
Goto Forum:
  


Current Time: Sun Dec 04 06:24:33 CST 2016

Total time taken to generate the page: 0.08516 seconds