| ORA-01422: exact fetch returns more than requested number of rows. [message #472123] |
Wed, 18 August 2010 23:19  |
 |
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
|
Senior Member |
|
|
Dear,
ORA-01422: exact fetch returns more than requested number of rows.
I receive this error because i tried to introduce the below coding in a post query of the block.
begin
select supplier_cd into :fin_ex_rev_head.vendor_code
from fin_ex_rev_receipts
where receipt_date
between :keyblock.receipt_date
and :keyblock.to_date
and receipt_no||to_char(receipt_date,'DDMMRRRR') not in
(select receipt_no||to_char(receipt_date,'DDMMRRRR') from fin_ex_rev_head where receipt_no is not null)
order by invoice_no;
end;
i know the query gives more data and i need that too. what shall i do in this situation.
Thanks is Advance.
Stalin Ephraim.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-01422: exact fetch returns more than requested number of rows. [message #475571 is a reply to message #472123] |
Wed, 15 September 2010 15:38  |
tasos
Messages: 3
Registered: September 2010
|
Junior Member |
|
|
You can use cursors to solve this issue.
CREATE TABLE employee
(
emp_id,
first_name VARCHAR2(50),
salary FLOAT(126)
)
populated with many records (table can have a primary key etc.- out of scope)
If you try to execute the pl/sql below you should get an error
ORA-01422: exact fetch returns more than requested number of rows
declare
name varchar2(40);
sal number;
begin
select salary into sal from
employee;
dbms_output.put_line(sal);
end;
This is because the sql query returns more than 1 rows. In order to get all records you could use cursors as shown below.
declare
cursor c1 is
select first_name, salary
from employee;
name varchar2(40);
sal number;
counter number;
begin
--find number of records
select count(emp_id) into counter
from employee;
open c1;
--print all
for i in 1..counter loop
fetch c1 into name,sal;
dbms_output.put_line(name||' '||sal);
end loop;
close c1;
end;
Results
Tasos 2000
Spyros 2500
Panos 5000
George 1200
Jenny 2300
Julia 9000
Mary 9000
|
|
|
|
| Re: ORA-01422: exact fetch returns more than requested number of rows. [message #475572 is a reply to message #475571] |
Wed, 15 September 2010 15:58 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Can you please read the orafaq forum guide and follow it in future posts - especially the bit about formatting your posts.
2) Variables should be typed to their corresponding columns with %type.
3) If you're going to count records use count(*)
4) Counting records in this case is a complete waste of time. A cursor for loop will retrieve all records without you having to find out how many there are.
I'd rewrite your code like this:
BEGIN
FOR rec IN (SELECT first_name, salary FROM employee) LOOP
dbms_output.put_line(rec.first_name||' '||rec.salary);
END LOOP;
END;
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
