1422 Oracle Error
Contents |
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 and reload this ora 1422 page. Please enter a title. You can not post a blank
Oracle Error 1422 Encountered
message. Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application oracle error codes 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 than requested number of rows
Ora-01422 Exception Handling
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: exact fetch returns more than requested number of the number specified in exact fetch is less than the rows returned. rows ORA-06512: at "BREW_SCHED.SPAC_ASSIGN_FERMS", line 959 Please help! Thanks much! 32311Views 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 loop 8 dbms_output.put_line('updated empno '||empno_tab(i)); 9 end loop; 10 end; 11 / updated empno 783
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Ora 01422 Unhandled Exception
ORA-01422: exact fetch
Exact Fetch Returns More Than Requested Number Of Rows Cursor
returns more than one requested number of rows tips Oracle Error Tips by Burleson Consulting Oracle docs note frm-40735 ora-01422 this about ORA-01422: 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 https://community.oracle.com/thread/364848 number of rows requested You may also be interested in this community troubleshooting from Oracle Forums: Question: I continue to receive this ORA-01422 message whenever I get to this stage in the process: 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: exact fetch returns more than requested number http://www.dba-oracle.com/sf_ora_01422_exact_fetch_returns_more_than_one_requested_number_of_rows.htm of rows ORA-06512: at "BREW_SCHED.SPAC_ASSIGN_FERMS", line 959 Answer: There are several options to resolve ORA-01422, which take some time to figure out which option is appropriate for you. Here are some things you may want to try: This is most likely signifying that you have updated multiple rows. Because of this, Oracle throws the ORA-01422 error because it is not able to return the variables in simple variables. To remedy this, try BULK COLLECT in a table such as the below: 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 loop 8 dbms_output.put_line('updated empno '||empno_tab(i)); 9 end loop; 10 end; 11 / updated empno 7839 updated empno 7698 updated empno 7782 updated empno 7566 updated empno 7788 updated empno 7902 updated empno 7369 updated empno 7499 updated empno 7521 updated empno 7654 updated empno 7844 updated empno 7876 updated empno 7900 updated empno 7934 PL/SQL procedure successfully completed. You may want to declare SPAC_ASSIGN_FERMS.xfer_seq because a table may be needed if multiple rows are being updated. If, from update, you are tr
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 Recruiting Managed Services Candidate https://www.tekstream.com/resources/ora-01422-exact-fetch-returns-more-than-requested-number-of-rows/ 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 http://nimishgarg.blogspot.com/2013/05/ora-01422-exact-fetch-returns-more-than.html 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 of Oracle errors can be broken down into two oracle error 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. 1422 oracle error 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 eith
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 pl/sql, but your select query returns more than one row. Example of ORA-01422: 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 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 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