Re: Displaying sparsely populated tables

In article <6418.2b19db9b_at_hayes.com> fgreene_at_hayes.com wrote:
: In article <1992Nov30.042949.7346_at_tin.monsanto.com>, nghoff_at_albert.monsanto.com (Norman G. Hoffman) writes:
: > ...Suppose I want to keep track of appointments which can be made
: > on the hour. I have an appointment table with date/time and
: > customer columns. I want to store only appointments in the
: > table, but I would like to display a scrollable register of
: > hourly entries with the customer name filled in for appointments
: > and blank otherwise. I also want to be able to scroll beyond
: > the first and last appointments.
: >
: > I'm using Oracle 6.0 with SQL*Forms 3.0 and PL/SQL 1.0 under
: > DOS 5.0 on a PC compatible....
:
: As I understand it, you only want to store valid appointments
: in the table, but also be able to display invalid (ie, unbooked)
: appointments as well without storing the blank records in the
: the data base. Problem is that Oracle wants to store each and
: every displayed record to the database.

Right.  

: Solution is that you need to force the form into displaying
: unfilled appointments after each retrieval and then erase them
: again before doing any commits. You need to write a POST_QUERY
: procedure that will start at record 1, save the current time slot,
: and go to the next record. If this new records time is greater than
: the standard increment, insert a new blank record. Repeat the loop
: until you are at the bottom of the retrieved records.

(I'm not at the machine [or documentation] right now, so I may not have the terminology completely correct.) It seems to me I was not allowed to do individual record processing (NEXT_RECORD?) in a POST_QUERY trigger. I did find, however, that I could do it with the execute query (KEY_EXEQRY, sp?) trigger. Are you saying I could process the individual records on the tail end of the query, insert new records where appropriate, and these new records would be displayed on the form along with the retrieved records, in their proper order?

: On the output cycle, you need a PRE_COMMIT trigger that does
: essentially the reverse. That is, start at record 1. Check each
: record in turn and, if some field is blank (probably the appointee's
: name) delete the record. When you hit the last record, exit the
: procedure and allow the normal commit process to fire.

The "output cycle" would be the case in which an existing record was modified or a new record inserted (new appointment made). So each time a record is modified or inserted, I'd have to delete all the unbooked appointments, then perform the modify or insert operation, commit it, then retrieve the database records and insert new blank records again? Ouch! A technical question -- would it also be reasonable to perform a rollback as the first step instead of deleting the unbooked entries?

: As you are doing all of this in memory, the expansion/contraction
: should be very fast.

I don't have experience with Oracle yet (soon to be rectified), but with other database systems I have found insert and delete operations to be fairly expensive in terms of system resources, and rollbacks even more so. Is this not the case with Oracle?

I was hoping there would be some way to fake a scrolled region such that not all displayed records actually came from the table. Has anyone done this with SQL*FORMS, PRO*C, or PRO*Pascal?

Norm Hoffman
nghoff_at_bb1t.monsanto.com Received on Tue Dec 01 1992 - 16:50:53 CET