Problem solve Get help with specific problems with your technologies, process and projects.

Forward and backward scrolling cursors

I am trying to figure out a SQL statement and was hoping for some direction or an example.

What I would like to do is write a statement that selects a specific number of rows (in my case 50). I would like to link it to a web page that uses the keys < > to scroll though the first 50 records in the table and then the next 50 forward or back, until it hits the end of the rows.

I'd also like to be able to add user input that would allow the user to specify where to start then bring back the next 50 rows from that starting point.

I hope this explains the situation somewhat. If you require more information, I could supply it. If you can help or give ideas or examples that would be great. I believe it has something to do with the cursor state.

What you are asking for is available in only some of the popular databases. Transact-SQL databases have support for cursors that scroll forward and backward. If you are using a Transact-SQL database, you can declare a cursor as STATIC, DYNAMIC, or KEYSET, all of which are fully scrollable. You can then use FETCH and FETCH PRIOR FROM to navigate the cursor.

Unfortunately, Oracle only supports forward-fetching cursors.

If you wanted to retrieve previous rows in a forward-only cursor, you would have to close and reopen the cursor, and then scroll it forward until you reached the row(s) you needed. Not a necessarily efficient prospect, especially when dealing with a relatively large number of rows. The solution in Oracle and other databases that only support forward-fetching cursors is probably to implement some sort of caching at the application level. Many database call libraries support such caching. I believe even ODBC and OLE DB support caching at the client level, or in your case, application server level. This solution can be inefficient, as you would need to load the first 150 rows in order to display rows 101 through 150. If those rows are not accessed by the application before they are disregarded, they would have been loaded more or less in vain.

For More Information

  • What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL