Implement Oracle Paging For ANY Query?

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.

First of all, What's the point in doing

SELECT TABLE1.*, TABLE1.someValue from TABLE1

Wouldn't TABLE1.* automatically select "someValue", so why query it redundantly ?

Secondly for pagination, try the analytical query approach

SELECT * FROM {    SELECT col1, col2, col3    , row_number() OVER (order by col1) position    FROM TABLE1} WHERE rn >= p_seek and rn < (p_seek+p_count)

p_seek is the starting position and p_count is the number of rows to fetch.

Here instead of col1, col2, col3, etc you can do TABLE1.*, TABLE1.someValue etc.