stored procedure in oracle for custom paging RRS feed

  • Question

  • User-222555557 posted
    Hi, I have seen examples using stored procedures in SQL server for retrieving few records at at time (custom paging..)..has anybody done that in Oracle?? I am having difficulty in converting to Oracle..I am not sure how to handle the temporary tables....below... Here is an example in SQL server from dotnetjunkies.. CREATE PROCEDURE [Get_Customers_By_Page] @CurrentPage int, @PageSize int, @TotalRecords int output AS --Create a temp table to hold the current page of data --Add and ID column to count the records CREATE TABLE #TempTable ( ID int IDENTITY PRIMARY KEY, CompanyName nvarchar(40), ContactName nvarchar (30), ContactTitle nvarchar (30), Phone nvarchar (24), Fax nvarchar (24) ) --Fill the temp table with the Customers data INSERT INTO #TempTable ( CompanyName, ContactName, ContactTitle, Phone, Fax ) SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers --Create variable to identify the first and last record that should be selected DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@CurrentPage - 1) * @PageSize SELECT @LastRec = (@CurrentPage * @PageSize + 1) --Select one page of data based on the record numbers above SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM #TempTable WHERE ID > @FirstRec AND ID < @LastRec --Return the total number of records available as an output parameter SELECT @TotalRecords = COUNT(*) FROM Customers Thanks, Pravs
    Thursday, September 2, 2004 9:30 AM

All replies

  • User186802836 posted

    Hi Pravs,

    Oracle is designed diferently from SQL server (Sybase). And it is not convenient to write SP to retrieve a few records. The PL/SQL will accept as an error if your select statement retrive more than one row. to work with multiple rows you should use cursour or in the latest versions of oracle you can use the new statement MERGE. ther is not any #tables.

    more you can find in: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

    Wednesday, October 19, 2011 4:25 PM
  • User269602965 posted

    It is better to create VIEWs than TEMP tables as well.

    But many grid controls today have porgrammable, custom paging built in so that you do not have to go back to database PL/SQL.

    (DevExpress, Telerik, and other grid controls)

    Wednesday, October 19, 2011 5:03 PM