Ignore ORA-01427: single-row subquery returns more than one row [message #436469] |
Wed, 23 December 2009 13:53  |
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   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
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   |
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 #436482 is a reply to message #436469] |
Wed, 23 December 2009 15:16   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
cool_scorpio wrote on Wed, 23 December 2009 20:53update 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 #436486 is a reply to message #436479] |
Wed, 23 December 2009 16:27  |
 |
Littlefoot
Messages: 21823 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.
|
|
|