Subject: CURSOR and ROWID

[ruse7013]

Env: Oracle 8.1.7

The following procedure is executed:
--------------------------------------
PROCEDURE proc_name AS
CURSOR cur_name IS
SELECT col1, col2, rowid
FROM table_name;
BEGIN
FOR rec_name IN cur_name LOOP
BEGIN
v_var3 := foo(rec_name.col1, rec_name.col2);
UPDATE table_name
SET col3 = v_var3
WHERE rowid = rec_name.rowid;
END;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_err_msg:=SUBSTR(sqlerrm,1,255);
RAISE_APPLICATION_ERROR(-20004,'ERROR: Unable to update table_name '||v_err_msg);
END;
END proc_name;
------------------------------------------

And the following ERRORS are logged:
------------------------------------------
ORA-20001: ORA-20001: ORA-20004: ERROR: Unable to update table_name
ORA-01422: exact fetch returns more than requested number of rows
------------------------------------------

Question:
What's the reason for ORA-01422 in this case, IF the ROWID is used in the UPDATE statement?

Thank you.
<ruse7013>

 

[HimanB]

Hi Ruse7013,
I think this has nothing to do with the Update statement but with your Function FOO which must be having some Select Statment & it is trying to return more than One value in V_var3.

So,please send the complete code & Sample table data to investigate.

Regards
Himanshu

 

[ruse7013]

Hi Himanshu,

You are right. I found the problem in the foo() function. It's related to:

SELECT SYSDATE
FROM DUAL;

TWO records are returned instead of ONE:
5/5/2004 12:45:36 PM
5/5/2004 12:45:36 PM

How can you explain that?

I added:

SELECT SYSDATE
FROM DUAL
WHERE ROWNUM = 1;

... but I don't like it.

Thank you for your help.

Regards,
<ruse7013>

 

[sem]

You'll have a lot of problems with DUAL containing more than 1 record! I'd suggest you to remove extra records, created probably by some hacker or not quite accurate student, if the name DUAL references SYS.DUAL or drop this object at all if it belongs to somebody else. In the second case I'd also suggest to avoid using this name for user objects in future. This word is not reserved, but its usage is commonly adopted.

Regards, Dima

http://seminihin.narod.ru

 

[ruse7013]

Sposibo, Dima!

Regards,
<ruse7013>

 

[HimanB]

Ni Za Shto ruse7013.
Dima vam Dal ochen paleznoi covet...
Spasibo Dima.

Poka
Himanshu