How to do custom paging in Oracle Stored Procedure

Last Reply on Apr 25, 2013 06:23 AM By Mudassar

Posted on Apr 25, 2013 06:22 AM

how can create this store proc in oracle thanks;)

 

CREATE PROCEDURE [dbo].[GetCustomersPageWise]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@PageCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
            (
                  ORDER BY [CustomerID] ASC
            )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[City]
      ,[Country]
      ,[PostalCode]
      ,[Phone]
      ,[Fax]
    INTO #Results
      FROM [Customers]
    
      DECLARE @RecordCount INT
      SELECT @RecordCount = COUNT(*) FROM #Results
 
      SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
      PRINT       @PageCount
          
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) +@PageSize) - 1
    
      DROP TABLE #Results
END
CREATE PROCEDURE [dbo].[GetCustomersPageWise]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@PageCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
            (
                  ORDER BY [CustomerID] ASC
            )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[City]
      ,[Country]
      ,[PostalCode]
      ,[Phone]
      ,[Fax]
    INTO #Results
      FROM [Customers]
    
      DECLARE @RecordCount INT
      SELECT @RecordCount = COUNT(*) FROM #Results
 
      SET @PageCount = CEILING(CAST(@RecordCount AS DECIMAL(10, 2)) / CAST(@PageSize AS DECIMAL(10, 2)))
      PRINT       @PageCount
          
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
    
      DROP TABLE #Results
END

 

Posted on Apr 25, 2013 06:23 AM

The stored procedure you are using is for SQL Server. For Oracle, please refer

http://www.codeproject.com/Articles/55616/Custom-Paging-Stored-Procedure

 

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html