Oracle Error Ora-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 r
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: http://www.tek-tips.com/viewthread.cfm?qid=133225 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 column_name = variable_nameand the column_name and the http://blogs.orecreeksystems.com/2008/01/ora-1422-1427-2112-query-returns-two.html 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 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 ma
vid Type in oracle error text* *e.g.username (at oracle error ora-2112 least 3 characters long) ora amd aud dbv drg epc exp img imp kup lcd lfi lpx lrm lsx mod ncr nid nmp nnc nnf nnl nno npl nze o2f o2i o2u pcb pcc pcf pls qsm rman sql sql*loader tns vid Error type information..... Search tips..... Oracle Support Copyright © Ora-error 2004-2012, all rights reserved.
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 ALIASES AND AND & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT 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 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-00904 Error Message Learn the cause and how to resolve the ORA-00904 error message in Oracle. Description When you encounter an ORA-00904 error, the following error message will appear: ORA-00904: invalid identifier Cause You tried to execute a SQL statement that included an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement. Resolution The option(s) to resolve this Oracle error are: Option #1 Rewrite your SQL to include a valid column name. To be a valid column name the following criteria must be met: The column name must begin with a letter. The column name can not be longer than 30 characters. The column name must be made up of alphanumeric characters or the following special characters: $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks. The column name can not be a reserved word. Let's look at an example of how to resolve an ORA-00904 error. For example, if you ran the following SELECT statement, you would receive an ORA-00904 error: SQL> SELECT contact_id AS "c_id", last_name, first_name 2 FROM contacts 3 ORDER BY "cid"; ORDER BY "cid" * ERROR at line 3: ORA-00904: "cid": invalid identifier This error was created by aliasing a column, but then mistyping the alias later. In this example, we created the alias called "c_id" for the contact_id, but then called it as "cid" in the ORDER BY clause. To resolve this error, we can modify our SELECT statement to use the correct alias name in the ORDER BY clause as follows: SQL> SELECT contact_id AS "c_id", last_name, first_name 2 FROM contacts 3 ORDER BY "c_id"; 10 rows selected Share this page: Advertisement Back