Re: Linked Server in Oracle

On 11 Mar 2007 00:59:30 -0800, "Kayda" <blairjee_at_gmail.com> wrote:

>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

Obviously you come from a sqlserver background. This results in you thinking
- Oracle is sqlserver sold by a different vendor - The concepts are similar and you don't need to read the Oracle documentation
- As soon as you can't find something, you start posting instead of doing your own research. This seems to replace the act of reading manuals by Microsofties.
As this is not the place to abstract complete manuals, I will just provide some general pointers *and* warnings.

1 Oracle has a concept called 'distributed database'. This is discussed in a separate manual.
2 Oracle also has Transparent Gateways. This is an interface to sqlserver and similar product. I don't know whether there is a specific Sybase driver, but Transparent Gateways, aka Hetergeneous Services supports ODBC, and Heterogeneous Servers are discussed in the Net Administrators Manual.
*ODBC scales badly*
3 Oracle has Native Dynamic SQL. This allows you to submit any command at runtime. Basic commands are execute immediate and open <cursor variable > for <string>.
Probably 'Morgan's library' at http://www.psoug.org has examples, the PL/SQL reference manual definitely has them, and you would find them also at http://asktom.oracle.com
*Native Dynamic SQL* also scales *badly*.

That said you would probably better set up a Materialized View in Oracle consisting of
select * from day20070205_at_remotedb
union
select * from day20070206_at_remotedb

etc
to get rid of the NDS issue.

Hth

-- 
Sybrand Bakker
Senior Oracle DBA