TriniDBA

Problem: A custom developed XML report for EBS is returning an “ORA-01427: single-row subquery returns more than one row” error when run.

Solution: For this report, include the predicate “AND rownum = 1” in the WHERE clause of the SQL SELECT statement.

The error can be reproduced using the following SQL statements:

SQL> create table temp_company
  2  (coid number,
  3   coname varchar2(10)
  4  );

Table created.

SQL> create table temp_site
  2  (coid number,
  3   cosite varchar2(10)
  4  );

Table created.

SQL>
SQL> insert into temp_company (coid, coname)
  2  values (1, 'Acme');

1 row created.

SQL> insert into temp_site (coid, cosite)
  2  values (1, 'Florida');

1 row created.

SQL> select coid, coname, (select cosite from temp_site ts 
where ts.coid = tc.coid) sitename from temp_company tc;

      COID CONAME     SITENAME
---------- ---------- ----------
         1 Acme       Florida

SQL> insert into temp_site (coid, cosite)
  2  values (1, 'Texas');

1 row created.

SQL> select coid, coname, (select cosite from temp_site ts 
where ts.coid = tc.coid) sitename from temp_company tc;
select coid, coname, (select cosite from temp_site ts where ts.coid = tc.coid) s
itename from temp_company tc
                      *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


SQL>

As you can see, the SELECT statement has a subquery that is supposed to return one row to the main query. However, when the subquery returns more than one rows, then the ORA-01427 error is thrown.

If it does not matter which value from the subquery is returned, then using the predicate ‘ROWNUM = 1’ in the WHERE clause provides a simple solution to the problem.

SQL> select coid, coname, (select cosite from temp_site ts where ts.coid = tc.co
id and rownum = 1) sitename from temp_company tc;

      COID CONAME     SITENAME
---------- ---------- ----------
         1 Acme       Florida

SQL>