<i><font color="#663300">Hey all,
I have come across an interesting problem with Oracle based remote views. I have an Oracle user that has created all the tables I will be using for my application, and therefore all the tables are in this user's schema, and all the other user's of this application will be granted rights on these tables. All that seems fine, but when a user uses their userid and password for the shared connection, the Oracle driver kicks an error indicating that the table or view does not exist. I have eventually come to discover that the SQL used in the views does not have the prefix for the schema where the table resides, and therefore the view is looking in the currently logged on user's schema for the named table instead of the table owner's schema.
For instance, user egghead has created the tables for the application and has granted rights to the subsequent users who will be logging onto the application. So all the tables are in egghead's schema. When egghead logs on, the sql statement can read as follows without error:
SELECT * FROM dummy Dummy;
This is the SQL in the View designer when the remote view is created using the designer. Using that SQL statement as user eggface, returns an error that the table or view does not exist. But if the SQL appears like this:
SELECT * FROM egghead.dummy Dummy;
there is no problem, and eggface can perform any operation on that table that has been granted.
To my question, is there any way around this, besides using the CREATE SQL VIEW to recreate all of the views with the schema prefix in front of all the table names?
Mike D</font></i>
Nevermind. Actaully, what I need is a program to change the VFP '!' to the Oracle '.'. I had made the remote views from the SQL of the local views, and stripped off the 'pacts!', instead of just substituting the '!' with a '.'
Devman
mdevlin .at. ajdebruin DO.T com
©2002 Mike Devlin |
