Home » SQL & PL/SQL » SQL & PL/SQL » Empty Collection
Empty Collection [message #395072] Tue, 31 March 2009 04:26 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have pl/sql block below and there i want to continue/exit if collection record empty.

can anyone help me to sort this one .I have solution but when i execute this i got error message also given below.

Declare
 cursor c1 is select * from emp;
 coll_cnt  number;

type rec_list is table of c1%rowtype;
recs rec_list;

Begin

   open c1;
   fetch c1 BULK COLLECT INTO recs;
   
   coll_cnt := recs.COUNT;

   if (coll_cnt = 0) then
     dbms_output.put_line('Collection is Empty');
     exit;
   end if;

   For i in recs.FIRST .. recs.LAST 
    Loop
      dbms_output.put_line('The employee id is '|| recs(i).empid);
    End loop;

End;


Error :

     exit;
     *
ERROR at line 12:
ORA-06550: line 12, column 6:
PLS-00376: illegal EXIT statement; it must appear inside a loop
ORA-06550: line 12, column 6:
PL/SQL: Statement ignored


Re: Empty Collection [message #395080 is a reply to message #395072] Tue, 31 March 2009 04:46 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
This would have been better posted in the SQL & PL/SQL forum don't you think?

RETURN would be the key word you're looking for, although I would use raise_application_error instead.
Re: Empty Collection [message #395157 is a reply to message #395080] Tue, 31 March 2009 08:51 Go to previous message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Thanks cookie...
Previous Topic: To Load a single table at a time from 4 different tables (merged 2)
Next Topic: Fetching changes for a given query
Goto Forum:
  


Current Time: Sun Dec 11 04:31:40 CST 2016

Total time taken to generate the page: 0.12541 seconds