Error Ora 01422 Exact Fetch Returns
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 ora-01422 exception handling result is : ORA-01422 exact fetch returns more than requested number of ora-01422 in oracle forms rows Cause: More rows were returned from an exact fetch than specified. Action: Rewrite the query to return fewer the number specified in exact fetch is less than the rows returned. rows or specify more rows in the exact fetch. So How can I handle this proplem ? Thank you and we said... If you EXPECT the query to return more then one
Ora 01422 Unhandled Exception
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 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 exact fetch returns more than requested number of rows exception handling 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; 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: o
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie ora-01422 in cursor for loop Humor Advertisement Oracle Basics ALIASES AND AND & OR BETWEEN COMPARISON OPERATORS
Ora-01422 Select Into
DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS NULL
"exact Fetch Returns More Than Requested Number Of Rows" Cursor
JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:981494932508 Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions Oracle / PLSQL: ORA-01422 Error Message Learn the cause and how to resolve the ORA-01422 error message in Oracle. Description When you encounter an ORA-01422 error, the following error message will appear: ORA-01422: exact fetch returns more than requested number of rows Cause You https://www.techonthenet.com/oracle/errors/ora01422.php tried to execute a SELECT INTO statement and more than one row was returned. Resolution The option(s) to resolve this Oracle error are: Option #1 Rewrite your SELECT INTO statement so that only one row is returned. Option #2 Replace your SELECT INTO statement with a cursor. For example, if you tried to execute the following SQL statement: SELECT supplier_id INTO cnumber FROM suppliers WHERE supplier_name = 'IBM'; And there was more than one record in the suppliers table with the supplier_name of IBM, you would receive the ORA-01422 error message. In this case, it might be more prudent to create a cursor and retrieve each row if you are unsure of how many records you might retrieve. Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. We use advertisements to support this website and fund the development of new content. Copyright © 2003-2016 TechOnTheNet.com. All rights reserved.
ORA-01422: exact fetch returns more than requested number of rows. [message #472123] Wed, 18 August 2010 23:19 stalin4d Messages: 226Registered: May 2010 Location: Chennai, Tamil Nadu, Indi... Senior Member Dear, ORA-01422: exact fetch returns more than requested number http://www.orafaq.com/forum/t/160843/ 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 exact fetch is Advance. Stalin Ephraim. Report message to a moderator Re: ORA-01422: exact fetch returns more than requested number of rows. [message #472148 is a reply to message #472123] Thu, 19 August 2010 01:38 orakam1808 Messages: 7Registered: August 2010 Junior Member Hi Stalin, You can use Cursor if you want to return more than one rows. Select Into won't work here as it always returns one row. Hope, this exact fetch returns will help. kam Report message to a moderator Re: ORA-01422: exact fetch returns more than requested number of rows. [message #472151 is a reply to message #472148] Thu, 19 August 2010 01:48 stalin4d Messages: 226Registered: May 2010 Location: Chennai, Tamil Nadu, Indi... Senior Member Kam, Cursor too tried, coz its a form level trigger similar to a procedure here we cannot return a value. this is the error i receive: In a procedure, RETURN statement cannot contain an expression [Updated on: Thu, 19 August 2010 01:49]Report message to a moderator Re: ORA-01422: exact fetch returns more than requested number of rows. [message #472155 is a reply to message #472151] Thu, 19 August 2010 02:11 cookiemonster Messages: 12333Registered: September 2008 Location: Rainy Manchester Senior Member What exactly do you want to do with values returned by this select? Report message to a moderator Re: ORA-01422: exact fetch returns more than requested number of rows. [message #473003 is a reply to message #472155] Wed, 25 August 2010 06:12 tamzidulamin Messages: 132Registered: October 2009 Location: Dhaka Senior Member A select statement returns three types result i) Single Record ii) Multiple Record iii) No Record So you have to think first, what will be your des