First, next, previous, last record navigation

[mdevman]

Hey all,
This is my question. I am looking for the method used to select the first, last, next, previous records from an ordered table using SQL statements.

Using ODBC I am pulling data across a network. I want to pull just one record at at time. I need to allow my users to navigate to the next, previous, last and first records, so I need to be able to select the rownum, or rowid, or just the primary key field's value of those relative rows, dependant on the order that the user wants to view the records.

I have been going through the Oracle documentation and have not found what I am looking for. Considering that this must be a fairly common need, I am hoping someone can lead me to the solution.

Thanks.
Mike D

 

[stefanhei]

Hi.
If you use ADO you can use the Methods provided by the Recordset-objects and clientsided cursors for that task. Fetching just 1 Record at a time will cause lots of overhead but you can set the Prefetch count (ODBC-Driver-Config) low (if Ora-Version > 8.0.3). The .MoveLast-method will fetch all rows anyway.

Stefan

 

[sem]

You may also take a look at FIRST_VALUE/LAST_VALUE functions. They do not provide navigation, but rather DIRECT access to the first/last values.

 

[mdevman]

I am using Visual FoxPro 6, so no ADO.
I looked into the FIRST_VALUE/LAST_VALUE functions. I thought I might be able to use them, but when I tried Oracle threw an error:ERROR at line 1:
ORA-00439: feature not enabled: OLAP Window Functions

I looked but was unable to find out how to enable this feature. The other aspect is that from what I did find out about these Window functions is that the result set in a Client-Server environment, the result set for the window needs to brought over to the Client machine. What I am trying to accomplish is to not do that. But, if I am mistaken, please let me know.

Thank you for your responses.

 

[sem]

As for Oracle error, check compatibility flag (it should be 8.1.6 at least) and/or event 10408 in your init.ora file.

As for passing the "raw" set to client - I have no such information. I'm sure that everything is done by server. Where did you find this info?

 

[mdevman]

My compatible parameter is set to 8.0.5. This is a must since there is an 8.0.5 home on the server also. I was instructed to do this by Oracle support when I added the 8.1.7 home to the server.

Where do I find an event 10480 in an init.ora file?

The information is in the Oracle documentation that came with the software. In order to use the window functions the data for the window needs to be brought over to the client. At least that is what the documentation seems to indicate, I may be mistaken.

So, back to my original question, what is a good method for navigating the records with a parameterized view, next, previous, first and last?

 

[sem]

Your compatibility parameter prevents from using this feature, as it was added in 8.1.6. So the only chance is to set it to 8.1.6 or higher. Did the instruc to set this parameter for BOTH databases? I can not grasp a reason ... Do you need your application to run on both databases?

As for your original question, I suppose that unfortunately you SHOULD fetch all the records.

 

[mdevman]

Hey SEM,
thanks again for the feedback. I am going to look into the compatible parameter setting. Hopefully I do not need it set to 8.0.5. In the meantime, I guess I will just have to work on the solution myself, nothing is impossible, and since the best method for remote views is using parameterized views there must be a way to navigate from them.