Home » SQL & PL/SQL » SQL & PL/SQL » Ignore ORA-01427: single-row subquery returns more than one row (SQL*Plus: Release 11.1.0.7.0)
Ignore ORA-01427: single-row subquery returns more than one row [message #436469] Wed, 23 December 2009 13:53 Go to next message
cool_scorpio
Messages: 2
Registered: December 2009
Junior Member
Hi
I am trying to ignore ORA-01427: single-row subquery returns more than one row for now and just write it to log, so that we can resolve that particular issue. I have tried adding :

EXCEPTION
when TOO_MANY_ROWS then
DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS Exception for : '||timestampmap.name);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SOME OTHER Exception for : '||timestampmap.name);
But these dont' seem to work. My procedure still throws the error and exits. Is there any way I could log this error and proceed to the next record.

My update statement looks like this -

update work set number=(
select xyz.number from votes xyz where xyz.name=timestampmap.name );

Really appreciate any help. Thank you.
Re: Ignore ORA-01427: single-row subquery returns more than one row [message #436471 is a reply to message #436469] Wed, 23 December 2009 13:57 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>I am trying to ignore ORA-01427: single-row subquery returns more than one row for now and just write it to log,
can not ignore
must return only 1 row

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

>update work set number=( select xyz.number from votes xyz where xyz.name=timestampmap.name );

It makes no sense to assign multiple values to a scalar.

It is BAD idea to use KEYWORD like "number" as column_name.

[Updated on: Wed, 23 December 2009 13:59]

Report message to a moderator

Re: Ignore ORA-01427: single-row subquery returns more than one row [message #436479 is a reply to message #436471] Wed, 23 December 2009 15:08 Go to previous messageGo to next message
cool_scorpio
Messages: 2
Registered: December 2009
Junior Member
My apologies,BlackSwan. I accept it was oversight on my part not to read the posting guidelines.
Also thank you for pointing out that this cannot be done.
Do you think there is any way to get around this ?
I mean execute the select statement into an array before the update and then check if array.length >1, then don't update. I am not a pl sql expert, but I just can't seem to get the array stuff working.
Regarding
>It makes no sense to assign multiple values to a scalar.

>It is BAD idea to use KEYWORD like "number" as column_name.
That was just a example of the real code. Again my apologies, its not the real code.
Re: Ignore ORA-01427: single-row subquery returns more than one row [message #436480 is a reply to message #436479] Wed, 23 December 2009 15:12 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Your query is returning multiple rows
select xyz.number from votes xyz where xyz.name=timestampmap.name


To avoid you can use Correlated query in update statement
Re: Ignore ORA-01427: single-row subquery returns more than one row [message #436481 is a reply to message #436469] Wed, 23 December 2009 15:15 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

When Others is not followed by Raise its always almost a bug. Avoid it.
Re: Ignore ORA-01427: single-row subquery returns more than one row [message #436482 is a reply to message #436469] Wed, 23 December 2009 15:16 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
cool_scorpio wrote on Wed, 23 December 2009 20:53
update work set number=(
select xyz.number from votes xyz where xyz.name=timestampmap.name );

What is TIMESTAMPMAP.NAME? Is there any correlation to the updated table (WORK) in the subquery?

You may filter only rows for which the subquery returns at most/exactly one row.
UPDATE work
SET number = (SELECT .. FROM .. WHERE ..)
WHERE 1 >= (SELECT COUNT(*) FROM .. WHERE ..);

If there is no condition on the updated table (WORK) in the subquery (= the subquery returns the same rowset for each row of updated table), this will do the same as before (update all rows or none).
Re: Ignore ORA-01427: single-row subquery returns more than one row [message #436483 is a reply to message #436479] Wed, 23 December 2009 15:24 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Ignore ORA-01427: single-row subquery returns more than one row [message #436486 is a reply to message #436479] Wed, 23 December 2009 16:27 Go to previous message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you might consider use of error logging; my database is 10g (I don't have 11g, but I belive it exists there too). Here's an example - review it, read some more documentation and see whether you can use it or not.

First, let's create two test tables (based on Scott's schema): TEST_DEPT will contain departments with an "artificial" column named "hire_date"; I'll try to update it to a single value based on data stored in employee's table, TEST_EMP. TEST_EMP will contain only one record for department 10 (so that it won't raise TOO-MANY-ROWS), while other departments will contain more than a single record in the TEST_EMP table.
SQL> create table test_dept as
  2    select deptno, dname, to_date(null) hire_date
  3    from dept;

Table created.

SQL> create table test_emp as
  2    select deptno, empno, ename, hiredate
  3      from emp
  4      where deptno = 10
  5        and rownum = 1
  6    union
  7    select deptno, empno, ename, hiredate
  8      from emp
  9      where deptno <> 10;

Table created.

SQL> select * from test_dept;

    DEPTNO DNAME          HIRE_DAT
---------- -------------- --------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL> select * from test_emp order by deptno;

    DEPTNO      EMPNO ENAME      HIREDATE
---------- ---------- ---------- --------
        10       7782 CLARK      09.06.81
        20       7566 JONES      02.04.81
        20       7788 SCOTT      09.12.82
        20       7876 ADAMS      12.01.83
        20       7369 SMITH      17.12.80
        20       7902 FORD       03.12.81
        30       7499 ALLEN      20.02.81
        30       7521 WARD       22.02.81
        30       7654 MARTIN     28.09.81
        30       7698 BLAKE      01.05.81
        30       7844 TURNER     08.09.81
        30       7900 JAMES      03.12.81

12 rows selected.

SQL>

Now let's try to do the update:
SQL> update test_dept d set
  2    d.hire_date = (select e.hiredate from test_emp e
  3                   where e.deptno = d.deptno
  4                  );
  d.hire_date = (select e.hiredate from test_emp e
                 *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row


SQL>
A failure, as expected. So let's create an error logging table which will contain records that will fail to be updated, along with the error:
SQL> exec dbms_errlog.create_error_log (dml_table_name => 'test_dept');

PL/SQL procedure successfully completed.

SQL> 
The result is a new table, called ERR$_TEST_DEPT. It is now empty:
SQL> select * from err$_test_dept;

no rows selected

SQL> 

Now, a new UPDATE statement syntax, which will include error logging:
SQL> update test_dept d set
  2    d.hire_date = (select e.hiredate from test_emp e
  3                   where e.deptno = d.deptno
  4                  )
  5  log errors into err$_test_dept ('update') reject limit unlimited;

2 rows updated.

SQL>
Lovely! No error!

What have we done? First, the updated table, TEST_DEPT:
SQL> select * from test_dept;

    DEPTNO DNAME          HIRE_DAT
---------- -------------- --------
        10 ACCOUNTING     09.06.81
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL>
As expected, department with only one employee got updated. The others are still NULL.

Now the error logging table:
SQL> select ora_err_mesg$, deptno, dname, hire_date
  2  from err$_test_dept;

ORA_ERR_MESG$                                                DEPTNO DNAME    HIRE_DAT
------------------------------------------------------------ ------ -------- --------
ORA-01427: single-row subquery returns more than one row     20     RESEARCH 17.12.80
ORA-01427: single-row subquery returns more than one row     30     SALES    20.02.81

SQL>

Once you have information about records that were "invalid", perhaps you'll be able to "fix" them and finish updating.

Another option, a trivial one but not that sophisticated is looping. Here's how it can be done: we'll need to have a separate BEGIN-EXCEPTION-END block within the loop, so that - once we get a message (no matter which one, thus WHEN OTHERS) we want to log it and continue with the next record.

As we can't use SQLERRM directly, we need a local variable to store error message and, eventually, insert it into the error log table.

First, an empty table:
SQL> select * from test_dept;

    DEPTNO DNAME          HIRE_DAT
---------- -------------- --------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS

Let's update!
SQL> declare
  2    l_err_msg varchar2(200);
  3  begin
  4    for cur_r in (select d.deptno from test_dept d) loop
  5      begin
  6        update test_dept d set
  7          d.hire_date = (select e.hiredate
  8                         from test_emp e
  9                         where e.deptno = cur_r.deptno
 10                        )
 11          where d.deptno = cur_r.deptno;
 12      exception
 13        when others then
 14          l_err_msg := sqlerrm;
 15          insert into test_dept_err (deptno, err_msg)
 16            values (cur_r.deptno, l_err_msg);
 17      end;
 18    end loop;
 19  end;
 20  /

PL/SQL procedure successfully completed.

The result(s):
SQL> select * from test_dept;

    DEPTNO DNAME          HIRE_DAT
---------- -------------- --------
        10 ACCOUNTING     09.06.81
        20 RESEARCH
        30 SALES
        40 OPERATIONS

SQL> select * from test_dept_err;

    DEPTNO ERR_MSG
---------- ------------------------------------------------------------
        20 ORA-01427: single-row subquery returns more than one row
        30 ORA-01427: single-row subquery returns more than one row

SQL>


That's, I guess, all I meant to say.
Previous Topic: What is the best (most efficient) way to process complex Select Queries?
Next Topic: SQL Query Help
Goto Forum:
  


Current Time: Thu Sep 29 00:32:03 CDT 2016

Total time taken to generate the page: 0.10828 seconds