ORA 1422, 1427, 2112 (query returns two 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 column_name = variable_name

and 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 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 don't care what the account value is, then this is probably the best approach. If you have to do a full table scan, this is less desirable. If you do need the value, then you will have to do a second query to get the value, and this simply does not work.

The best approach, if you need a value from a query, and you don't care what the value is, is to add

AND ROWNUM = 1

to your WHERE clause. If there are multiple values, this returns the first one found (and does it as quickly as possible). If there is one value, you get it. And, if there are no values, it raises the NO_DATA_FOUND exception, which is what your code was already expecting (you were prepared for this exception, were you not?) In particular, this avoids any sort which would be produced if the DISTINCT operator were used instead, and it works if there are different values present.

One trick which is more trouble than it is worth to use the MAX or MIN function to make all values
the same. This may introduce a sort, and will always return a row, so you will have to modify your
error checking.

One Interesting Table!

I have some thoughts on a number of topics, and this table will serve as a good example for all of them. Let's first start with some DDL:

CREATE TABLE PROSPECTS (
PROSPECT_ID NUMBER PRIMARY KEY,
FIRST_NAME VARCHAR2(25),
LAST_NAME VARCHAR2(25),
SSN VARCHAR2(9),
STREET VARCHAR2(30),
CITY VARCHAR2(30),
STATE_CD VARCHAR2(2),
PHONE VARCHAR2(10)
)
/

And let's fill it with some data:

PROSPECT   FIRST      LAST        SSN       STREET         CITY     STATE      PHONE        
  ID       NAME       NAME                                                                                                                                                 CD
-----   ----------  ----------- --------- -------------  ---------  ----   ----------
  1     Judi        Singer      135792468 6216 Kevin     Brighton     MI   8102222222
  2     Mega        Hertz       148273423 810 Main       Howell       MI   5174234243
  3     Judy        Singer      987131312 6216 Kevin Dr  Brighton     MI   8102222222
  4     Judi        Singer      145792468 6216 Kevin     Brighton     MI   8102222222
  5     Mega        Hertz       148273423 810 Main       Howell       MI   5174234243
  6     Junior      Hertz                 810 Main       Howell       MI   5174234243
  7     Mighty      Moose       184726423 333 Third St.  Brighton     MI   8102223423  

Before I go any further, let me add that this is not my idea of a great design. In any sort of a decent prospect database, this table should be split off into at least two separate tables, and a (large) number of constraints added. This will become clear in the following discussion

(And yes, although some of the names and addresses are real, the Social Security Numbers and telephone numbers have been changed to protect the innocent).

What we are trying to have is a table which holds potential customers in some sort of marketing database. And we can start out with these questions (which relate to the whole issue of 1NF and surrogate keys): How many different prospects do we have in the database? What are the candidate keys in this table? Is it in 1NF? And we will close with these questions (which relate to a different issue): How many people is our system aware of? Suppose I added the business rule that any children in the system had to have a parent in the system?

To Be Continued:

ORA-12705 "invalid or unknown NLS parameter value specified"

The "Official" explanation is that the NLS parameter value specified in an alter session statement is not valid or not implemented. Except that this is not quite the entire story. If we put some nonsense in an alter session statement, Oracle is able to figure that out:


SQL> alter session set dodah = hohah;
alter session set dodah = hohah
*
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

O.K., let's try a bad option to a good parameter:

SQL> alter session set nls_date_format = dodah;
ERROR:
ORA-01821: date format not recognized

What we need is a valid NLS parameter (such as nls_date_format, we were on the right track there) and pass it a bad option, but pick an NLS parameter which doesn't have it's own editing available (which appears to be any of them except the ones which refer to dates or time) :

SQL> alter session set nls_territory = dodah;
ERROR:
ORA-12705: invalid or unknown NLS parameter value specified

This describes how the ORA-12705 can arise in an existing session. Can one get this error during the logon process? The answer is, yes. Just set the NLS_LANG environment variable to an invalid value, and try to connect:

$ export NLS_LANG=dodah
$ echo $NLS_LANG
dodah
$ sqlplus psinger1@oradev

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Oct 24 07:48:45 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:
ERROR:
ORA-12705: invalid or unknown NLS parameter value specified

and NLS_LANG appears to be the only NLS parameter which can be set as an environment variable. So, in summary, if you get a 12705 and you haven't just made an obvious mistake with an alter session statement, check your NLS_LANG setting.