CODEWITHSUNDEEP
databaseoracle-databaseplsql
Harshana
Harshana

Reputation: 7647

check for cursor found and loop the cursor in pl sql

I have a situation where i need to first check whether a select statement return rows and then loop it. Below it what high level i am doing.

CURSOR euip_info
IS
SELECT
e.TRANS_ID
from
EQUIPINFO e
where
and e.ORD_NO = s_no;

euip_info_t  euip_info%ROWTYPE;

BEGIN
       OPEN euip_info;
       FETCH euip_info INTO euip_info_t;

        IF euip_info%FOUND THEN

         FOR i in euip_info
           LOOP
           //Do something
           END LOOP;       
        ELSE
          //Do otherthing       
        END IF; 
END

But i am getting below error when the cursor has values which go to the loop section.

ORA-06511: PL/SQL: cursor already open

How can i check whether the cursor has values and do a loop also?

Upvotes: 0

Views: 4074

Answers (2)

tbone
tbone

Reputation: 15473

You can simply do this to iterate the cursor:

declare
  cursor my_cur is 
  select col1, col2
  from my_table;

  l_cnt number := 0;

begin
  for rec in my_cur
  loop
    l_cnt := l_cnt + 1;
    -- do something with rec.col1, rec.col2 

  end loop;
  if (l_cnt = 0) then
    -- the cursor was empty

  end if;
end;

Upvotes: 0

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

You can do it like this:

CURSOR euip_info
  IS
    SELECT e.TRANS_ID
      FROM EQUIPINFO e
    WHERE e.ORD_NO = s_no;

  euip_info_t  euip_info%ROWTYPE;

BEGIN
  OPEN euip_info;
  FETCH euip_info INTO euip_info_t;

  IF euip_info%FOUND THEN
    LOOP
      EXIT WHEN euip_info%NOTFOUND;
      -- do something with euip_info_t

      -- fetch next record
      FETCH euip_info INTO euip_info_t;
    END LOOP;       
  ELSE
    --Do other thing       
  END IF; 

  CLOSE euip_info;
END;

The problem was that you were trying to open the cursor again using it in the FOR loop.

Upvotes: 1

Related Questions