Prev: Inverse function of ODCINumberList ?
Next: select_catalog_role does not let me see data dictionary . why?
From: amoroder on 22 Jun 2010 11:27
Hello,

I remember that we tested this years ago( DB on windows with ODBC ) ,
but don't know where to search for dcumentation today.

Is it possible to create a link to a non oracle db ( sqlserver or
other ) from our oracle 11 DB ?
Is the access to the external tables transparent ?

Thanks
Andreas
From: Vladimir M. Zakharychev on 22 Jun 2010 15:35
On Jun 22, 7:27 pm, amoroder <amoro...(a)sb-brixen.it> wrote:
> Hello,
>
> I remember that we tested this years ago( DB on windows with ODBC ) ,
> but don't know where to search for dcumentation today.
>
> Is it possible to create a link to a non oracle db  ( sqlserver or
> other ) from our oracle 11 DB ?
> Is the access to the external tables transparent ?
>
> Thanks
> Andreas

Definitely. Research Oracle Heterogeneous Services, Generic
Connectivity (employs ODBC on multiple platforms and OLE DB on Windows
only, and is included with the database at no additional price) and
Transparent Gateways (target database specific, separately licensed
option.) Full documentation on your Oracle release is available at
http://tahiti.oracle.com - searchable online and downloadable in PDF
for offline reading.

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
From: Mladen Gogala on 22 Jun 2010 15:49
On Tue, 22 Jun 2010 08:27:26 -0700, amoroder wrote:

> Hello,
>
> I remember that we tested this years ago( DB on windows with ODBC ) ,
> but don't know where to search for dcumentation today.
>
> Is it possible to create a link to a non oracle db ( sqlserver or other
> ) from our oracle 11 DB ?
> Is the access to the external tables transparent ?
>
> Thanks
> Andreas


It is possible. I tested it on Red Hat 5.2 (the current version is 5.5)
with unixODBC and freeTDS, I was able to connect to SQL Server and select
data. Mixed transactions are not supported. That, in particular, means
that you can not push data from Oracle. You can do insert as select, you
can even create a view, but no mixed transactions.



--
http://mgogala.byethost5.com
From: Mladen Gogala on 22 Jun 2010 16:14
On Tue, 22 Jun 2010 19:49:18 +0000, Mladen Gogala wrote:

> On Tue, 22 Jun 2010 08:27:26 -0700, amoroder wrote:
>
>> Hello,
>>
>> I remember that we tested this years ago( DB on windows with ODBC ) ,
>> but don't know where to search for dcumentation today.
>>
>> Is it possible to create a link to a non oracle db ( sqlserver or
>> other ) from our oracle 11 DB ?
>> Is the access to the external tables transparent ?
>>
>> Thanks
>> Andreas
>
>
> It is possible. I tested it on Red Hat 5.2 (the current version is 5.5)
> with unixODBC and freeTDS, I was able to connect to SQL Server and
> select data. Mixed transactions are not supported. That, in particular,
> means that you can not push data from Oracle. You can do insert as
> select, you can even create a view, but no mixed transactions.

The only problem I had was with the character set. The setting I had to
check was HS_LANGUAGE. The setting that did the trick for me was
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1




--
http://mgogala.byethost5.com
From: mhoys on 23 Jun 2010 09:38
On Jun 22, 9:49 pm, Mladen Gogala <n...(a)email.here.invalid> wrote:
> On Tue, 22 Jun 2010 08:27:26 -0700, amoroder wrote:
> > Hello,
>
> > I remember that we tested this years ago( DB on windows with ODBC ) ,
> > but don't know where to search for dcumentation today.
>
> > Is it possible to create a link to a non oracle db  ( sqlserver or other
> > ) from our oracle 11 DB ?
> > Is the access to the external tables transparent ?
>
> > Thanks
> > Andreas
>
> It is possible. I tested it on Red Hat 5.2 (the current version is 5.5)
> with unixODBC and freeTDS, I was able to connect to SQL Server and select
> data. Mixed transactions are not supported. That, in particular, means
> that you can not push data from Oracle. You can do insert as select, you
> can even create a view, but no mixed transactions.
>
> --http://mgogala.byethost5.com

What do you mean with "you can not push data from Oracle"?

The following works fine in my test setup (executed from Oracle
sqlplus):
SQL> insert into test001(a)mssql01 values(1,'test');
SQL> commit;

This is Oracle 10g on CentOS connected to SQL Server 2008R2 on Windows
XP using unixODBC, freeTDS and heterogeneous services.
The only "problem" is that as long as you don't commit or rollback in
your Oracle session, the SQL Server table apparently remains locked
for other users. Don't know if this is "normal" behavior or not.


Matthias Hoys

 |  Next  |  Last
Pages: 1 2
Prev: Inverse function of ODCINumberList ?
Next: select_catalog_role does not let me see data dictionary . why?