Linked Server in Oracle

Hi:

Pretty inexperienced with Oracle but just wanted to know if there is a way in Oracle to setup what SQL Server calls a "Linked Server" in Oracle--a link to some other product's db that (in SQL Server) is referenced as:

ServerName.dbname.dbo.tablename

Also, if this is possible, I would like know if it is possible to write a stored proc in Oracle that would set the tablename in the FROM clause dynamically. Basically, I'm trying to link to a Sybase database that has database views that are named according to the date, i.e. :

detail20070204
detail20070205

etc.

So at runtime the tablename has to be changed. The ETL tool I have to use for this project won't let me set the source tablename according to a variable (or set it dynamically in any way), and the product that uses the Sybase database won't let me create any stored procs on it. This is a problem because my ETL package has to run automaticlly and figure out "OK, I"ve got data from Feb 4th, 2007, I now need Feb 5th, 2007 and need to look at the databse view detail20070205. So not merely a matter of changing a WHERE clause.

My target server is Oracle so, since Oracle will be available I'm thinking of this strange workaround, using Oracle as the source also by creating a link to the Sybase database and writing an Oracle stored procedure that references the 'linked' Sybase server, setting the view name according to the date I'll query from my Oracle target.

Any ideas on this?

Thanks,
Kayda Received on Sun Mar 11 2007 - 03:59:30 CDT