Ora-01422 Oracle Error
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: On Randomness, Syntax, and Mutation
Ora-01422 Exception Handling
Latest Followup You Asked Hi Tom I'm trying to execute SQL statment but ora 01422 unhandled exception the result is : ORA-01422 exact fetch returns more than requested number of rows Cause: More rows were returned from
The Number Specified In Exact Fetch Is Less Than The Rows Returned
an exact fetch than specified. Action: Rewrite the query to return fewer rows or specify more rows in the exact fetch. So How can I handle this proplem ? Thank you and ora-01422 in cursor for loop 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 one record, you would code: begin select * into .... from t where .... process.... exception ora-01422 select into 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. 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.OR
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on
Trigger Raised Unhandled Exception Ora 01422
and reload this page. Please enter a title. You can not oracle too many rows post a blank message. Please type your message and try again. More discussions in PL/SQL and
Frm-40735 Post-query Trigger Raised Unhandled Exception Ora-01422
SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 10 Replies Latest reply on Feb 15, 2006 6:39 PM by 12826 ORA-01422: exact fetch returns more https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:981494932508 than requested number of rows 475922 Feb 15, 2006 3:48 PM Good Morning Everyone! When my proc is getting to this startment its giving me the error below: UPDATE TT_FERMS SET assigned_system_id = 2 WHERE ferm_bank = 3 RETURNING ( SPAC_ASSIGN_FERMS.xfer_seq + 1 ), SPAC_ASSIGN_FERMS.xfer_seq + 1 INTO SPAC_ASSIGN_FERMS.xfer_seq, SPAC_ASSIGN_FERMS.xfer_seq; ERROR:ERROR at line 1: ORA-01422: https://community.oracle.com/thread/364848 exact fetch returns more than requested number of rows ORA-06512: at "BREW_SCHED.SPAC_ASSIGN_FERMS", line 959 Please help! Thanks much! 33863Views Tags: none (add) This content has been marked as final. Show 10 replies 1. Re: ORA-01422: exact fetch returns more than requested number of rows 12826 Feb 15, 2006 4:17 PM (in response to 475922) What are the datatypes for the RETURNING columns; can they accept more than one row/record Like Show 0 Likes(0) Actions 2. Re: ORA-01422: exact fetch returns more than requested number of rows Tony Andrews Feb 15, 2006 4:17 PM (in response to 475922) It means you updated more than one row, so Oracle can't return the values into simple variables. You can use BULK COLLECT and tables like this: SQL> declare 2 type num_tab is table of number; 3 empno_tab num_tab; 4 begin 5 update emp set ename = ename 6 returning empno bulk collect into empno_tab; 7 for i in 1..empno_tab.count
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 https://www.tekstream.com/resources/ora-01422-exact-fetch-returns-more-than-requested-number-of-rows/ WebCenter Sites Sourcing Staffing & Recruiting Recruiting Managed Services Candidate Registration Technical Focus Client Opportunities Support Solutions Training Legacy to Oracle WebCenter Oracle Documents Cloud Service Next Generation AP Automation & 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 unhandled exception returns 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 trigger raised unhandled 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 triggere