Oracle Error 2112
FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Oracle: Oracle release - 9i and earlier Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesOracle: Oracle release - 9i and earlier Forum SQL error code -2112 thread759-133225 Forum Search FAQs Links MVPs SQL error code -2112 SQL error code -2112 Guest (visitor) (OP) 10 Sep 01 15:32 We're porting our code from a Unix platform to Windows '98.On the PC we're gettinga SQL error code of -2112 on one of our select statements.This error does notoccur in Unix, which runs fine.We are running Oracle 8.1.5 but our referencematerial (version 6.0)does not contain the definition of this error code.Does anyone know what error -2112 indicates??? RE: SQL error code -2112 lfoata (IS/IT--Management) 10 Sep 01 15:51 SQL-02112 SELECT..INTO returns too many rows Cause: A SELECT...INTO statement returned more rows than can be stored in the host variable provided. Not too familiar with windows, but it seems that you are hitting a max for a variable that Unix does not have. RE: SQL error code -2112 Guest (visitor) (OP) 10 Sep 01 18:20 Thank you for your reply.Knowing that error code alerted us to the fact that our code was no declaring a cursor.This is really old code, and it never showed up as an error onUnix, but we think that's our problem on the PC.We will declare a cursor and fetch so that we are guaranteed one row at a time.Thanks again.You've been a tremendous help, you have no idea. Red Fla
many rows) Three error codes, which all mean the same thing: a query which syntactically must return a single row returns multiple rows.In a 3GL program, a SELECT ... INTO ... must return just one row. If a PL/SQL routine returns multiple rows, a 1422 is returned. The 2112 is returned from a precompilier program. The 1427 is returned when a subquery must return one row, and it does not. For example: SELECT .... WHERE column_name = (some subquery).This error is easy to verify: just run the query. If it returns more than one row there is nothing more to investigate. One situation where it may be tricky to identfy is a PL/SQL program with this construct: SELECT ... INTO ... FROM ... WHERE http://www.tek-tips.com/viewthread.cfm?qid=133225 column_name = variable_nameand the column_name and the variable_name are identical. For example: DECLARE this_column number; BEGIN SELECT COUNT(*) INTO KOUNT FROM SOME_TABLE WHERE this_column = this_column;This is ambiguous: does the right hand side represent SOME_TABLE.THIS_COLUMN,or the variable? PL/SQL takes it as the column name, and all rows get returned.The 1427 can also arise in an UPDATE statement:UPDATE .... SET column_name = (some subquery)If the subquery returns two or more rows, a 1427 results.What to do:Knowing what is wrong is easy; fixing is another matter. In http://blogs.orecreeksystems.com/2008/01/ora-1422-1427-2112-query-returns-two.html theory, you have misstated your query; just add additional predicates to make it return a single row. Problems arise when there is bad data (the query should return just one row, but it doesn't) or when you expect multiple rows, but really don't care. The first case is simple: just delete the duplicate data and add enough constraints to the application so that it can't happen again. Where the designer decided to have the application enforce constraints (because it is too much work for the poor database to enforce uniqueness) this may be difficult, and things may devolve into the second case.In the second case, you really can get multiple rows back; you just thought (possibly because of incomplete test cases) that you would just get one row back. As an example, you are given an e-mail address, and need to decide whether to go to the new account screen, or bring up an existing account. Normally, there is just one account, but a few people may have multiple accounts. If you need to know every account you have no choice but to replace the singleton select with a cursor. But, you may only need to know if there are multiple accounts; you do one thing if there are no accounts, and something else if there is one or more accounts, and you do the same thing regardless of the value of the accounts.The easiest trick is the old SELECT count (*). If this can be satisfied by an index, and you do
in random -2112 errors [message #429431] Tue, 03 November 2009 15:04 jhughe90 Messages: 3Registered: November 2009 Junior Member Pro*C Query: EXEC SQL SELECT TO_CHAR(MAX(B.TIMESTAMP),:o_ts_format) INTO :o_max_action_entry_ts:o_max_action_entry_ts_ind FROM SCHEMA.TABLE1 A, SCHEMA.TABLE2 B WHERE A.ID = :o_input_id AND A.OTHER_FIELD = my_fixed_value http://www.orafaq.com/forum/t/151860/ AND A.ID = B.ID; We're getting occasional random -2112 (Too many rows) from this query. Our process then switches to a backup mode that validates off a flat file. We then requeue the transaction to be reprocessed every https://community.oracle.com/thread/1080380 half hour so that a record can be stored for auditing purposes. When the job reruns later on, it again fails anywhere from 2-4 times with a 2112 before finally working with no data changes being made. Without oracle error the MAX, some IDs do return multiple rows. With the MAX I see no reason why it would return multiple rows. I cannot duplicate the results using PL/SQL Developer query windows either. The data in these tables is fairly static and does not change often. Most of the B.TIMESTAMP values are years old. The only pattern I see is that for the data conditions that are failing, B.TIMESTAMP is the same value when multiple rows match oracle error 2112 before the MAX call. MAX should still eliminate the possibility of a 2112 though. Is there a bug/patch out there that we need? Report message to a moderator Re: MAX() resulting in random -2112 errors [message #429434 is a reply to message #429431] Tue, 03 November 2009 15:35 Littlefoot Messages: 20850Registered: June 2005 Location: Croatia, Europe Senior MemberAccount Moderator Is there, by any chance, a GROUP BY clause you didn't mention? Report message to a moderator Re: MAX() resulting in random -2112 errors [message #429435 is a reply to message #429434] Tue, 03 November 2009 15:52 jhughe90 Messages: 3Registered: November 2009 Junior Member No that is the full query. Report message to a moderator Re: MAX() resulting in random -2112 errors [message #429436 is a reply to message #429435] Tue, 03 November 2009 16:00 ThomasG Messages: 3186Registered: April 2005 Location: Heilbronn, Germany Senior Member Can you please confirm that it is really an ORA-02112 error, and not a PCC-02122 Error, since you didn't actually mention that in the post, and a PCC-02122 would be something completely different? Report message to a moderator Re: MAX() resulting in random -2112 errors [message #429439 is a reply to message #429436] Tue, 03 November 2009 16:36 jhughe90 Messages: 3Registered: November 2009 Junior Member Definitely is ORA 2112. 10/29/2009-11:49:10.345[16750]auth: orac
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. 제목을 입력하세요. 빈 메시지를 게시할 수 없습니다. 메시지를 입력한 후 다시 시도하세요. Applications (Korean)의 다른 토론 전체Oracle CommunityArchived ForumsKoreanTechnical Bulletin (Korean)새로운 Technical BulletinApplications (Korean) 이 토론은 보관되었습니다. 0 회신 Esyu-Oracle 님이 May 27, 2010 7:49 AM에 마지막 답글 ESYU: Memory-Based snapshot에 App-Fnd-01564, Mrllis_load_item_sourcing Sql-0 Esyu-Oracle May 27, 2010 7:49 AM Purpose -------- Materials Requirement Planning - Version: 11.5.10 to 11.5.10.2 This problem can occur on any platform. Standard MRP plan을 실행시 MRCNSP Memory-based Snapshot process에서 다음과 같은 error가 발생하는 경우가 있다. APP-FND-01564: ORACLE error 2112 in mrllis_load_item_sourcing Cause: mrllis_load_item_sourcing failed due to SQL-02112: SELECT..INTO returns too many rows. The SQL statement being executed at the time of the error was: select NVL(purchasing_unit_of_measure,'-1') into :b0 from po_approved and was executed from the file 5203827/mrp/lib/mrlnex.pc. Statement with the error: select NVL(purchasing_unit_of_measure,'-1') into :b0 from po_approved 5203827/mrp/lib/mrlnex.pc 이 issue는 아래 steps에 따라 재현이 가능하다: 1. Material Planner 2. Confirgure MRP plan 3. Launch MRP 이와 같은 error 발생시 해결방법을 알아본다. Cause ------ Item_id, vendor_id, vendor_site_id, asl_status_id = 3 (approved)에 대해 po_approved_supplier_list에 여러건의 rows가 존재하기 때문이다. -- script to check -- select ... from po_approved_supplier_list t1 where ASL_STATUS_ID=3 and (item_id,ven