nigel@cnw01.storesys.coles.oz.au (Nigel Harwood) (06/27/91)
I asked a question a few days ago about how to use a cursor to pass sequentially through a table updating and committing as it went. Using Oracle 6 I had been unable to do this because as soon as I did a commit the next fetch would report a "fetch out of sequence" error. The responses I received started out by letting me know that Oracle didn't invent Cursors and so can't have the credit or the blame ;-) The suggestions went as follows : 1. You do not need to do a select to be able to update a table. Yes I understand that but I need the information from the row. 2. Why not do a select for each row rather than using a cursor. I am trying to avoid this at it sounds inefficient. 3. Why not leave your commit till the end of the processing. Firstly, I have no idea how big the table will be and I understand that I may strike a limit if it's too big. Secondly, some of the information I am processing for each row comes from an external device and I can't afford to risk loosing it so I want to commit for each row. 4. Use array processing and commit after each array fetch. I don't really understand this one. Won't I have the same problem ? 5. Turn on the mode=ansi flag. Tried it but unfortunately it didn't work. 6. Open a cursor no update for passing through the table and use another to do the updating. Wouldn't this also be subject to performance problems ? At the moment this then leaves me with doing a select by rowid of each row of the table which I am not really happy with. I will quickly restate my processing in the hope that someone can offer a further suggestion. I have a maintenance transaction table, a reference table to be maintained, a set of devices on DLC links which contain a memory file to be maintained. I need to apply each of the maintenance transactions i.e. add item etc, agains the reference table and the DLC devices memory files. After each transaction is applied I must update the statuses in the transaction row to indicate how things went. In addition I may receive some information from the DLC devices which must also be included. A major factor is that when I apply the transaction to the DLC devices the information they send to me in response cannot be recovered if lost. This is the reason why I want to commit after each transaction. So, any futher suggestions ? PS. Some responses I got seemed to imply there was some way of taking up where you were in the table across cursor open/closes. For my application I can't understand how this is possible ? Regards -- <<<<<<<<<<<<<<<<<<<<<<<<< Nigel Harwood >>>>>>>>>>>>>>>>>>>>>>>>>>> << Post: Coles Myer Ltd, PO Box 2000 Tooronga 3146, Australia >> << Phone: +61 3 829 6090 E-mail: nigel@cnw01.storesys.coles.oz.au >> << FAX: +61 3 829 6886 >>