| Unexpected behaviour of ORA-01422: exact fetch returns more than requested number of rows [message #130200] |
Thu, 28 July 2005 13:29  |
welchaz
Messages: 23
Registered: April 2005
Location: Tucson, AZ
|
Junior Member |
|
|
I am surprised that when the ORA-01422 error is encountered, that the value of the variable being selected "INTO" still gets populated. See the following simple example (this is Oracle 8.1.7.4):
1 declare
2 v_crew TWMWORKPACKET.CD_CREW%TYPE;
3 begin
4 begin
5 SELECT distinct cd_crew
6 INTO v_crew
7 FROM twmworkpacket
8 WHERE cd_dist = 'MAT'
9 AND cd_wr = 142653
10 AND cd_crew IS NOT NULL;
11 exception
12 when others then
13 dbms_output.put_line(SQLERRM);
14 end;
15 DBMS_OUTPUT.PUT_LINE('V crew = ' || v_crew);
16 end;
17 /
ORA-01422: exact fetch returns more than requested number of rows
V crew = POLHLR
PL/SQL procedure successfully completed.
I expected that when an exception is encountered, the value of V_CREW would remain null as initialized. However, it appears to be populating the variable with the first crew it comes across, then erroring. Is this how it was intented to work?
Thanks in advance
|
|
|
|
|
|
|
|
| Re: Unexpected behaviour of ORA-01422: exact fetch returns more than requested number of rows [message #130497 is a reply to message #130200] |
Sun, 31 July 2005 14:43  |
oraclejo
Messages: 50
Registered: July 2005
Location: Ammar
|
Member |
|
|
regardless of the value retrieved, the exception too_many_rows is an indication that you are trying to populate a single value variable with a set of values. If you are expecting that, you should be writing a Cursor in scrolling through it, if you are not, then you should stop immediately and raise an error, where the value fetched is really irrelevent, because it could mean that your data is not what you are expecting (duplicates)
Ammar Sajdi
www.e-ammar.com
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
