CODEWITHSUNDEEP
oracle-database
user334560415
user334560415

Reputation: 17

trying to read cursor from record but getting exact fetch returns more than requested number of rows?

I am trying to read cursor from record but getting that error

ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 14

declare 
ename      table1.ename%TYPE;
seq_ENAME   NUMBER (4);


CURSOR List_ENAME_cur IS
SELECT ENAME from table1
WHERE status = 2 AND ROWNUM <= 100;

begin 

FOR List_ENAME_rec IN List_ENAME_cur
loop
select substr(ename,5,4),ENAME into seq_ENAME,ename from table1 where REGEXP_LIKE(ENAME,'[1-9]{4}[_][1-9]{2}[_][1-9]{2}[_][1-9]{2}[_][0-9]{4}');
DBMS_OUTPUT.PUT_LINE('seq_ENAME'||seq_ENAME);
end loop;
end;

Upvotes: 0

Views: 124

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

At the moment your cursor and the select you are doing inside the loop are not related. The cursor loop is just identifying up to 100 records and you're looping over those, but not using the results. You're almost doing:

for i in 1..100 loop
  select ... into ... from ...
end loop;

So each time around the loop you're trying to select all rows into your variable, hence the error you're getting.

You can refer to the cursor variable inside the loop, as list_ename_rec.ename. But you're adding a where clause inside the loop; you can still do that test inside the loop, but you might as well do it in the cursor declaration:

declare
  CURSOR List_ENAME_cur IS
    SELECT ENAME, substr(ename,5,4)
    from table1
    WHERE status = 2
    AND REGEXP_LIKE(ENAME,
      '[1-9]{4}[_][1-9]{2}[_][1-9]{2}[_][1-9]{2}[_][0-9]{4}')
    AND ROWNUM <= 100;
begin  
  FOR List_ENAME_rec IN List_ENAME_cur
  loop
    DBMS_OUTPUT.PUT_LINE('seq_ENAME'||List_ENAME_rec.seq_ENAME);
  end loop;
end;

Alternatively, if you want to get 100 records but then only display the ones within that set which match your pattern you could do this:

declare 
  seq_ENAME   NUMBER (4);
  CURSOR List_ENAME_cur IS
    SELECT ENAME from table1
    WHERE status = 2
    AND ROWNUM <= 100;
begin 
  FOR List_ENAME_rec IN List_ENAME_cur
  loop
    if REGEXP_LIKE(List_ENAME_rec.ENAME,
        '[1-9]{4}[_][1-9]{2}[_][1-9]{2}[_][1-9]{2}[_][0-9]{4}')
    then
      seq_ENAME := substr(List_ENAME_rec.ename,5,4);
      DBMS_OUTPUT.PUT_LINE('seq_ENAME'||seq_ENAME);
    end if;
  end loop;
end;

Either way, it's worth noting that which 100 rows you get from the table is indeterminate, because you don't have an order by clause.

Upvotes: 1

Related Questions