In my previous articles, I have given the proper idea of different oracle errors, which are frequently come. In this article, I will try to explain another common error, which has been searched on google approximately 10 k times per month. ORA-01422 is another simple error, which is commonly come in database when select into statement when it retrieves more than one row. All oracle errors are categorized in to two types one is network and memory issues and other are syntax errors come due to bad syntax. ORA-01422 is user-initiated mistake resulting from either a typo or a misunderstanding of how Oracle functions may work.
Cause and resolution of this error:
The ORA-01422 error is most common error, which will come because of the multiple reasons .The main reason of this error, is ‘SELECT INTO’ statement. Oracle has one important inbuilt error named ‘TOO_MANY_ROWS’ error.
1.More than requested rows in “Select into”:
If ‘Select Into’ statement returns more than one rows in variable then this error will come. The error says fetch returns more than requested number of rows means ‘Exact Fetch’ will return ‘More than one row’. The basic problem will come from ‘Select Into’ statement from oracle, which will return more than one rows. The select statement will fetch the record from multiple databases and multiple tables and it will store into variable. The Oracle engine will fetch more than one record for single specified row. The ORA-01422 error will trigger when PLSQL engine returns multiple rows of data. The select into statement has default setting, which is designed to retrieve one row, but when it retrieves more than one rows, the ORA-01422 error will trigger.
Consider following real life example:
If table named ‘test_error’ has more than one records and user tries to fetch all records in to one variable this error will come.
Procedure :
DECLARE
v_test VARCHAR2(30);
BEGIN
SELECT roll_no INTO v_test FROM test_error; —-Error statement
end;
Output:
Error report:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
01422. 00000 – “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
Resolution of this error:
Handle the exception:
Just make the small change in the specified code and write the exception block for the same.
Procedure :
DECLARE
V_SRNUM VARCHAR2(20);
DECLARE
v_test VARCHAR2(30);
BEGIN
SELECT roll_no INTO v_test FROM test_error;
exception WHEN too_many_rows THEN
dbms_output.put_line(‘Errors fetching are more than one’);
end;
The above procedure will handle the error and anonyms block will complete successfully.So if this kind of error will occure handle user defined exception named ‘TOO_MANY_ROWS’.
2. Add cursor with loop:
There are many primitive adjustments to resolve this error. The approach needs to be chosen by the user dependent on the database tables and scenarios. The error will occur because of multiple rows are returning so user will change the code by adding the cursor.
Therefore, the above procedure will be:
Declare
v_test VARCHAR2(30);
begin
for c in (SELECT roll_no INTO v_test FROM test_error)
loop
v_test := c.roll_no;
end loop;
end;
3. Recommend to use aggregate function:
This error will come because of multiple rows selection. Therefore, as per requirement if user uses the aggregate function like sum, count then it will fetch only one row.
Therefore, the above procedure will be:
DECLARE
v_test VARCHAR2(30);
BEGIN
SELECT count(roll_no) INTO v_test FROM test_error; —-Error statement
end;
4. Use of bulk collect:
This will pull more rows and variables but in a concise manner. However, be wary of using BULK COLLECT excessively as it can use a great deal of memory.
So there are different ways to deal with this error, my best recommendation is to use the cursor for fetching more than one rows and processing it.
ORA-01422
A vast majority of Oracle errors can be broken down into two categories: memory & network problems and issues arising from improper use of syntax & phrasing. Many of the former errors involve interacting with a network administrator to determine where faulty connections and misaligned partitioning of memory are stemming from. The latter, which the ORA-01422 can most aptly identify with, concerns a user-initiated mistake resulting from either a typo or a misunderstanding of how Oracle functions may work.
So what syntax mistakes are causing the ORA-01422 error? Why are they a problem? And most importantly, how can we fix it? Well, let’s start at the beginning and talk briefly about just what exactly an ORA-01422 really is.
The Problem
Oracle describes the ORA-01422 error as the “exact fetch” returning “more than requested number of rows”. The type of vague contextual clue that Oracle attaches to this message concerning the error’s origins can be quite infuriating initially.
The basics of the problem derive from a failed SELECT INTO statement. This statement pulls data from one or more database tables and subsequently assigns the information to specified variables. In its default setting, the SELECT INTO statement will return one or more columns from a single specified row. This is where the error is being thrown.
When an ORA-01422 is triggered, your SELECT INTO statement is retrieving multiple rows of data or none at all. If it is returning multiple, the predefined exception TOO_MANY_ROWS will be raised, and for no returns the PL/SQL will raise NO_DATA_FOUND. Because the SELECT INTO statement in its default setting is designed to retrieve only one row, the system responds with an error at either one.
The Solution
There are a number of fixes and preemptive adjustments that you can make in order to prevent the issuance of an ORA-01422 error. How you choose to approach it is entirely dependant on your data tables, but we will go over some strategies.
First, to protect against no rows being returned, you can select the result of any type of aggregating function (such as AVG or COUNT). This will be helpful because a function like COUNT will at the least be guaranteed to return some type of value.
Much more common will be an error resulting from multiple rows being returned. An initial step can be to check that the WHERE clause in your statement is exclusive enough to only matchup one row. This will require having knowledge of your rows and how they are similar & different so that you can know how to target results from a particular row.
Another step that you can take to offset multiple row quandaries is to run a BULK COLLECT in a table to pull more variables. This will pull more rows and variables but in a concise manner. However, be wary of using BULK COLLECT excessively as it can use a great deal of memory.
Finally, a great recommendation would be to replace your SELECT INTO statement with a cursor. A cursor is a tool through which you can give a name to a SELECT statement and change the data within that statement as you please. This will effectively create a loop that will fetch multiple rows without generating the error. Below is an example of what this would look like in action.
Example of ORA-01422 Error
SQL> declare
v_student number;
begin
select student into v_student from michael.std where deptno=12;
end;
/
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
Example of Cursor Solution
SQL>declare
v_student number;
begin
for c in (select student into v_student from Michael.std where deptno=12)
loop
v_student := c.student;
end loop;
end;
/
Looking forward
The ORA-01422 error differs from many in that it could involve a bit of coding on your end to resolve. This is a far cry from the simple Oracle errors that simply require changing the punctuation of a statement quickly and moving on. With that said, the tools above should be plenty to get started and work to develop a solution. In the event that you find yourself completely lost, it can never hurt to contact a licensed Oracle consultant for more information.
Содержание
- ORA-01422: fetch returns more than requested number of rows | ORA-01422
- ORA-01422: Exact Fetch Returns More Than Requested Number Of Rows (Doc ID 2588206.1)
- Applies to:
- Symptoms
- Changes
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- Autoinvoice Error: ORA-1422: exact fetch returns more than requested number of rows (Doc ID 1207707.1)
- Applies to:
- Symptoms
- Changes
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- 12c : DB Upgrade Fails with Errors «ORA-01422: exact fetch returns more than requested number of rows» (Doc ID 2080793.1)
- Applies to:
- Symptoms
- Cause
- To view full details, sign in with your My Oracle Support account.
- Don’t have a My Oracle Support account? Click to get started!
- Error oracle ora 01422
ORA-01422: fetch returns more than requested number of rows | ORA-01422
In my previous articles, I have given the proper idea of different oracle errors, which are frequently come. In this article, I will try to explain another common error, which has been searched on google approximately 10 k times per month. ORA-01422 is another simple error, which is commonly come in database when select into statement when it retrieves more than one row. All oracle errors are categorized in to two types one is network and memory issues and other are syntax errors come due to bad syntax. ORA-01422 is user-initiated mistake resulting from either a typo or a misunderstanding of how Oracle functions may work.
The ORA-01422 error is most common error, which will come because of the multiple reasons .The main reason of this error, is ‘SELECT INTO’ statement. Oracle has one important inbuilt error named ‘TOO_MANY_ROWS’ error.
1.More than requested rows in “Select into”:
If ‘Select Into’ statement returns more than one rows in variable then this error will come. The error says fetch returns more than requested number of rows means ‘Exact Fetch’ will return ‘More than one row’. The basic problem will come from ‘Select Into’ statement from oracle, which will return more than one rows. The select statement will fetch the record from multiple databases and multiple tables and it will store into variable. The Oracle engine will fetch more than one record for single specified row. The ORA-01422 error will trigger when PLSQL engine returns multiple rows of data. The select into statement has default setting, which is designed to retrieve one row, but when it retrieves more than one rows, the ORA-01422 error will trigger.
Consider following real life example:
If table named ‘test_error’ has more than one records and user tries to fetch all records in to one variable this error will come.
Procedure :
SELECT roll_no INTO v_test FROM test_error; —-Error statement
Output:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
01422. 00000 – “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
Resolution of this error:
Handle the exception:
Just make the small change in the specified code and write the exception block for the same.
Procedure :
SELECT roll_no INTO v_test FROM test_error;
exception WHEN too_many_rows THEN
dbms_output.put_line(‘Errors fetching are more than one’);
The above procedure will handle the error and anonyms block will complete successfully.So if this kind of error will occure handle user defined exception named ‘TOO_MANY_ROWS’.
2. Add cursor with loop:
There are many primitive adjustments to resolve this error. The approach needs to be chosen by the user dependent on the database tables and scenarios. The error will occur because of multiple rows are returning so user will change the code by adding the cursor.
Therefore, the above procedure will be:
for c in (SELECT roll_no INTO v_test FROM test_error)
3. Recommend to use aggregate function:
This error will come because of multiple rows selection. Therefore, as per requirement if user uses the aggregate function like sum, count then it will fetch only one row.
Therefore, the above procedure will be:
SELECT count(roll_no) INTO v_test FROM test_error; —-Error statement
4. Use of bulk collect:
This will pull more rows and variables but in a concise manner. However, be wary of using BULK COLLECT excessively as it can use a great deal of memory.
So there are different ways to deal with this error, my best recommendation is to use the cursor for fetching more than one rows and processing it.
Источник
ORA-01422: Exact Fetch Returns More Than Requested Number Of Rows (Doc ID 2588206.1)
Last updated on APRIL 12, 2022
Applies to:
Symptoms
SQL> select distinct (a.username) «USER», count (r.xmldata) «TOTAL»
from dba_users a, xdb.xdb$resource r
where sys_op_rawtonum (extractvalue (value(r),’/Resource/OwnerID/text()’)) = a.USER_ID
group by a.username;
ERROR at line 2:
ORA-01422: exact fetch returns more than requested number of rows
ORA-04045: errors during recompilation/revalidation of SYS.XDS_ACL
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at «XDB.DBMS_XMLSCHEMA_INT», line 122
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at «XDB.DBMS_XMLSCHEMA_INT», line 122
Changes
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use
Источник
Autoinvoice Error: ORA-1422: exact fetch returns more than requested number of rows (Doc ID 1207707.1)
Last updated on JUNE 07, 2021
Applies to:
Oracle Receivables — Version 12.0.0 to 12.0.0 [Release 12.0]
Oracle Receivables — Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
EXECUTABLE:RAXTRX — Autoinvoice Import Program
Symptoms
When attempting to run Autoinvoice, the following error is displayed in the log file captured as instructed from :
ORA-1422: exact fetch returns more than requested number of rows
This error can be prefaced with different values and suffix strings.В For example:
APP-AR-11526: ORA-01422: exact fetch returns more than requested number of rows
ORA-1422 errors occur when the code makes use of a SELECT . INTO . expecting to pick up one row, but instead more than one row is returned. When there is no TOO_MANY_ROWS exception handler in the block, this error is returned.В
Changes
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
Error Presentations and Solutions
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use
Источник
12c : DB Upgrade Fails with Errors «ORA-01422: exact fetch returns more than requested number of rows» (Doc ID 2080793.1)
Last updated on JULY 20, 2022
Applies to:
Symptoms
Database upgrade to 12.1.0.2 fails with below errors:
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 24
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 25
Oracle Server INVALID 12.1.0.2.0 01:22:21
Database upgrade to 12.2.0.1 fails with below errors:
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use
Источник
Error oracle ora 01422
A vast majority of Oracle errors can be broken down into two categories: memory & network problems and issues arising from improper use of syntax & phrasing. Many of the former errors involve interacting with a network administrator to determine where faulty connections and misaligned partitioning of memory are stemming from. The latter, which the ORA-01422 can most aptly identify with, concerns a user-initiated mistake resulting from either a typo or a misunderstanding of how Oracle functions may work.
So what syntax mistakes are causing the ORA-01422 error? Why are they a problem? And most importantly, how can we fix it? Well, let’s start at the beginning and talk briefly about just what exactly an ORA-01422 really is.
Oracle describes the ORA-01422 error as the “exact fetch” returning “more than requested number of rows”. The type of vague contextual clue that Oracle attaches to this message concerning the error’s origins can be quite infuriating initially.
The basics of the problem derive from a failed SELECT INTO statement. This statement pulls data from one or more database tables and subsequently assigns the information to specified variables. In its default setting, the SELECT INTO statement will return one or more columns from a single specified row. This is where the error is being thrown.
When an ORA-01422 is triggered, your SELECT INTO statement is retrieving multiple rows of data or none at all. If it is returning multiple, the predefined exception TOO_MANY_ROWS will be raised, and for no returns the PL/SQL will raise NO_DATA_FOUND. Because the SELECT INTO statement in its default setting is designed to retrieve only one row, the system responds with an error at either one.
There are a number of fixes and preemptive adjustments that you can make in order to prevent the issuance of an ORA-01422 error. How you choose to approach it is entirely dependant on your data tables, but we will go over some strategies.
First, to protect against no rows being returned, you can select the result of any type of aggregating function (such as AVG or COUNT). This will be helpful because a function like COUNT will at the least be guaranteed to return some type of value.
Much more common will be an error resulting from multiple rows being returned. An initial step can be to check that the WHERE clause in your statement is exclusive enough to only matchup one row. This will require having knowledge of your rows and how they are similar & different so that you can know how to target results from a particular row.
Another step that you can take to offset multiple row quandaries is to run a BULK COLLECT in a table to pull more variables. This will pull more rows and variables but in a concise manner. However, be wary of using BULK COLLECT excessively as it can use a great deal of memory.
Finally, a great recommendation would be to replace your SELECT INTO statement with a cursor. A cursor is a tool through which you can give a name to a SELECT statement and change the data within that statement as you please. This will effectively create a loop that will fetch multiple rows without generating the error. Below is an example of what this would look like in action.
Example of ORA-01422 Error
SQL> declare
select student into v_student from michael.std where deptno=12;
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4
Example of Cursor Solution
SQL>declare
for c in (select student into v_student from Michael.std where deptno=12)
The ORA-01422 error differs from many in that it could involve a bit of coding on your end to resolve. This is a far cry from the simple Oracle errors that simply require changing the punctuation of a statement quickly and moving on. With that said, the tools above should be plenty to get started and work to develop a solution. In the event that you find yourself completely lost, it can never hurt to contact a licensed Oracle consultant for more information.
Источник
April 27, 2021
I got “ORA-01422: exact fetch returns more than requested number of rows ” error in Oracle.
ORA-01422: exact fetch returns more than requested number of rows
Details of error are as follows.
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
exact fetch returns more than requested number of rows
This ORA-01422 errors are related with the number specified in exact fetch is less than the rows returned.
To solve this error, you should rewrite the query or change number of rows requested
If you got this error while using the SELECT INTO, then Replace your SELECT INTO statement with a cursor.
For example, if you tried to execute the following SQL statement, you will get this error.
SELECT id INTO NUM FROM employers WHERE name = 'MEHMET';
You can read the following post to learn Cursors.
PL/SQL For Practitioners – #3 Explicit Cursor FOR LOOP Statement
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
1,737 views last month, 1 views today
About Mehmet Salih Deveci
I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].- -Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için [email protected] a mail atabilirsiniz.