How To Solve Ora-01422 Error
Contents |
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII ora-01422 exception handling Table Linux UNIX Java Clipart Techie Humor Advertisement Oracle Basics ALIASES
Ora 01422 Unhandled Exception
AND AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT
The Number Specified In Exact Fetch Is Less Than The Rows Returned.
INSERT ALL INTERSECT IS NOT NULL IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced
Ora-01422 In Cursor For Loop
Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle 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 ora-01422 select into following error message will appear: ORA-01422: exact fetch returns more than requested number of rows Cause You 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 agre
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 result is : ORA-01422 exact fetch returns more than requested number of rows trigger raised unhandled exception ora 01422 Cause: More rows were returned from an exact fetch than specified. Action: Rewrite the query frm-40735 ora-01422 to return fewer rows or specify more rows in the exact fetch. So How can I handle this proplem ? Thank you and frm-40735 post-query trigger raised unhandled exception ora-01422 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 https://www.techonthenet.com/oracle/errors/ora01422.php 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 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 .. https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:981494932508 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.ORACLE.COM> @connect "/ as sysdba" sys@ORA920.US.ORACLE.COM> insert into dual values ( 'y' ); 1 row created. sys@ORA920.US.ORACLE.COM> select * from dual; D - X sys@ORA920.US.ORACLE.COM> select count(*) from dual; COUNT(*) ---------- 2 sys@ORA920.US.ORACLE.COM> dual is magic, make sure to COUNT(*) it, not just select * from it sys@ORA920.US.ORACLE.COM> declare x number; begin select my_seq.nextval into x from dual; end; 2 / declare x number; begin select my_seq.nextval into x from dual; end; * ERROR at line 1: ORA-01422: exa
here for a quick overview of the site Help Center Detailed http://stackoverflow.com/questions/19779483/pl-sql-ora-01422-exact-fetch-returns-more-than-requested-number-of-rows answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the http://www.orafaq.com/forum/t/160843/ company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss unhandled exception Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up PL/SQL ORA-01422: exact fetch returns more than requested number of rows up vote 11 down vote favorite 5 I get trigger raised unhandled keep getting this error I can't figure out what is wrong. "DECLARE * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 11" Here is my code. DECLARE rec_EMPID EMPLOYEE.EMPID%TYPE; rec_ENAME EMPLOYEE.ENAME%TYPE; rec_JOB EMPLOYEE.DESIGNATION%TYPE; rec_SAL EMPLOYEE.SALARY%TYPE; rec_DEP DEPARTMENT.DEPT_NAME%TYPE; BEGIN SELECT EMPLOYEE.EMPID, EMPLOYEE.ENAME, EMPLOYEE.DESIGNATION, EMPLOYEE.SALARY, DEPARTMENT.DEPT_NAME INTO rec_EMPID, rec_ENAME, rec_JOB, rec_SAL, rec_DEP FROM EMPLOYEE, DEPARTMENT WHERE EMPLOYEE.SALARY > 3000; DBMS_OUTPUT.PUT_LINE ('Employee Nnumber: ' || rec_EMPID); DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------'); DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || rec_ENAME); DBMS_OUTPUT.PUT_LINE ('---------------------------------------------------'); DBMS_OUTPUT.PUT_LINE ('Employee Designation: ' || rec_JOB); DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------'); DBMS_OUTPUT.PUT_LINE ('Employee Salary: ' || rec_SAL); DBMS_OUTPUT.PUT_LINE ('----------------------------------------------------'); DBMS_OUTPUT.PUT_LINE ('Employee Department: ' || rec_DEP); END; / oracle plsql oracle11g sqlplus share|improve this question asked Nov 4 '13 at 23:46 Hiram 61114 add a comment| 1 Answer 1 active oldest votes up vote 20 down vote accepted A SELECT INTO statement will throw an error
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: 12334Registered: 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