How To Resolve Ora-01422 Error
Contents |
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java ora-01422 exception handling Clipart Techie Humor Advertisement Oracle Basics ALIASES AND AND & OR BETWEEN
Ora 01422 Unhandled Exception
COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL the number specified in exact fetch is less than the rows returned. IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign
Ora-01422 In Cursor For Loop
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 following error message will appear: ORA-01422: exact fetch returns more than requested ora-01422 select into 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 agree to have read and accepted our Terms of Service and Privacy Policy. We use advertisements to support this website and fund the deve
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 :
Trigger Raised Unhandled Exception Ora 01422
ORA-01422 exact fetch returns more than requested number of rows Cause: More rows
Frm-40735 Ora-01422
were returned from an exact fetch than specified. Action: Rewrite the query to return fewer rows or specify more frm-40735 post-query trigger raised unhandled exception ora-01422 rows in the exact fetch. 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 https://www.techonthenet.com/oracle/errors/ora01422.php 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 when NO_DATA_FOUND then error handling code when no record is found when TOO_MANY_ROWS then error handling code https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:981494932508 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.ORACLE.COM> @connect "/ as sysdba" sys@ORA920.US.ORACLE.COM> insert into dual values ( 'y' ); 1 row created. sys
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 http://stackoverflow.com/questions/21669419/ora-01422-exact-fetch-returns-more-than-requested-number-of-rows-ora-06512-at about Stack Overflow the company Business Learn more about hiring developers or posting ads http://stackoverflow.com/questions/22345040/dbms-mview-refresh-call-cause-ora-01422-error 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 4.7 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 unhandled exception 5 up vote 0 down vote favorite set 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,2373198173 asked Feb 10 '14 at 5:00 user3291451 14113 add a comment| 2 Answers 2 active oldest votes up vote 3 down trigger raised unhandled vote The error message is self explanatory. Your select statement returned more than 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,2373198173 add a comment| Your Answer draft saved draft discarded Sign up or
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 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up DBMS_MVIEW.REFRESH call cause ORA-01422 error up vote 1 down vote favorite 1 I performed full database import into existing database with identical structure. After this, call the DBMS_MVIEW.REFRESH procedure cause ORA-01422 error. For example: begin DBMS_MVIEW.REFRESH('SCHEMA_NAME.T_TOP_FRESH_COMMENTS'); end; Error: ORA-01422 exact fetch returns more than requested number of rows ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776 ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745 ORA-06512: at line 2 Select bellow returns single row: select * from dba_registered_mviews t where t.name = 'T_TOP_FRESH_COMMENTS' What do errors cause? How can I fix that? Thanks! UPD: Materialized view select doesn’t cause error. The same error repeated for others materialized views. Script for creating view is listed below: CREATE MATERIALIZED VIEW T_TOP_FRESH_COMMENTS REFRESH FORCE ON DEMAND AS SELECT ROWNUM AS order_comment, id_comment, date_refresh FROM ( SELECT c.id_comment, MAX (h.updated) date_refresh FROM t_comment_user c INNER JOIN t_comment_history h ON c.id_comment = h.id_comment WHERE c.published = 'T' AND h.published = 'T' and h.updated is not null GROUP BY c.id_comment ORDER BY date_refresh DESC) WHERE ROWNUM <= 10; UPD1: I ran completed refresh and recieved the same error: begin DBMS_MVIEW.REFRESH('SCHEMA_NAME.T_TOP_FRESH_COMMENTS','c'); end; I recreated materialized view with REFRESH COMPLETE and this didn't help too. I tried to purge view log. It is didn't help: begin dbms_mview.purge_log('SCHEMA_NAME.T_COMMENT_HISTORY',99999,'delete'); end; sql oracle plsql oracle11g share|improve this question edited Mar 13 '14 at 9:45 asked Mar 12 '14 at 7:46 Bazaleev Nikolay 417 2 There must be some sub query in the materialized view script (create materialize view script) which is returning multiple rows where only one is expected. Posting create materialized view code will be helpful. –San Mar 12 '14 at 8:08 Thanks for comment. I updated my question. –Bazaleev Nikolay Mar 12 '14 at 8:36 1 One of the reason ca