Oracle Too Many Rows Error
Contents |
Kyte � Last updated: September 09, 2013 - 10:57 am UTC Category: Developer � Version: 2000 Whilst you are here, check out some content from the AskTom team: Why You Should ALWAYS Use Packages Latest Followup You Asked Hi Tom I'm trying to execute SQL statment but the result is too many rows exception in oracle : ORA-01422 exact fetch returns more than requested number of rows Cause: More rows were returned from
Too Many Rows Exception In Sql Server
an exact fetch than specified. Action: Rewrite the query to return fewer rows or specify more rows in the exact fetch. So How can I
Ora-01422 Error
handle this proplem ? Thank you and 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
Too Many Values Exception In Oracle
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 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 ) others exception in oracle 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: exact fetch returns more than requested number of rows ORA-06512: at line 1 will happen when dual has more then 1 row September 25, 2002 - 4:
Error Handling" in Oracle Database PL/SQL Language Reference. See the end of this chapter for TimesTen-specific considerations. The following topics are covered: Understanding raise too_many_rows exceptions Trapping exceptions Showing errors in ttIsql Differences in TimesTen: exception handing oracle exceptions and error behavior Understanding exceptions This section provides an overview of exceptions in PL/SQL programming, covering the following functions for error trapping are contained in which section of a pl/sql block 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 an https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:981494932508 exception by trapping it with a handler or propagating it to the calling environment. For example, if your SELECT statement returns multiple rows, 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 https://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm > 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); > 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 s
FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL PLUS Session EnvironmentSystem Tables Data DictionarySystem PackagesObject OrientedXMLLarge ObjectsTransactionUser PrivilegeTOO_MANY_ROWS Exception : Handle Exception«PL SQL Programming«Oracle PL/SQL TutorialOracle http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/Toomanyrowsexception.htm PL/SQL TutorialPL SQL ProgrammingHandle ExceptionSQL> SQL> SQL> SQL> create table https://www.techonthenet.com/oracle/exceptions/named_system.php employee ( 2 id number, 3 employee_type_id number, 4 external_id varchar2(30), 5 first_name varchar2(30), 6 middle_name varchar2(30), 7 last_name varchar2(30), 8 name varchar2(100), 9 birth_date date , 10 gender_id number ); Table created. SQL> SQL> SQL> create table gender ( 2 too many id number, 3 code varchar2(30), 4 description varchar2(80), 5 active_date date default SYSDATE not null, 6 inactive_date date ); Table created. SQL> SQL> SQL> SQL> insert into gender ( id, code, description ) values ( 1, 'F', 'Female' ); 1 row created. SQL> insert into gender ( id, code, description ) values too many rows ( 2, 'M', 'Male' ); 1 row created. SQL> insert into gender ( id, code, description ) values ( 3, 'U', 'Unknown' ); 1 row created. SQL> SQL> SQL> SQL> SQL> set serveroutput on size 1000000; SQL> SQL> declare 2 3 d_birth_date employee.birth_date%TYPE; 4 n_gender_id employee.gender_id%TYPE; 5 n_selected number := -1; 6 n_id employee.id%TYPE; 7 v_first_name employee.first_name%TYPE; 8 v_last_name employee.last_name%TYPE; 9 v_middle_name employee.middle_name%TYPE; 10 v_name employee.name%TYPE; 11 12 begin 13 v_first_name := 'JOHN'; 14 v_middle_name := 'J.'; 15 v_last_name := 'DOE'; 16 v_name := rtrim(v_last_name||', '||v_first_name||' '||v_middle_name); 17 d_birth_date := to_date('19800101', 'YYYYMMDD'); 18 19 begin 20 select id into n_gender_id from gender where code = 'M'; 21 exception 22 when OTHERS then 23 raise_application_error(-20001, SQLERRM||' on select gender'); 24 end; 25 26 begin 27 select id into n_id from employee; 28 n_selected := sql%rowcount; 29 exception 30 when NO_DATA_FOUND then 31 n_selected := sql%rowcount; 32 DBMS_OUTPUT.PUT_LINE('Caught raised exception NO_DATA_FOUND'); 33 when TOO_MANY_ROWS the
Server MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie Humor Advertisement Oracle Basics Oracle Advanced Oracle Cursors Oracle Exception Handling Named Programmer-Defined Exception Named System Exception WHEN OTHERS Clause SQLCODE SQLERRM 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 NEXT: Named Programmer-Defined Exception Oracle / PLSQL: Named System Exceptions This Oracle tutorial explains how to use Named System Exceptions in Oracle/PLSQL with syntax and examples. What is a named system exception in Oracle? Named system exceptions are exceptions that have been given names by PL/SQL. They are named in the STANDARD package in PL/SQL and do not need to be defined by the programmer. Oracle has a standard set of exceptions already named as follows: Oracle Exception Name Oracle Error Explanation DUP_VAL_ON_INDEX ORA-00001 You tried to execute an INSERT or UPDATE statement that has created a duplicate value in a field restricted by a unique index. TIMEOUT_ON_RESOURCE ORA-00051 You were waiting for a resource and you timed out. TRANSACTION_BACKED_OUT ORA-00061 The remote portion of a transaction has rolled back. INVALID_CURSOR ORA-01001 You tried to reference a cursor that does not yet exist. This may have happened because you've executed a FETCH cursor or CLOSE cursor before OPENing the cursor. NOT_LOGGED_ON ORA-01012 You tried to execute a call to Oracle before logging in. LOGIN_DENIED ORA-01017 You tried to log into Oracle with an invalid username/password combination. NO_DATA_FOUND ORA-01403 You tried one of the following: You executed a SELECT INTO statement and no rows were returned. You referenced an uninitialized row in a table. You read past the end of file with the UTL_FILE package. TOO_MANY_ROWS ORA-01422 You tried to execute a SELECT INTO statement and more than one row was returned. ZERO_DIVIDE ORA-01476 You tried to divide a number by zero. INVALID_NUMBER ORA-01722 You tried to execute a SQL statement that tried to convert a string to a number, but it was unsuccessful. STORAGE_ERROR ORA-06500 You ran out of memory or memory was corrupted. PROGRAM_ERROR ORA-06501 This is a generic "Contact Oracle support" message because an internal problem was encountered. VALUE_ERROR ORA-06502 You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or cha