Ora-01422 Error Handling
Contents |
the flexible error trapping and error handling you can use in your PL/SQL programs. For more information on error-handling and exceptions in PL/SQL, see "PL/SQL Error Handling" in Oracle Database PL/SQL Language Reference See the end ora-01422 exact fetch returns of this chapter for TimesTen-specific considerations. The following topics are covered: Understanding exceptions Trapping ora 01422 unhandled exception exceptions Showing errors in ttIsql Differences in TimesTen: exception handing and error behavior Understanding exceptions This section provides an overview of exceptions ora-01422 in cursor for loop in PL/SQL programming, covering the following topics: About exceptions Exception types About exceptions An exception is a PL/SQL error that is raised during program execution, either implicitly by TimesTen or explicitly by your program. Handle
Ora-01422 Select Into
an exception by trapping it with a handler or propagating it to the calling environment. For example, if your SELECT statement returns more than one row, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message. (ORA messages, originally defined for Oracle Database, are similarly implemented by TimesTen.) Command> DECLARE > v_lname VARCHAR2 (15); > BEGIN the number specified in exact fetch is less than the rows returned > SELECT last_name INTO v_lname > FROM employees > WHERE first_name = 'John'; > DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname); > END; > / 8507: ORA-01422: exact fetch returns more than requested number of rows 8507: ORA-06512: at line 4 The command failed. You can handle such exceptions in your PL/SQL block so that your program completes successfully. For example: Command> DECLARE > v_lname VARCHAR2 (15); > BEGIN > SELECT last_name INTO v_lname > FROM employees > WHERE first_name = 'John'; > DBMS_OUTPUT.PUT_LINE ('Last name is :' || v_lname); > EXCEPTION > WHEN TOO_MANY_ROWS THEN > DBMS_OUTPUT.PUT_LINE (' Your SELECT statement retrieved multiple > rows. Consider using a cursor.'); > END; > / Your SELECT statement retrieved multiple rows. Consider using a cursor. PL/SQL procedure successfully completed. Exception types There are three types of exceptions: Predefined exceptions are error conditions that are defined by PL/SQL. Non-predefined exceptions include any standard TimesTen errors. User-defined exceptions are exceptions specific to your application. In TimesTen, these three types of exceptions are used in the same way as in Oracle. Exception Description How to handle Predefined TimesTen error One of approximately 20 errors that occur most often in PL/SQL code. You are not required to declare these exceptions. They are predefined by TimesTen.
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. oracle too_many_rows Please enter a title. You can not post a blank message.
Trigger Raised Unhandled Exception Ora 01422
Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and
Frm-40735 Ora-01422
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 475922 Feb https://docs.oracle.com/cd/E18283_01/timesten.112/e13076/exceptions.htm 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 rows ORA-06512: at https://community.oracle.com/thread/364848 "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 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 upda
Library mySQL Code Library PHP Code Library JavaScript Code Library Oracle Terms & Definitions Oracle Error exact fetch Codes PSOUG Community Blogs Oracle Jobs Board PSOUG Forum Oracle User Group Directory Free Oracle Magazines Online Learning Center PSOUG Presentations Advanced Code Search News and Events Sponsors ora-01422 error handling Page Submit Code Contact Us Looking for the original pages? (formerly called "Morgan's Library") You can find them here. Term: TOO_MANY_ROWS Definition: The TOO_MANY_ROWS Exception (ORA-01422) occurs when a SELECT INTO statement returns more than one row. Related Links: TOO_MANY_ROWS Exception - ORA-01422 Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us 87 users online © 2009 psoug.org PSOUG LOGIN Username: Password: Forgot your password?