Implement Oracle Paging For ANY Query?

Question

I've found a lot of examples of paging in Oracle. The particular one I'm using now looks a like this:

SELECT * FROM (
  SELECT a.*, ROWNUM RNUM FROM (
    **Select * From SomeTable**) a 
  WHERE ROWNUM <= 500) b 
WHERE b.RNUM >= 1

The line in bold represents the 'original' query. The rest of the SQL there is to implement the paging. The problem I'm running into is a query that is perfectly valid by itself; will fail when I place it inside of my paging code.

As an example - this query will fail:

SELECT TABLE1.*, TABLE1.SomeValue FROM TABLE1

With a ambiguous column error. But, without my extra code; it will run just fine. I have a large number of 'saved' queries, but I have to ensure that my paging solution doesn't invalidate them.

I've used SQL Developer as my Oracle querying tool and it manages to implement paging that works even with the above query that fails when I wrap it in the paging code. Can anyone tell me how they manage to pull it off?

Answer 1

First off, the original query would need to have an ORDER BY clause in order to make the paging solution work reasonably. Otherwise, it would be perfectly valid for Oracle to return the same 500 rows for the first page, the second page, and the Nth page.

SQL Developer is not changing your query to implement paging. It is simply sending the full query to Oracle and paging the results itself using JDBC. The JDBC client application can specify a fetch size which controls how many rows are returned from the database to the client at a time. The client application can then wait for the user to either decide to go to the next page or to do something else in which case the cursor is closed.

Whether the SQL Developer approach makes sense depends heavily on the architecture of your application. If you're trying to page data in a stateless web application, it probably doesn't work because you're not going to hold a database session open across multiple page requests. On the other hand, if you've got a fat client application with a dedicated Oracle database connection, it's quite reasonable.