miker@tdpvax.UUCP (07/08/89)
I am using Oracle on SCO386 Xenix and I wish to be able to be able to retrieve a previous record. I know that Oracle does not inherently have such an operation, although I am mystified as to why they don't. Does anybody know of a way to emulate such an operation very effiently? Maybe there is an undocumented library call that somebody has found? Thanks in advance.
Bron@cup.portal.com (George Bron Faison) (07/17/89)
->In an article written 7/7/89 17:48 miker@tdpvax.UUCP writes:
->
->I am using Oracle on SCO386 Xenix and I wish to be able to be
->able to retrieve a previous record. I know that Oracle does
->not inherently have such an operation, although I am mystified
->as to why they don't. Does anybody know of a way to emulate
Because Oracle is a relational and SQL oriented database
which means you should think *sets* not record-at-a-time!
Previous record has no valid meaning in a truly relational system
where by definition physical ordering is irrelevant.
->as to why they don't. Does anybody know of a way to emulate
->such an operation very effiently? Maybe there is an undocumented
->library call that somebody has found?
You didn't mention *how* you are accessing your data in your posting,
i.e. through SQL*Forms, Sql*Plus, Pro*C, etc. But, I would suggest
one method is to use the ROWID, just save each row's ROWID in a
"prevrow" variable and then use it to "recall the previous record"
as necessary.
Let me again caution you, however, that the very fact that you feel
the need to do this indicates to me that you may not be
"thinking relationally" in which case you are missing the
real power of Oracle and SQL!
(This is not to say that there might not be a legitimate reason...)
Good Luck!
-----------------------------------------------------------------------------
George "Bron" Faison Email: Bron@cup.portal.com
USMAIL: Modern Office Systems Technology, Inc.
"Be Still!" 6455 Penrith Drive
Mechanicsville, VA 23111
Oracle & UNIX Phone: (804) 730-1467 (voice)
-----------------------------------------------------------------------------mao@eden.uucp (Mike Olson </>) (07/18/89)
in article <20532@cup.portal.com>, Bron@cup.portal.com (G.B. Faison) writes >In an article written 7/7/89 17:48 miker@tdpvax.UUCP writes: > >> I am using Oracle on SCO386 Xenix and I wish to be able to be >> able to retrieve a previous record. > > ... you should think *sets* not record-at-a-time! > Previous record has no valid meaning in a truly relational system > where by definition physical ordering is irrelevant. a valid point, but virtually all commercial implementations of SQL provide pretty hefty non-relational extensions. in a real relational system, for example, duplicate tuples are forbidden. it turns out, though, that the pentagon and the phone company care more about solving their own problems than preserving the semantics of relational calculus and set theory. the database systems with which i'm familiar at least offer the option of having duplicate tuples in a single relation (or rows in tables, if you swing that way). the ansi sql committee clearly understands the demands of the marketplace. the appearance of cursors in the new standard is evidence of that. it may not be relational, and you may not even think it's pretty, but it's what people want. in general, when a big customer asks for a procedural or non-relational extension to a vendor's SQL, it'll get put in. marketing departments are notoriously weak on theory. mike olson postgres group uc berkeley
bg0l+@andrew.cmu.edu (Bruce E. Golightly) (07/18/89)
There are indeed times when you can't think in terms of the relational model. Sometimes the users insist that they want what they want, and you can't do it without breaking the model. We had to write an application to do this kind of thing a while back. We don't use Oracle here, we use Ingres. The problem is basically the same using either, though. The suggestion that you use the ROWID (TID in Ingres) has some merit. Relational Technology (Ingres) has repeated pointed out that use of the tuple ID in user specified operations is not a supported feature and that relying on characteristics of the TID may result in a broken application some time. I suspect that Oracle would echo this. Another method might be used, however. In Ingres I have occassionally set up a tablefield that the user can't see on the screen and used it to store the set of possible matches. The idea of PreviousRecord may then be implemented by scrolling up in this table field and doing a simple SELECT based on the result to fill in the visible portions of the screen. The problem with this appraoch is that it requires lots more code. I don't know if you can do something like that in Oracle, so take it with a grain of salt. Bruce
miker@tdpvax.UUCP (07/20/89)
/* ---------- "Re: Oracle: Previous Record" ---------- */ > Because Oracle is a relational and SQL oriented database >which means you should think *sets* not record-at-a-time! >Previous record has no valid meaning in a truly relational system >where by definition physical ordering is irrelevant. OK, I really meant previous record in the context of the results of an SQL query. I don't care in what order Oracle stores rows internally. > You didn't mention *how* you are accessing your data in your posting, >i.e. through SQL*Forms, Sql*Plus, Pro*C, etc. I am using Pro*C. >I would suggest one method is to use the ROWID, just save each row's >ROWID in a "prevrow" variable and then use it to "recall the previous record" But I want to be able to 'Previous' all the way back to the first record if needed. If the table contains 100,000+ records than this gets ridiculous when one must build an internal table or external file and then re-execute a single record query to retreive the record. >Let me again caution you, however, that the very fact that you feel >the need to do this indicates to me that you may not be >"thinking relationally" in which case you are missing the >real power of Oracle and SQL! By that definition Oracle is voilating the same 'rules' in SQL*Forms. Michael Reamy Tandy Information Services
Bron@cup.portal.com (George Bron Faison) (07/21/89)
Re: Oracle: Previous Record On 7/17/89 11:22 mao@eden.uucp (Mike Olson </>) writes: >in article <20532@cup.portal.com>, Bron@cup.portal.com (G.B. Faison) writes >>In an article written 7/7/89 17:48 miker@tdpvax.UUCP writes: >> >>> I am using Oracle on SCO386 Xenix and I wish to be able to be >>> able to retrieve a previous record. >> >> ... you should think *sets* not record-at-a-time! >> Previous record has no valid meaning in a truly relational system >> where by definition physical ordering is irrelevant. > >a valid point, but virtually all commercial implementations of SQL provide >pretty hefty non-relational extensions. in a real relational system, for >example, duplicate tuples are forbidden. it turns out, though, that the >pentagon and the phone company care more about solving their own problems >than preserving the semantics of relational calculus and set theory. the >database systems with which i'm familiar at least offer the option of ... For those unfamiliar with Oracle, let me clarify. Oracle also provides many extensions to SQL, allows duplicate rows to exist in a table, and all the rest. It certainly has what must be called a very robust and complete SQL implementation. Oracle's SQL*Forms product which provides the screen/form interface that is what most "users" interact with does provide the most complete "next/previous record" type capability you could possibly want. You can browse the records in any order you wish, optionally clear records from the que as you do so, and more. However, all access to the database is (and properly so in my opinion) through SQL, and SQL defines no "previous row" and shouldn't. It's just inappropriate. Using PRO*C (C with embedded SQL cursors, fetches, etc.) you can achieve just about anything you want, including implementing a next/previous record/row. My point was, that, as a consultant, I am constantly called in to straighten out the mess that results when one jumps to using procedural, row-at-a-time logic instead of taking advantage of all of the power and functionality provided by the relational system at hand. I see people spend days writing convoluted C code to do things that can be accomplished in 1/2 hour using SQL directly with no procedural statements necessary. They use SQL but they use it like it was CoBol! Sure there are times when nothing but good old procedural code will do, but those times should be few and far between, not routine, or else why are you implementing a "relational" system? I added the caution, because it was obvious from the question that this person might profit by it. Of course, he, you, and I are all free to do these things as we chose - and that's as it should be. On 7/18/89 06:42 bg0l+@andrew.cmu.edu (Bruce E. Golightly) writes: >There are indeed times when you can't think in terms of the relational >model. Sometimes the users insist that they want what they want, and you >can't do it without breaking the model. We had to write an application >to do this kind of thing a while back. I hope you argued like hell against it! >We don't use Oracle here, we use Ingres. The problem is basically the same >using either, though. The suggestion that you use the ROWID (TID in Ingres) >has some merit. Relational Technology (Ingres) has repeated pointed out >that use of the tuple ID in user specified operations is not a supported >feature and that relying on characteristics of the TID may result in a >broken application some time. I suspect that Oracle would echo this. Well, they document it (and that's saying something for Oracle!) and I think it would be safe to use as an "address" as in this case, but who knows, I agree that another method might be preferable. >Another method might be used, however. In Ingres I have occassionally set >up a tablefield that the user can't see on the screen and used it to store >the set of possible matches. The idea of PreviousRecord may then be >implemented by scrolling up in this table field and doing a simple SELECT >based on the result to fill in the visible portions of the screen. The >problem with this appraoch is that it requires lots more code. As discussed above, this isn't necessary in Oracle's SQL*Forms, as it supports very sophisticated access methods for the "forms" type applications, including all manner of next/previous row functions. Ok, I'll shutup! :-) ----------------------------------------------------------------------------- George "Bron" Faison Email: Bron@cup.portal.com USMAIL: Modern Office Systems Technology, Inc. "Be Still!" 6455 Penrith Drive Mechanicsville, VA 23111 Oracle & UNIX Phone: (804) 730-1467 (voice) -----------------------------------------------------------------------------
emuleomo@yes.rutgers.edu (Emuleomo) (07/23/89)
In article <20532@cup.portal.com> George "Bron" Faison writes... > Because Oracle is a relational and SQL oriented database >which means you should think *sets* not record-at-a-time! >Previous record has no valid meaning in a truly relational system >where by definition physical ordering is irrelevant. If you are thinking of SETS alone, then why ever use a CURSOR?? Besides, there is NO fundamental difference between FETCH NEXT and FETCH PREVIOUS!. I hear ANSI is considering including a FETCH PREVIOUS command in SQL. That would be great! (although Informix already has it). Emuleomo O.O. (emuleomo@yes.rutgers.edu) -- ** Research is what I'm doing when I dont know what I'm doing! **
emuleomo@accur8.UUCP (Olumide Emuleomo) (07/23/89)
In article <20645@cup.portal.com>, Bron@cup.portal.com (George Bron Faison) writes: > Re: Oracle: Previous Record > On 7/17/89 11:22 mao@eden.uucp (Mike Olson </>) writes: > >in article <20532@cup.portal.com>, Bron@cup.portal.com (G.B. Faison) writes > >>In an article written 7/7/89 17:48 miker@tdpvax.UUCP writes: > >> > >>> I am using Oracle on SCO386 Xenix and I wish to be able to be > >>> able to retrieve a previous record. > >> > >> ... you should think *sets* not record-at-a-time! > >> Previous record has no valid meaning in a truly relational system > >> where by definition physical ordering is irrelevant. > > > >a valid point, but virtually all commercial implementations of SQL provide > > However, all access to the database is (and properly so in my opinion) > through SQL, and SQL defines no "previous row" and shouldn't. It's > just inappropriate. Using PRO*C (C with embedded SQL cursors, fetches, I seems to me that peaople are just finding excuses for why FETCH PREVIOUS is not implemented in ORACLE. I have seen this grouse several times. If people need a FETCH PREVIOUS, why doesnt ORACLE Corp just implement it? Afterall it is implemented in SQL/FORMS. Right? Besides, people know that it is present in dBASE in the form of SKIP -n. For people who really NEED FETCH PREVIOUS, take a look at INFORMIX SQL.!! Emuleomo O O (emuleomo@accur8@uunet.uu.net)
bsa@telotech.UUCP (Brandon S. Allbery) (07/28/89)
In article <259@accur8.UUCP>, emuleomo@accur8 (Olumide Emuleomo) writes: +--------------- | I seems to me that peaople are just finding excuses for why FETCH PREVIOUS | is not implemented in ORACLE. I have seen this grouse several times. +--------------- Not really. The only reason FETCH NEXT is implemented is that few C programs can handle getting 200,000 rows back from a query all at the same time. SQL is a *batch* language, not an interactive one! I fail to understand why anyone would want to use SQL for interactive purposes. (Of course, I fail to understand why anyone would use SQL at all....) +--------------- | If people need a FETCH PREVIOUS, why doesnt ORACLE Corp just implement it? | Afterall it is implemented in SQL/FORMS. Right? +--------------- SQL*Forms is interactive. The main underlying problem seems to be that people want to write their screen interfaces to Oracle databases (I don't blaim them, SQL*Forms sucks). The problem is that SQL itself is not designed for such hackery; Informix added scrollable cursors primarily so they could bring Perform back up to the same level of functionality it had under 3.3 (pre-SQL). +--------------- | For people who really NEED FETCH PREVIOUS, take a look at INFORMIX SQL.!! +--------------- Or any DBMS which isn't constrained by a batch language: Unify 4.0/Unify 2000, Informix 3.3, Progress, etc. (C'mon, SQL was developed by IBM for use on mainframes. You expect it to be interactive?) ++Brandon -- Brandon S. Allbery @ telotech, inc. (I do not speak for telotech. Ever.) *This article may only be redistributed if all articles listed in the header's* * References: field, and all articles listing this one in their References: * * fields, are also redistributed. *