Home » SQL & PL/SQL » SQL & PL/SQL » Paging in pl/sql (merged) (Oracle, 10g, Windows 2003 server)
Paging in pl/sql (merged) [message #434064] Mon, 07 December 2009 01:52 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
My problem is as follows

I am suppose to display 3 records per page in sharepoint by
using a stored procedure in oracle pl/sql.What if records
fetched is in millions.Following is code for stored
procedure wich displays only first three records in
sharepoint.

Create or replace procedure sp_emp(p_pageno number) is 
empnum emp.empno%Type;                               
empname emp.ename%Type;
num number;
Cursor c is
Select empno,ename,r
From (Select empno,ename,rownum r
      From (Select empno,ename
            From emp
            Order by empno))
Where r Between 1 And 3;
Begin
Open c;
Loop
 Fetch c into empnum,empname,num;
 Exit when c%NotFound;
 Dbms_output.put_line('EMP NO: '||empnum||' '||'EMP NAME: '||empname);
End loop;
End;
/


i am not using input parameter p_pageno in my code

Shown below is the invocation of sp_emp showing first 3
recods.


begin
 sp_emp(1);
end;
/
EMP NO: 7369 EMP NAME: SMITH
EMP NO: 7499 EMP NAME: ALLEN
EMP NO: 7521 EMP NAME: WARD


Hope u understood my requirement

Sharepoint developer is going to pass page number which i'm suppose to accept as input parameter in my stored procedure
i.e. if he passes page no 3 i'm suppose to display 7 to 9 records or he passes page no 4 i'm suppose to display 10 to 12 records etc


Regards,
Ritesh
Re: Paging in pl/sql [message #434066 is a reply to message #434064] Mon, 07 December 2009 01:58 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
use ROWNUM
Re: Paging in pl/sql (merged) [message #434071 is a reply to message #434064] Mon, 07 December 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1: 1 to 3
2: 4 to 6
3: 7 to 9
n: 3*(n-1)+1 to 3*n

Regards
Michel
Re: Paging in pl/sql (merged) [message #434074 is a reply to message #434071] Mon, 07 December 2009 02:12 Go to previous message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member


Thanks Michel it worked successfully.[/
Previous Topic: display message
Next Topic: Offset Value of a image
Goto Forum:
  


Current Time: Wed Sep 28 09:14:55 CDT 2016

Total time taken to generate the page: 0.06214 seconds