Oracle Error Code Ora-01422
Contents |
Kyte � Last updated: September 09, 2013 - 10:57 am UTC Category: Developer � Version: 2000 Whilst you are here, check out some content from the AskTom team: ora-01422 exception handling Table Functions, Part 2: Returning complex (non-scalar) datasets Latest Followup You Asked Hi
Ora 01422 Unhandled Exception
Tom I'm trying to execute SQL statment but the result is : ORA-01422 exact fetch returns more than ora-01422 in cursor for loop requested number of rows Cause: More rows were returned from an exact fetch than specified. Action: Rewrite the query to return fewer rows or specify more rows in the exact fetch. the number specified in exact fetch is less than the rows returned. So How can I handle this proplem ? Thank you and we said... If you EXPECT the query to 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 AT MOST
Ora-01422 Select Into
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; 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. Cou
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 trigger raised unhandled exception ora 01422 Sites Sourcing Staffing & Recruiting Recruiting Managed Services Candidate Registration Technical oracle too many rows Focus Client Opportunities Support Solutions Training Legacy to Oracle WebCenter Oracle Documents Cloud Service Next Generation AP Automation
Frm-40735 Post-query Trigger Raised Unhandled Exception Ora-01422
& Dynamic Discounting Oracle WebCenter Contract Lifecycle Management (CLM) Search ORA-01422: fetch returns more than requested number of rowsYou are here: Home / Resources / ORA-01422: fetch returns https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:981494932508 more than requested number of rows 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. https://www.tekstream.com/resources/ora-01422-exact-fetch-returns-more-than-requested-number-of-rows/ 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
Action: Rewrite the query or change number of rows requested Reference: Oracle Documentation ORA-01422 exception usually occurs when you attempt "select colname into varname" in http://nimishgarg.blogspot.com/2013/05/ora-01422-exact-fetch-returns-more-than.html pl/sql, but your select query returns more than one row. Example of ORA-01422: http://www.orafaq.com/forum/t/160843/ SQL> declare 2 v_empno number; 3 begin 4 select empno into v_empno from scott.emp where deptno=10; 5 end; 6 / declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 4 ORA-01422 may have occured because of incorrect logic implementation unhandled exception and can be resolved in any of the following ways - Correct the query to return exact one row - Implement the cursor if the query may return more than one rows - Bulk collect may be implemented to avoid ORA-01422 Solution of ORA-01422 using query correction: (lets say the logic was to find empid having max sal in deptno 10): declare v_empno trigger raised unhandled number; begin select empno into v_empno from scott.emp e where deptno = 10 and sal = ( select max(sal) from scott.emp e1 where e.deptno = e1.deptno ); end; / Solution of ORA-01422 using cursor: declare v_empno number; begin for c in (select empno into v_empno from scott.emp where deptno=10) loop v_empno := c.empno; end loop; end; / Solution of ORA-01422 using bulk collect: declare type mycollectiontype is table of number index by binary_integer; mycollection mycollectiontype; v_empno number; begin select empno bulk collect into mycollection from scott.emp where deptno=10; FOR indx IN 1 .. mycollection.COUNT LOOP v_empno := mycollection(indx); dbms_output.put_line(v_empno); END LOOP; end; / Related Links - Difference Between Cursor And Ref cursor - View results of refcursor out parameter - ORA-01403: no data found Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Labels: Ora-Codes 4 comments: AnonymousMarch 7, 2014 at 1:32 AMI am getting following error while entering Order for a KIT itemORA-01422: exact fetch returns more than requested number of rows in Package OE_Order_PVT Procedure Lines ReplyDeleteRepliesNimish GargMarch 7, 2014 at 11:35 AMjust check query at the line of error is returning a single
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 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. 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 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: 12341Registered: September