oracle ora-01422,Oracle/PLSQL: ORA-01422

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:

阅读(2472) | 评论(0) | 转发(0) |