ORA-01422: exact fetch returns more than requested number of rows
A select into always need to returns 1 row, if there is more than 1 row, this error is generated, if there are no rows returned, you will get the ORA-01403: No data found error message
eg:
declareAlternatively you can create a cursor loop to process more than 1 row:
do dba_objects%rowtype;
begin
select *
into do
from dba_objects
where object_id in (1, 2);
end;
/
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
declare
do dba_objects%rowtype;
begin
for r_obj in (select * from dba_objects where object_id in (1,2, 3) )
loop
-- do processing here with r_obj
dbms_output.put_line(r_obj.object_id);
end loop;
end;
/
Should you have a PL/SQL procedure with lots of commands inside, you can choose to trace the session. This will generate a trace file which contains the error together with the offending statement.
Welcome to our forum for Oracle error: ORA-01422 Add your own message
do dba_objects%rowtype;
begin
select *
into do
from dba_objects
where object_id in (1, 2);
end;
/
begin
declare snam varchar(20):=' ';
begin
select sname into snam from sample1;
end;
end;
error is
ora_01422:exact fetch returns more than requested number of rows
ora-06512 at schmaname.procedurename line6
ora-06512:at line2
but sample1 table have 2records are there
As you state that you have 2 records in sample1, you will get this ORA-01422 error.
Change your query so it only returns 1 row, eg.
select sname into snam from sample1 where rownum=1;
Declare
Org_SrNo Numeric;
Emp_Joining_Date Date;
Emp_SrNo Numeric;
Begin
Emp_SrNo := 87;
Select To_Date(emp_doj), emp_company
Into Emp_Joining_Date, Org_SrNo
From hr_emp_mst
Where emp_srno = Emp_SrNo;
Exception
When No_Data_Found
Then
DBMS_OUTPUT.PUT_LINE('No Data Found Exception occured while retrieving joining date & Company for Emp SrNo: ' || Emp_SrNo);
When Too_Many_Rows
Then
DBMS_OUTPUT.PUT_LINE('Too Many Rows Exception occured while retrieving joining date & Company for Emp SrNo: ' || Emp_SrNo);*/
End;
When i execute this block it goes into Too_Many_Rows exception block even though there exists only one record in the table against Emp_SrNo=87. In the same pl/sql block if i change the select query to the one below, then it executes fine with no error.
Select To_Date(emp_doj), emp_company
Into Emp_Joining_Date, Org_SrNo
From hr_emp_mst
Where emp_srno = 87;
I am not able to find where I am going wrong.
Any help would be appreciated.
Regards
Ravi
The reason was that in my script I put an insert just before the select, thus I created a second row that raised the TOO_MANY_ROWS exception.
That's silly but it can happen :) Hope this will help others.
FOR x IN (SELEC ........
....)
LOOP
.....
END LOOP;
I used some functions to get values; but when I executed those functions I got NO_DATA_FOUND error!!!
Not sure what is happening
Hi,
We are using Oracle R12 with multi org and every org is working fine. We have create new entity and new org. By mistek we have run "Replicate Seed Data" with out any parameter due to this mistek now all org setups are showing an error. "ORA-01422: exact fetch returns more than requested number of rows".
We want not know that who can we resolve this mistek.
Rahul
You need to check for duplicate records in the AR_SYSTEM_PARAMETERS_ALL table, and remove them
Be sure to take a backup first!
Dear Sir,
If I open the form the above error occurs. So kindly suggest us how to solve the problem.
anand
You need to check what code is executed when you open the form.
There is probably a bug inside the code.
helo sir,
how to select all values in a table using pl/sql command
You will need to create a cursor and select using the cursor:
declare
rc v$parameter%rowtype;
begin
for rc in (select * from v$parameter) loop
-- do processing with rc.column_name;
end loop;
end;
Please provide your help.
please answer the question raised by ravi. Here I am pasting it:
Hi, i m executing the below query pl/sql block.
Declare
Org_SrNo Numeric;
Emp_Joining_Date Date;
Emp_SrNo
Numeric;
Begin
Emp_SrNo := 87;
Select
To_Date(emp_doj), emp_company
Into Emp_Joining_Date,
Org_SrNo
From hr_emp_mst
Where emp_srno =
Emp_SrNo;
Exception
When No_Data_Found
Then
DBMS_OUTPUT.PUT_LINE('No Data Found Exception occured
while retrieving joining date & Company for Emp SrNo: ' ||
Emp_SrNo);
When Too_Many_Rows
Then
DBMS_OUTPUT.PUT_LINE('Too Many Rows Exception occured while
retrieving joining date & Company for Emp SrNo: ' || Emp_SrNo);*/
End;
When
i execute this block it goes into Too_Many_Rows exception block even
though there exists only one record in the table against Emp_SrNo=87. In
the same pl/sql block if i change the select query to the one below,
then it executes fine with no error.
Select To_Date(emp_doj),
emp_company
Into Emp_Joining_Date, Org_SrNo
From hr_emp_mst
Where
emp_srno = 87;
I am not able to find where I am going wrong.
Any
help would be appreciated.
You are comparing the column to its own:
emp_srno =
Emp_SrNo;
i.e. your variable name in pl/sql is the same as the column name. Column names take precedence over pl/sql variable names.
The easiest solution is to rename the pl/sql variable name
Declare
Org_SrNo Numeric;
Emp_Joining_Date Date;
p_Emp_SrNo
Numeric;
Begin
Emp_SrNo := 87;
Select
To_Date(emp_doj), emp_company
Into Emp_Joining_Date,
Org_SrNo
From hr_emp_mst
Where emp_srno =
p_Emp_SrNo;
Exception
When No_Data_Found
Then
DBMS_OUTPUT.PUT_LINE('No Data Found Exception occured
while retrieving joining date & Company for Emp SrNo: ' ||
Emp_SrNo);
When Too_Many_Rows
Then
DBMS_OUTPUT.PUT_LINE('Too Many Rows Exception occured while
retrieving joining date & Company for Emp SrNo: ' || Emp_SrNo);*/
End;
Thank you very much for clarifying my silly doubt, but it really ate my time :(
CREATE OR REPLACE PROCEDURE x( latest_id varchar2)
IS
dest_clob CLOB;
src_clob BFILE := BFILENAME('NEW_DIR', 'w.csv');
dst_offset number := 1 ;
src_offset number := 1 ;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning number;
latest_r_id varchar2(200) := 'where r_id = '||chr(39)||latest_id||chr(39);
BEGIN
DBMS_OUTPUT.ENABLE(100000);
--dbms_output.put_line('query' ||latestid);
update phonescrub_request set downloaded_file = empty_clob() ||latest_r_id
returning downloaded_file INTO dest_clob;
........ ...
end;
CREATE OR REPLACE PROCEDURE x( latest_id varchar2)
IS
dest_clob CLOB;
src_clob BFILE := BFILENAME('NEW_DIR', 'w.csv');
dst_offset number := 1 ;
src_offset number := 1 ;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning number;
latest_r_id varchar2(200) := 'where r_id = '||chr(39)||latest_id||chr(39);
BEGIN
DBMS_OUTPUT.ENABLE(100000);
--dbms_output.put_line('query' ||latestid);
update phonescrub_request set downloaded_file = empty_clob() ||latest_r_id
returning downloaded_file INTO dest_clob;
........ ...
end;
i am getting the same error "ORA-01422: exact fetch returns more than requested number of rows" while trying to execute the procedure,pointing the update quey.
someone plz help
Your table will probably contain more than 1 record, so this update statement:
update phonescrub_request set downloaded_file = empty_clob() ||latest_r_id
returning downloaded_file INTO dest_clob;
Will return more than 1 row. You need to add a where condition
CREATE
OR REPLACE PROCEDURE ECO_DM_CHECK1_2 IS
ECO_NUMBER varchar2(30);
ECO_STATUS varchar2(30);
ECO_STATUS_WF varchar2(30);
PUBLISH_DATE date;
begin
select distinct
eco.eco_number eco_numb,
eco.eco_status iman_eco_staus,
ecowf.eco_status ecowf_status,
ecowf.publish_date publis_dat
into ECO_NUMBER, ECO_STATUS, ECO_STATUS_WF, PUBLISH_DATE
from infodm.iman_eco eco, infodm.iman_eco_wf ecowf
where eco.eco_number = ecowf.eco_number (+) and
(eco.eco_status <> ecowf.eco_status or eco.eco_status is null);
end ECO_DM_CHECK1_2;
If you run the select in sqlplus/toad/sql developer/..., do you get more than 1 row back?
select distinct
eco.eco_number eco_numb,
eco.eco_status iman_eco_staus,
ecowf.eco_status ecowf_status,
ecowf.publish_date publis_dat
into ECO_NUMBER, ECO_STATUS, ECO_STATUS_WF, PUBLISH_DATE
from infodm.iman_eco eco, infodm.iman_eco_wf ecowf
where eco.eco_number = ecowf.eco_number (+) and
(eco.eco_status <> ecowf.eco_status or eco.eco_status is null);
If so, you will need to work with a cursor and fetch all rows into variables
If I run the select distinct I am getting more than one rows. How to proceed with cursor for this query?
eco.eco_number eco_numb,
eco.eco_status iman_eco_staus,
ecowf.eco_status ecowf_status,
ecowf.publish_date publis_dat
into ECO_NUMBER, ECO_STATUS, ECO_STATUS_WF, PUBLISH_DATE
from infodm.iman_eco eco, infodm.iman_eco_wf ecowf
where eco.eco_number = ecowf.eco_number (+) and
(eco.eco_status <> ecowf.eco_status or eco.eco_status is null);
declare
cursor c_select is
select distinct
eco.eco_number eco_numb,
eco.eco_status iman_eco_staus,
ecowf.eco_status ecowf_status,
ecowf.publish_date publis_dat
into ECO_NUMBER, ECO_STATUS, ECO_STATUS_WF, PUBLISH_DATE
from infodm.iman_eco eco, infodm.iman_eco_wf ecowf
where eco.eco_number = ecowf.eco_number (+) and
(eco.eco_status <> ecowf.eco_status or eco.eco_status is null);
begin
for r_select in c_select loop
-- do processing here with r_select.eco_number, ...
end loop;
end;
/
If I am ddeclaring the cursor, it is giving ORA-00103 error
If I am ddeclaring the cursor, it is giving ORA-00103 error
You don't need to include following when using a cursor:
into ECO_NUMBER, ECO_STATUS, ECO_STATUS_WF, PUBLISH_DATE
Just remove the line
I have declared the following sql
CREATE
OR REPLACE PROCEDURE ECO_DM_CHECK1_2 IS
cursor c_select is
select distinct
eco.eco_number eco_numb,
eco.eco_status iman_eco_staus,
ecowf.eco_status ecowf_status,
ecowf.publish_date publis_dat
--into ECO_NUMBER, ECO_STATUS, ECO_STATUS_WF, PUBLISH_DATE
from infodm.iman_eco eco, infodm.iman_eco_wf ecowf
where eco.eco_number = ecowf.eco_number (+) and
(eco.eco_status <> ecowf.eco_status or eco.eco_status is null);
begin
for r_select in c_select loop
do processing here with r_select.eco_number, r_select.iman_eco_status, r_select.ecowf_status, r_select.publis_dat
end loop;
end;
But it is giving error:
Error: PLS-00103: Encountered the symbol "PROCESSING" when expecting one of the following:
:= . ( @ % ;
Line: 19
Text: do processing here with r_select.eco_number, r_select.iman_eco_status, r_select.ecowf_status, r_select.publis_dat
The do processing here was with 2 dashed --, meaning comment.
You need to implement your logic there. What do you want to do with the fetched records.
The fetched records are used to find the discrepancy between the tables and fix it.
Actually, the two tables," infodm.iman_eco eco"," infodm.iman_eco_wf ecowf" need to have same status, If status is different I ll run this procedure and find the mismatch datas and fix it. How to give this in cursor ?
We only assist in trying to solve error codes, by either provide solutions or redirect you to the documentation.
We are not offering code design as a free service. For that, you can hire one of our experienced consultants
How to proceed with cursor for the above query?
For the above query, it needs to retrive all the data from the table
Hi
I created a SQL query against the Post Change Trigger of my Item.It compiled correctly in Oracle Form Builder and ERP.But when I tried to Enter the Demand ID in Demand_Id field It created th following Error
ORA-01422: exact fetch returns more than requested number of rows
--------------------------------------------------------------
FRM-40735: POST-CHANGE trigger raised unhandled exception ORA-01422.
My SQL query is
begin
select
--m.demand_id,
m.for_dept,
d.qty,
msi.description
into
--:demand_id,
:for_dept,
:qty,
:item_des
from
ifl.cc_comp_chart_d d,
ifl.cc_comp_chart_m m,
apps.mtl_system_items_b msi
where
m.chart_id = d.chart_id
and msi.inventory_item_id = d.item_id
and msi.organization_id = 82;
end;
Please help me how can i sort out this problem
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.