custome paging in C# & oracle using inline queries.

Hi Experts,

I need to implement custome paging so that I could handle larger dataset. I saw many articles for that but all of them talking about ROW_NUMBER() OVER() which is not available in oracle.

I am using
- Asp.Net and oracle db.
- using in-line queries rather store procedure.

How can I apply custome paging to address larger dataset ?

Looking forward to hearing from you.
satmishaAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
OK.  I think I found your error.  If you are getting ORA-00933: SQL command not properly ended, then the issue is with your table alias.  While SQL Server supports the syntax of using the word AS, Oracle does not.  However, Oracle doesn't require a table alias to be supplied.  If you use what I posted, it should work just fine.  This would be your correct version.
SELECT productid, 
       productname, 
       supplierid, 
       categoryid, 
       quantityperunit, 
       unitprice, 
       unitsinstock, 
       unitsonorder, 
       reorderlevel, 
       discontinued, 
       categoryname, 
       suppliername 
FROM   (SELECT productid, 
               productname, 
               supplierid, 
               categoryid, 
               quantityperunit, 
               unitprice, 
               unitsinstock, 
               unitsonorder, 
               reorderlevel, 
               discontinued, 
               (SELECT categoryname 
                FROM   categories 
                WHERE  categories.categoryid = products.categoryid) AS 
               CategoryName, 
               (SELECT companyname 
                FROM   suppliers 
                WHERE  suppliers.supplierid = products.supplierid)  AS 
               SupplierName, 
               Row_number() 
                 OVER ( 
                   ORDER BY productname)                            AS RowRank 
        FROM   products) ProductsWithRowNumbers 
WHERE  rowrank > @startRowIndex 
       AND rowrank <= ( @startRowIndex + @maximumRows ) 

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
ROW_NUMBER is certainly available in Oracle.  If I remember correctly it was introduced in 10g.  As long as you aren't running a version less than 10, which I wouldn't think many people are doing today, it should work just fine.

Post the query you are using and the error you are getting.
0
 
Geert GOracle dbaCommented:
not available in oracle ?
lol, it's usually the opposite way.

oracle 12 has a new feature for paging ...
check the row limiting clause
http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#i2065706
http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF55636
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
satmishaAuthor Commented:
Thanks experts I am using toad for oracle 11.
0
 
johnsoneSenior Oracle DBACommented:
The version of TOAD doesn't tell you the database version.

You can get the database version with this query:

select * from v$version;

That should work in non-privileged account.
0
 
Geert GOracle dbaCommented:
what has toad got to do with paging ?
you want to give Toad to a business user and expect him to work directly with your oracle tables ?
0
 
satmishaAuthor Commented:
Consider this as an example which works fine in sql but not in oracle:

SELECT     ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit,
               UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
Discontinued,
               CategoryName, SupplierName
FROM
   (
       SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
              UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel,
Discontinued,
              (SELECT CategoryName
               FROM Categories
               WHERE Categories.CategoryID = Products.CategoryID) AS
CategoryName,
              (SELECT CompanyName
               FROM Suppliers
               WHERE Suppliers.SupplierID = Products.SupplierID) AS
SupplierName,
              ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
        FROM Products
    ) AS ProductsWithRowNumbers
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

have taken reference from :

https://msdn.microsoft.com/en-us/library/bb445504.aspx
0
 
slightwv (䄆 Netminder) Commented:
>>which works fine in sql but not in oracle

Aside from how you assign the variables, what doesn't work in Oracle?

I use ROW_NUMBER with .Net to do paging the exact way you are trying and have been doing it that way for years.

Since you are using .Net, if you aren't already using it, I suggest ODP.Net and the Managed driver as your data access provider.
0
 
johnsoneSenior Oracle DBACommented:
As suggested, could be a driver issue.  We really can't help you there unless you have some sort of description of what doesn't work.  An error message would be ideal.

Also, there isn't a need for the subqueries in the select.  Writing it as a join would probably be more efficient.  Like this:
SELECT productid, 
       productname, 
       supplierid, 
       categoryid, 
       quantityperunit, 
       unitprice, 
       unitsinstock, 
       unitsonorder, 
       reorderlevel, 
       discontinued, 
       categoryname, 
       companyname 
FROM   (SELECT product.productid, 
               product.productname, 
               product.supplierid, 
               product.categoryid, 
               product.quantityperunit, 
               product.unitprice, 
               product.unitsinstock, 
               product.unitsonorder, 
               product.reorderlevel, 
               product.discontinued, 
               categories.categoryname, 
               suppliers.companyname, 
               Row_number() 
                 OVER ( 
                   ORDER BY productname) AS rowrank 
        FROM   products 
               LEFT JOIN categories 
                      ON categories.categoryid = products.categoryid 
               LEFT JOIN suppliers 
                      ON suppliers.supplierid = products.supplierid) 
WHERE  rowrank > @startRowIndex 
       AND rowrank <= ( @startRowIndex + @maximumRows ) 

Open in new window

0
 
johnsoneSenior Oracle DBACommented:
Try these 3 queries in an interactive session:

select * from (select * from dual);
select * from (select * from dual) table_alias;
select * from (select * from dual) as table_alias;

First one demonstrates table alias is not necessary in Oracle.
Second shows correct usage of table alias.
Third shows the error.
0
 
satmishaAuthor Commented:
Thanks a lot for your prompt reply. I'll try to implement them and will revert back ASAP.
0
 
satmishaAuthor Commented:
Thanks johnsone. Thank you so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.