Oracle Error Code 1422

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 page. Please enter a

Ora-01422 Exception Handling

title. You can not post a blank message. Please type your message the number specified in exact fetch is less than the rows returned and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived ora 01422 unhandled exception 10 Replies Latest reply on Feb 15, 2006 6:39 PM by 12826 ORA-01422: exact fetch returns more than requested number of rows 475922 Feb 15, 2006 3:48 PM Good Morning Everyone!

Exact Fetch Returns More Than Requested Number Of Rows Cursor

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 rows ORA-06512: at "BREW_SCHED.SPAC_ASSIGN_FERMS", line 959 Please help! Thanks much! 33870Views Tags: none

Ora-01422 In Cursor For Loop

(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 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

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 Select Into

tips Oracle Error Tips by Burleson Consulting Oracle docs note this about ORA-01422: ORA-01422: exact fetch returns more trigger raised unhandled exception ora 01422 than requested number of rows Cause: The number specified in exact fetch is less than the rows returned. Action: Rewrite the query or change number of rows requested You may also be interested in this community https://community.oracle.com/thread/364848 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 of rows ORA-06512: at "BREW_SCHED.SPAC_ASSIGN_FERMS", line 959 Answer: There are several options to resolve ORA-01422, which take http://www.dba-oracle.com/sf_ora_01422_exact_fetch_returns_more_than_one_requested_number_of_rows.htm 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 trying to return two values, you should consider defining my_tab Try using a table of a record with two fields, and the record with two fields itself. (NOTE: as of 9.2.0.6, you are not able to use

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 this site About Us Learn more about Stack Overflow the company http://stackoverflow.com/questions/21669419/ora-01422-exact-fetch-returns-more-than-requested-number-of-rows-ora-06512-at Business Learn more about hiring developers or posting ads with us Stack Overflow Questions http://stackoverflow.com/questions/18477741/pl-sql-group-by-ora-01422-exact-fetch-returns-more-than-requested-number-of-r Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 5 up vote 0 down vote favorite set exact fetch serveroutput on; set verify off; set autoprint on; variable b_employee_id employees.employee_id%type; declare v_last_name employees.last_name%type; v_emp_id employees.employee_id%type; begin select employee_id into :b_employee_id from employees where last_name='&v_last_name'; end; / oracle-sqldeveloper procedural-programming share|improve this question edited Feb 10 '14 at 5:02 Bishan 5,2723198173 asked Feb 10 '14 at 5:00 user3291451 14113 add a comment| 2 Answers 2 active oldest votes up vote 3 down vote The error message is self explanatory. Your select statement returned more than oracle error code one row. When you use the INTO clause the select cannot return more than one row. From the documentation: By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of an aggregate function, such as COUNT(*) or AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition. share|improve this answer answered Feb 10 '14 at 5:02 wdosanjos 3,9701618 add a comment| up vote 0 down vote exact fetch returns more than requested number of rows This means, there are more than one entry in the database for given last_name. If you want, you can get max employee_id for given last_name as below. select max(employee_id) into :b_employee_id from employees where last_name='&v_last_name'; share|improve this answer answered Feb 10 '14 at 5:08 Bishan 5,2723198173 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Email Post as a gue

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 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 x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up PL/SQL Group By - ORA-01422: exact fetch returns more than requested number of rows up vote 2 down vote favorite 1 I am writing the following query that I want to display car registration, car group name, model name, cost and the number of bookings for each car. I have to use an explicit cursor and I have to use an implicit cursor to calculate the number of bookings that belong to each car. My query is as follows: SET SERVEROUTPUT ON FORMAT WRAP SIZE 12000 Declare v_count number; cursor carcur IS SELECT * FROM i_car; v_car carcur%ROWTYPE; Begin Select COUNT (registration) INTO v_count from i_booking group by registration; FOR v_car IN carcur LOOP DBMS_OUTPUT.PUT_LINE('Registration:'|| ' '|| v_car.registration); DBMS_OUTPUT.PUT_LINE('Car Group:'|| ' ' ||v_car.car_group_name); DBMS_OUTPUT.PUT_LINE('Model Name:'|| ' '||v_car.model_name); DBMS_OUTPUT.PUT_LINE('Cost:'|| ' '||v_car.cost); DBMS_OUTPUT.PUT_LINE('Total Bookings:'|| ' '||v_count); DBMS_OUTPUT.NEW_LINE; END LOOP; End; The output I am getting is as follows: Declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 7 I am sure it has something to do with the return values being put into the variable, but I have no idea how to rectify this. Any advice would be greatly appreciated. Many thanks. sql oracle plsql oracle11g oracle10g share|improve this question asked Aug 28 '13 at 0:53 Splunk 1092514 add a comment| 4 Answers 4 active oldest votes up vote 2 down vote The error you are facing is because you are trying to assign multiple values to a single valued variable. Your query is returning multiple values primarily because you are using a group by. What a group by does is, in your case, finds the total count of values in column registration for every distinct value in that