Lynn Pettis (9/4/2010)
We are using 8g and 10g. From what has been discussed, one of the databases will be moving from 8g to 10g. This is to eliminate duplicate data for the database. For development purposes on another project, a copy of the database was made in 10g. Since it was determined to would be easier to relink an Access front end from 8g to 10g rather than integrate the 10g databasse back to the 8g database, that is the direction we are going. At least then the two databases will be on the same server and integrated together in the new app that will replace the Access front end and a manual request process.
"8" doesn't have MERGE. If you have to write joined updates, you will need to use the correlated subquery method. And, you need to do it both in the update list and in the WHERE clause for performance... otherwise, your generally updating the whole table.
I've got a couple of tricks on disk somewhere about such things has how to build a "Tally Table" on the fly in Oracle... I'll see if I can still find them for you.
The other thing you need to be keenly aware of is that Oracle doesn't (unless something has changed) return result sets directly to a GUI. Instead, you need to build a "Reference Cursor" and those are best built in what they refer to as a "Package" using a "Global Reference Cursor". Packages are a bit of a pain to program but they do allow you to "group" stored procedures and functions into logical units. I'm still not sure I like that but some swear by it.
Think of the above as "reading suggestions".
--Jeff Moden
Change is inevitable... Change for the better is not.