How To Handle Ora-01422 Error
Contents |
Kyte � Last updated: September 09, 2013 - 10:57 am UTC Category: Developer � Version: 2000 Latest Followup You Asked Hi Tom I'm trying to execute SQL statment but the result is : ORA-01422 exact fetch returns more than requested number of rows Cause: More rows were returned ora-01422 exact fetch returns from an exact fetch than specified. Action: Rewrite the query to return fewer rows or specify
Ora-01422 Exception Handling
more rows in the exact fetch. So How can I handle this proplem ? Thank you and we said... If you EXPECT the query to
Ora-01422 In Oracle Forms
return more then one row, you would code: for x in ( select * from t where ... ) loop -- process the X record here end loop; If you expect the query to return AT LEAST one record and
Ora 01422 Unhandled Exception
AT MOST one record, you would code: begin select * into .... from t where .... process.... exception when NO_DATA_FOUND then error handling code when no record is found when TOO_MANY_ROWS then error handling code when too many records are found end; If you just want the FIRST record declare c1 cursor for select * from t where ... begin open c1; fetch c1 into .. if ( c1%notfound ) then error handling for no record found end if; close c1; end; exact fetch returns more than requested number of rows exception handling Reviews Write a Review September 24, 2002 - 9:28 am UTC Reviewer: MW from Germany Hi, Tom I have a following Problem. I have a table and it's primary key value get from the trigger ( refer Sequence). It was working file. All of a sudden, when I try to insert values, then I got this Error. ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "myusr.TRG_LNKPCGRP_PCGRPNO", line 5 ORA-04088: error during execution of trigger 'myusr.TRG_LNKPCGRP_PCGRPNO' But this fetch value is in trigger is as Select SEQ_LNKPCGRP_PCGRPNO.NEXTVAL INTO iCounter FROM Dual; So I am realy in confusing oin this. Could you please help me on this. Thank you in advance. Followup September 24, 2002 - 3:36 pm UTC check to see if someone added a row to dual: ops$tkyte@ORA920.US.ORACLE.COM> @connect "/ as sysdba" sys@ORA920.US.ORACLE.COM> insert into dual values ( 'y' ); 1 row created. sys@ORA920.US.ORACLE.COM> select * from dual; D - X sys@ORA920.US.ORACLE.COM> select count(*) from dual; COUNT(*) ---------- 2 sys@ORA920.US.ORACLE.COM> dual is magic, make sure to COUNT(*) it, not just select * from it sys@ORA920.US.ORACLE.COM> declare x number; begin select my_seq.nextval into x from dual; end; 2 / declare x number; begin select my_seq.nextval into x from dual; end; * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 1 will happen when dual has more then 1 row September 25, 2002 - 4:27 am UTC Reviewer: MW from Ger
Digital Records Management Enterprise Content Management Strategy Digital Asset Management Oracle Imaging & Process Management Web Content Management Oracle WebCenter Portal Enterprise Portal Support Enterprise Portal Strategy Enterprise Portal Upgrade Oracle WebCenter Sites Sourcing Staffing & Recruiting the number specified in exact fetch is less than the rows returned. Recruiting Managed Services Candidate Registration Technical Focus Client Opportunities Support Solutions Training ora-01422 in cursor for loop Legacy to Oracle WebCenter Oracle Documents Cloud Service Next Generation AP Automation & Dynamic Discounting Oracle WebCenter Contract Lifecycle ora-01422 select into Management (CLM) Search ORA-01422: fetch returns more than requested number of rowsYou are here: Home / Resources / ORA-01422: fetch returns more than requested number of rows ORA-01422 A vast majority https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:981494932508 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 https://www.tekstream.com/resources/ora-01422-exact-fetch-returns-more-than-requested-number-of-rows/ 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
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of http://stackoverflow.com/questions/19779483/pl-sql-ora-01422-exact-fetch-returns-more-than-requested-number-of-rows this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question http://itknowledgeexchange.techtarget.com/itanswers/i-am-getting-ora-01422-exact-fetch-returns-more-than-requested-number-of-rows/ x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up exact fetch PL/SQL ORA-01422: exact fetch returns more than requested number of rows up vote 11 down vote favorite 5 I get keep getting this error I can't figure out what is wrong. "DECLARE * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 11" Here is my code. DECLARE rec_EMPID EMPLOYEE.EMPID%TYPE; rec_ENAME EMPLOYEE.ENAME%TYPE; rec_JOB EMPLOYEE.DESIGNATION%TYPE; rec_SAL exact fetch returns EMPLOYEE.SALARY%TYPE; rec_DEP DEPARTMENT.DEPT_NAME%TYPE; BEGIN SELECT EMPLOYEE.EMPID, EMPLOYEE.ENAME, EMPLOYEE.DESIGNATION, EMPLOYEE.SALARY, DEPARTMENT.DEPT_NAME INTO rec_EMPID, rec_ENAME, rec_JOB, rec_SAL, rec_DEP FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.SALARY > 3000; DBMS_OUTPUT.PUT_LINE ('Employee Nnumber: ' || rec_EMPID); DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------'); DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || rec_ENAME); DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------'); DBMS_OUTPUT.PUT_LINE ('Employee Designation: ' || rec_JOB); DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------'); DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || rec_SAL); DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------'); DBMS_OUTPUT.PUT_LINE ('Employee Department: ' || rec_DEP); END; / oracle plsql oracle11g sqlplus share|improve this question asked Nov 4 '13 at 23:46 Hiram 61114 add a comment| 1 Answer 1 active oldest votes up vote 20 down vote accepted A SELECT INTO statement will throw an error if it returns anything other than 1 row. If it returns 0 rows, you'll get a no_data_found exception. If it returns more than 1 row, you'll get a too_many_rows exception. Unless you know that there will always be exactly 1 employee with a salary greater than 3000, you do not want a SELECT INTO statement here. Most likely, you want to use a cursor to iterate over (potentially) multiple rows of data (I'm also assuming that you intended to do a proper join between the two tables
? Ask a question, help others, and get answers from the community Discussions Start a thread and discuss today's topics with top experts Blogs Read the latest tech blogs written by experienced community members I am getting ORA-01422: Exact fetch returns more than requested number of rows Inprise 610 pts. Tags: Thanks! We'll email youwhen relevant content isadded and updated. Following Follow INSERT statement Thanks! We'll email youwhen relevant content isadded and updated. Following Follow ORA-01422 Thanks! We'll email youwhen relevant content isadded and updated. Following Follow Oracle development Thanks! We'll email youwhen relevant content isadded and updated. Following Follow Oracle error messages Thanks! We'll email youwhen relevant content isadded and updated. Following Follow PL/SQL error messages Thanks! We'll email youwhen relevant content isadded and updated. Following Follow SELECT statement Hi experts, I am getting this error when I run my test case for my PL/SQL procedure, which has select and insert statements only. Not clear where I would have went wrong, Please guide me where should I check exactly. Its popping up with this error message which i caught in a exception piece after insert statement. Asked: January 6, 200910:56 AM Last updated: April 19, 20135:29 PM Related Questions PL/SQL Insert SQL insert error SQLCOD = -7008 when doing an insert into file using SQLRPGLE-2 SQLCOD = -7008 when doing an insert into file using SQLRPGLE Can not figure out this error message Answer Wiki Last updated: April 19, 20135:30 PM GMT Michael Tidmarsh48,905 pts. History Contributors Ordered by most recent Michael Tidmarsh48,905 pts. carlosdl80,565 pts. Thanks. We'll let you know when a new response is added. That error occurs in a SELECT … INTO statement, when your query returns more than one row. Check your where clause, and make the necessary changes in order to ensure just 1 row is returned. There is a predefined exception for that error. It is: too_many_rows. If you know your query could return more than 1 row in some cases, you should put an exception handler, something like this:
Begin SELECT xx into l_xx FROM table_x; Exception when too_many_rows then ' do something End;That error occurs in a SELECT ... INTO statement, when your query returns more than one row. Check your where clause, and make the necessary changes in order to ensure just 1 row is returned. There is a predefined exception for that error. It is: too_many_rows. If you know your query could return more than 1 row in some cases, you should put an exception handler, something like this:
Begin SELECT xx into l_xx FROM table_