Home » Oracle » Oracle Database » ORA-01427: single-row subquery returns more than one row

ORA-01427: single-row subquery returns more than one row

The error ORA-01427 is raised, when a sub-query returns more than one row to an Equality or in-Equality operator.

ORA-01427

The reason being there are restrictions on the legal comparison operators.The single row subquery operator are

SymbolMeaning
=equal
>greater than
>=greater than or equal
<less than
<=less than or equal
<>not equal
!=not equal

So whenever you are using these single row subquery operator, if you return more than one row,it will start throwing the error

How to resolve the error ORA-01427

(1) either we change the query to use Multiple -row Subquery Operators 

Multiple-row Sub-query Operators
Symbol Meaning
IN equal to any member in a list
NOT IN not equal to any member in a list
ANY returns rows that match any value on a list
ALL returns rows that match all the values in a list

(2) We look at the query and find out what is happening, why it is returning more rows than expected. Like the way we resolved this error for Concurrent Manager issue. It is generally advisable  use the sub-query based on primary key in order to get consistent result,

Lets us take a look at few example

Few Examples

(1) While starting the concurrent Manager in the EBS environment, we were getting below error

Cause: cleanup_node failed due to ORA-01427: single-row subquery returns more than one row
ORA-06512: at "APPS.FND_CP_FNDSM", line 29
ORA-06512: at line 1.

This error happens when we have more than two FNDSM of FNDIM defined for the node.So we selected the node name of all the concurrent Manager in the system

See also  Oracle External tables

Solution
We can resolve the solution manually through below update

SQL> select CONCURRENT_QUEUE_NAME,NODE_NAME from fnd_concurrent_queues group by CONCURRENT_QUEUE_NAME,NODE_NAME;
CONCURRENT_QUEUE_NAMENODE_NAME
FNDSCHSUNTEST
PODAMGRSUNTEST
FTE_TXN_MANAGERSUNTEST
FNDSM_20151_SUNTESTSUNTEST
FNDICMSUNTEST
FNDTMTSTSUNTEST
STANDARDSUNTEST
FNDIM_SUNTESTSUNTEST
FNDIM_20153_SUNTESTSUNTEST
WFMLRSVCSUNTEST
XDP_Q_FE_READY_SVCSUNTEST
WMSTAMGRSUNTEST
INVMGRSUNTEST
AMSDMINSUNTEST
FNDSM_SUNTESTSUNTEST

We can see there are two FNDSM and FNDIM for the node SUNTEST.


FNDSM_20151_SUNTEST SUNTEST
FNDIM_SUNTEST SUNTEST
FNDIM_20153_SUNTEST SUNTEST
FNDSM_SUNTEST SUNTEST

Lets do the update now

SQL> update fnd_concurrent_queues set NODE_NAME=null where CONCURRENT_QUEUE_NAME='FNDSM_20151_SUNTEST';
1 row updated.
SQL> update fnd_concurrent_queues set NODE_NAME=null where CONCURRENT_QUEUE_NAME='FNDIM_20153_SUNTEST';
1 row updated.
SQL> commit;
Commit complete.
SQL> select CONCURRENT_QUEUE_NAME,NODE_NAME from fnd_concurrent_queues group by CONCURRENT_QUEUE_NAME,NODE_NAME;
CONCURRENT_QUEUE_NAMENODE_NAME
FNDSCHSUNTEST
PODAMGRSUNTEST
FTE_TXN_MANAGERSUNTEST
FNDSM_20151_SUNTEST 
FNDICMSUNTEST
FNDTMTSTSUNTEST
STANDARDSUNTEST
FNDIM_SUNTESTSUNTEST
FNDIM_20153_SUNTEST 
WFMLRSVCSUNTEST
XDP_Q_FE_READY_SVCSUNTEST
WMSTAMGRSUNTEST
INVMGRSUNTEST
AMSDMINSUNTEST
FNDSM_SUNTESTSUNTEST

Or we can run conc clean and run autoconfig again to resolve the issue

SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

(2) Another example would be

SELECT * FROM employers
WHERE dept_id = (SELECT dept_id
FROM dept);

This will fail as we are selecting all dept_id in subquery while expecting one. This will succeed if dept is having one rows only. To resolve we need to add some where clause in subquery so that it returns one value

SELECT * FROM employers
WHERE dept_id = (SELECT dept_id
FROM dept where location='NEWYORK');

or

We can put in operator in place of equality operator

SELECT * FROM employers
WHERE dept_id in (SELECT dept_id
FROM dept);

I hope you this content on ORA-01427: single-row subquery returns more than one row. Please do provide feedback

See also  How to generate TNS_ADMIN files in R12

Also Reads
ORA-00900 : Invalid Sql statement : check out this post for the various solutions for ORA-00900 invalid sql statement.Reasons for the error.How to debug and resolve it quickly,
ORA-03113 : Check out method to resolve ORA-03113: end-of-file on communication channel. This error could happen in database startup, running program
ora-29283: invalid file operation : check out this post on how to resolve ORA-29283 : invalid file operation
ORA-29285: file write error : ORA-29285: file write error is the common error while doing file handling operation.Check out this post on various reason and solution on how to solve it
ORA-00054 : ORA-00054: resource busy and acquire with NOWAIT specified with DDL statement,select for update,forms nowait locks,DDL_LOCK_TIMEOUT

1 thought on “ORA-01427: single-row subquery returns more than one row”

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top