I create a test procedure:
CREATE OR REPLACE PROCEDURE PROCE_0(
ival IN OSM_TAB_PROCE.COL_0%TYPE:=10,
ocnt IN OUT NUMBER)
AS
a OSM_TAB_PROCE.COL_0%TYPE;
r OSM_TAB_PROCE%ROWTYPE;
i NUMBER := 0;
CURSOR c1(ct0 NUMBER) IS SELECT COL_0 FROM OSM_DML_2SP.OSM_TAB_PROCE WHERE COL_0>ct0;BEGIN
IF c1%ISOPEN=FALSE THEN
OPEN c1(196);
END IF;
LOOP
FETCH c1 INTO a;
EXIT WHEN c1%NOTFOUND;
SELECT COL_0,COL_1,COL_2 INTO r FROM OSM_DML_2SP.OSM_TAB_PROCE
WHERE COL_0=a AND ROWNUM<2; -- if ROWNUM < 2 delete then ORA-01422
r.COL_0 := a + ival;
r.COL_2 := TO_CHAR(a) || TO_CHAR(SYSDATE);
INSERT INTO OSM_DML_2SP.OSM_TAB_PROCE VALUES r;
i := i + 1;
END LOOP;
CLOSE c1;
ocnt := i;
END;
Error Message:
Error: ORA-01422: exact fetch returns more than requested number of rows
Cause: You tried to execute a SELECT INTO statement and more than one row was returned.
Action: The options to resolve this Oracle error are:
1. Rewrite your SELECT INTO statement so that only one row is returned.
2. Replace your SELECT INTO statement with a cursor.
For example, if you tried to execute the following SQL statement:
SELECT supplier_id
INTO cnumber
from suppliers
where supplier_name = 'IBM';
And there was more than one record in the suppliers table with the supplier_name of IBM, you would receive the ORA-01422 error message.
In this case, it might be more prudent to create a cursor and retrieve each row if you are unsure of how many records you might retrieve.
Error Message Ref:
阅读(2926) | 评论(0) | 转发(0) |