Home » SQL & PL/SQL » SQL & PL/SQL » Collection Methods
Collection Methods [message #385955] Thu, 12 February 2009 00:19 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have given my create /insert scripts and pl/sql block , here i want to find the last record from the loop process and then i want to do some other process if it is last record.

In the below pl/sql block's commented area i want to find the last record of the loop iteration ,for that what collection method i have to use.I used LAST but for each iteration it always gives the LAST record count.COUNT always gives the no of recs.

My need is ,if the cursor return 5 records, at the time of 5 th record processing (ie. last record processing) i have to do another process.

Please help me to get the last record for the below.

create table empl (id number,name varchar2(20))

insert into empl values ('1','kallis')

insert into empl values ('2','Maha')

insert into empl values ('3','Raj')


Declare
 cursor c1 is select * from empl;
 Type empl_type is table of c1%rowtype;
 empltype empl_type;

Begin
 open c1;
 fetch c1 bulk collect into empltype;
 for i in empltype.FIRST .. empltype.LAST
 loop
    dbms_output.put_line('Emp id is '||' :'||empltype(i).id||' '||'Employee Name is'||' :'||empltype(i).name);

/*	if [CODE][B](the last record of the loop)[/B][/CODE] then
	    dbms_output.put_line('Last Record');
	    dbms_output.put_line('Emp id is '||' :'||empltype(i).id||' '||'Employee Name is'||' :'||empltype(i).name);
	end if;*/  
 end loop;
End;
Re: Collection Methods [message #385967 is a reply to message #385955] Thu, 12 February 2009 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put it after the loop then you are sure this is the last one and use empltype.LAST as index.

Regards
Michel
Re: Collection Methods [message #385968 is a reply to message #385955] Thu, 12 February 2009 01:48 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Just use a variable assigned with the number of rows of the cursor.decrement the same variable in the loop and check whether it is equal to 1 or not ..if it's 1 it mean it's the last record..

[Updated on: Thu, 12 February 2009 01:49]

Report message to a moderator

Re: Collection Methods [message #386048 is a reply to message #385955] Thu, 12 February 2009 06:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just use
IF i = empltype.AST then....
Re: Collection Methods [message #386595 is a reply to message #386048] Mon, 16 February 2009 03:44 Go to previous message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Thank you very much...
Previous Topic: 'PLS-00363: expression..' error when executing a stored procedure with in out parameters
Next Topic: Converting Numbers into Wordings (merged)
Goto Forum:
  


Current Time: Sat Dec 03 16:02:14 CST 2016

Total time taken to generate the page: 0.04387 seconds