>Does anyone have any thoughts/comments/experiance as to which is better SQL passthrough or Remote Views.
Remote views for updatable data. Views or SPT for read-only [I use both depending on the SQL complexity - views for simple SQL, SPT for complex SQL].
>Please also consider the following:
>
>1. The application also has to point at different Oracle boxes depending on its current status (development, UAT, production, DR, etc.)
Create a different DBC for each because you will need to create the views using different connections. Then you can open and SET DATABASE TO the DBC you need to be using at the time. Make sure the views names are slightly different than the actual table name [e.g., v_MyTable for a view and MyTable for the table]
>2. There are some 60+ tables currently used by the system
No big deal hear.
>3. These tables exist in both Oracle & FoxPro
OK.
>4. We need to implement this with the least impact on the current system (code).
Can't comment on this because I obviously do not know your code.
>P.S. If I was to use the remote view approach; is there anyway I can use indexes set up in Oracle instead of having to reindex each one of my views every time they're opened?
Your Oracle tables definitely need to be indexed for optimized retrievals by the Oracle server. However, the data returned to views or SPT cursors can not natively use Oracle indexes. You can index a view or cursor as long as table buffering is not in effect. You can change buffering from 5 to 3 with Cursorsetprop() for indexing, then back to 5 after indexing.
Mark McCasland
Midlothian, TX USA