Pessoal,

Olha o que achei, estava procurando algumas coisas no google.
Achei a pergunta feita na Oracle_Br do Yahoo! sobre acessar informações entre Oracle e MS SQL Server, na íntegra (com correção ortográfica)! Detalhe para a data: 13/06/2007!

Segue abaixo o questionamento e a minha resposta:

Dúvida:
On 6/13/07, marcio_pelarin wrote:
> Bom dia !
>
> É possível acessar informações do MS_SQL_Server 2000 de dentro do
> Oracle e, estando os bancos em servidores diferentes numa mesma rede??
> Se for possível alguém tem exemplo ou algum tutorial de como realizar
> este acesso?
> Utilizo o Oracle 10.2.0.1.0 64 Bits em ambiente MS_Windows_2003 Server.
> Grato,
> Márcio

Resposta:

Marcio,
Eu andei fazendo isso, só que de versões diferentes de MS Sql Server e Oracle, só que esbarrei em um problema, somente conseguia fazer do MS SQL SERVER para o ORACLE, e não o contrario, pois o contrario
necessita de uma ferramenta da própria Oracle.
A conexão do SQL SERVER com ORACLE foi feita via LINKED SERVER.
Abaixo mais detalhes:

Título : Como criar um LINKED SERVER acessando o ORACLE.
Autor : Fernando Franquini (# = Meus comentários sobre o documento do Nilton Pinheiro)
Data : 30/06/2006
Fonte : http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=494758&SiteID=21

Abaixo informações do Nilton Pinheiro (http://www.mcdbabrasil.com.br) retiradas do link forums.microsoft.com.

—
Segue um exemplo de como criar o LINKED SERVER com uma instancia Oracle.

To create a linked server to access an Oracle database instance

1) Ensure the Oracle client software on the server running SQL Server is at the level required by the provider. The Microsoft OLE DB Provider for Oracle requires Oracle Client Software Support File version 7.3.3.4.0 or later, and SQL*Net version 2.3.3.0.4.
# Como já havia uma instalação do Oracle XE neste computador, os clients do Oracle ja estavam instalados.

2) Create an SQL*Net alias name on the server running SQL Server that points to an Oracle database instance. For more information, see the Oracle documentation.
# Já estavam criadas as configurações padrão para acessar o Oracle XE local, foram alteradas adicionando as informações, conforme esta mostrando abaixo:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”I0FMVEVSQU5ETyB0bnNuYW1lcy5vcmEKI1hFX0NBUElOID0KIyAoREVTQ1JJUFRJT04gPQojIChBRERSRVNTID0gKFBST1RPQ09MID0gVENQKShIT1NUID0gMTcyLjE5LjM0LjEyMCkoUE9SVCA9IDE1MjEpKQojIChDT05ORUNUX0RBVEEgPQojIChTRVJWRVIgPSBERURJQ0FURUQpCiMgKFNJRCA9IFhFKQojICkKIyApCiMKI0NvbmZvcm1lIGRhZG9zIHJldGlyYWRvcyBkbyBzZXJ2aWRvciBkZSBkZXN0aW5vLg==” hover_enabled=”0″]I0FMVEVSQU5ETyB0bnNuYW1lcy5vcmEKI1hFX0NBUElOID0KIyAoREVTQ1JJUFRJT04gPQojIChBRERSRVNTID0gKFBST1RPQ09MID0gVENQKShIT1NUID0gMTcyLjE5LjM0LjEyMCkoUE9SVCA9IDE1MjEpKQojIChDT05ORUNUX0RBVEEgPQojIChTRVJWRVIgPSBERURJQ0FURUQpCiMgKFNJRCA9IFhFKQojICkKIyApCiMKI0NvbmZvcm1lIGRhZG9zIHJldGlyYWRvcyBkbyBzZXJ2aWRvciBkZSBkZXN0aW5vLg==[/et_pb_dmb_code_snippet]

3) Execute sp_addlinkedserver to create the linked server, specifying MSDAORA as provider_name, and the SQL*Net alias name for the Oracle database instance as data_ source. This example assumes that an
SQL*Net alias name has been defined as OracleDB.+

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”I3NwX2FkZGxpbmtlZHNlcnZlciDigJhPcmNsRELigJksIOKAmE9yYWNsZeKAmSwg4oCYTVNEQU9SQeKAmSwg4oCYT3JhY2xlRELigJkgKERFRkFVTFQgRE8gRVhFTVBMTykKIyDigJhPcmNsRELigJkgPSBOT01FIERPIExJTktFRCBTRVJWRVIsIE1BTlRJVkUgTyBNRVNNTywgTUFTIFBPREUgU0VSIEFMVEVSQURPIENPRk9STUUgTkVDRVNTSURBREUuCiMg4oCYT3JhY2xl4oCZID0gU0VSVklET1IgREVTVElOTywgTUFOVElWRSBPIE1FU01PLgojIOKAmE1TREFPUkHigJkgPSBTRVJWSURPUiBPUklHRU0sIE1BTlRJVkUgTyBNRVNNTy4KIyDigJhPcmFjbGVEQuKAmSA9IEFMVEVSQURPIFBBUkEg4oCYWEVfQ0FQSU4g4oCYIENPTkZPUk1FIEZPSSBDT05GSUdVUkFETyBOTyB0bnNuYW1lcy5vcmEgKEFDSU1BKQoKc3BfYWRkbGlua2Vkc2VydmVyIOKAmE9yY2xEQuKAmSwg4oCYT3JhY2xl4oCZLCDigJhNU0RBT1JB4oCZLCDigJhYRV9DQVBJTiDigJg=” hover_enabled=”0″]I3NwX2FkZGxpbmtlZHNlcnZlciDigJhPcmNsRELigJksIOKAmE9yYWNsZeKAmSwg4oCYTVNEQU9SQeKAmSwg4oCYT3JhY2xlRELigJkgKERFRkFVTFQgRE8gRVhFTVBMTykKIyDigJhPcmNsRELigJkgPSBOT01FIERPIExJTktFRCBTRVJWRVIsIE1BTlRJVkUgTyBNRVNNTywgTUFTIFBPREUgU0VSIEFMVEVSQURPIENPRk9STUUgTkVDRVNTSURBREUuCiMg4oCYT3JhY2xl4oCZID0gU0VSVklET1IgREVTVElOTywgTUFOVElWRSBPIE1FU01PLgojIOKAmE1TREFPUkHigJkgPSBTRVJWSURPUiBPUklHRU0sIE1BTlRJVkUgTyBNRVNNTy4KIyDigJhPcmFjbGVEQuKAmSA9IEFMVEVSQURPIFBBUkEg4oCYWEVfQ0FQSU4g4oCYIENPTkZPUk1FIEZPSSBDT05GSUdVUkFETyBOTyB0bnNuYW1lcy5vcmEgKEFDSU1BKQoKc3BfYWRkbGlua2Vkc2VydmVyIOKAmE9yY2xEQuKAmSwg4oCYT3JhY2xl4oCZLCDigJhNU0RBT1JB4oCZLCDigJhYRV9DQVBJTiDigJg=[/et_pb_dmb_code_snippet]

4) Use sp_addlinkedsrvlogin to create login mappings from SQL Server logins to Oracle logins. This example maps the SQL Server login Joe to the linked server defined in Step 3 using the Oracle login and
password OrclUsr and OrclPwd:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”I3NwX2FkZGxpbmtlZHNydmxvZ2luIOKAmE9yY2xEQuKAmSwgZmFsc2UsIOKAmEpvZeKAmSwg4oCYT3JjbFVzcuKAmSwg4oCYT3JjbFB3ZOKAmSAoREVGQVVMVCBETyBFWEVNUExPKQojIOKAmE9yY2xEQuKAmSA9IE5PTUUgRG8gTElOS0VEIFNFUlZFUiBSRUdJU1RSQURPIEFDSU1BCiMg4oCYSm9l4oCZID0gVVNVQVJJTyBMT0NBTCBETyBTUUwgU0VSVkVSLCBOTyBNRVUgQ0FTTyDigJhjaXJhbeKAmQojIGZhbHNlID0gTUFOVElWRSBvIE1FU01PCiMg4oCYT3JjbFVzcuKAmSA9IEFMVEVSQURPIFBBUkEgTyBVU1VBUklPIOKAmHN5c3RlbeKAmSwgTUFTIFBPREUgU0VSIFFVQUxRVUVSIE9VVFJPLCBDT0ZPUk1FIE5FQ0VTU0lEQURFIGUgUFJJVklMRUdJT1MgRVhJR0lET1MKIyDigJhPcmNsUHdk4oCZID0gQUxURVJBRE8gUEFSQSBBIFNFTkhBIERPIFVTVUFSSU8g4oCYKioqKioq4oCZCgpzcF9hZGRsaW5rZWRzcnZsb2dpbiDigJhPcmNsRELigJksIGZhbHNlLCDigJhjaXJhbeKAmSwg4oCYc3lzdGVt4oCZLCDigJgqKioqKirigJkKCkVhY2ggT3JhY2xlIGRhdGFiYXNlIGluc3RhbmNlIGhhcyBvbmx5IG9uZSBjYXRhbG9nIHdpdGggYW4gZW1wdHkgbmFtZS4gVGFibGVzIGluIGFuIE9yYWNsZSBsaW5rZWQgc2VydmVyIG11c3QgYmUgcmVmZXJlbmNlZCB1c2luZyBhIGZvdXItcGFydCBuYW1lIG9mIHRoZSBmb3JtIE9yYWNsZUxpbmtlZFNlcnZlck5hbWUuLk93bmVyVXNlck5hbWUuVGFibGVOYW1lLiBGb3IKZXhhbXBsZSwgdGhpcyBTRUxFQ1Qgc3RhdGVtZW50IHJlZmVyZW5jZXMgdGhlIHRhYmxlIFNBTEVTIG93bmVkIGJ5IHRoZSBPcmFjbGUgdXNlciBNQVJZIGluIHRoZSBzZXJ2ZXIgbWFwcGVkIGJ5IHRoZSBPcmNsREIgbGlua2VkIHNlcnZlcjoKCiMgU0VMRUNUICogRlJPTSBPcmNsREIuLk1BUlkuU0FMRVMgKERFRkFVTFQgRE8gRVhFTVBMTykKClNFTEVDVCAqIEZST00gT3JjbERCLi5DT1JQLlVTVUFSSU87CklOU0VSVCBJTlRPIE9yY2xEQi4uQ09SUC5VU1VBUklPIFZBTFVFUyAoNCzigJlGZXJuYW5kb+KAmSk7CiNPayEgRlVOQ0lPTkFORE8uCgrigJQgQWNhYm91IGFxdWkgbyBkb2N1bWVudG8h” hover_enabled=”0″]I3NwX2FkZGxpbmtlZHNydmxvZ2luIOKAmE9yY2xEQuKAmSwgZmFsc2UsIOKAmEpvZeKAmSwg4oCYT3JjbFVzcuKAmSwg4oCYT3JjbFB3ZOKAmSAoREVGQVVMVCBETyBFWEVNUExPKQojIOKAmE9yY2xEQuKAmSA9IE5PTUUgRG8gTElOS0VEIFNFUlZFUiBSRUdJU1RSQURPIEFDSU1BCiMg4oCYSm9l4oCZID0gVVNVQVJJTyBMT0NBTCBETyBTUUwgU0VSVkVSLCBOTyBNRVUgQ0FTTyDigJhjaXJhbeKAmQojIGZhbHNlID0gTUFOVElWRSBvIE1FU01PCiMg4oCYT3JjbFVzcuKAmSA9IEFMVEVSQURPIFBBUkEgTyBVU1VBUklPIOKAmHN5c3RlbeKAmSwgTUFTIFBPREUgU0VSIFFVQUxRVUVSIE9VVFJPLCBDT0ZPUk1FIE5FQ0VTU0lEQURFIGUgUFJJVklMRUdJT1MgRVhJR0lET1MKIyDigJhPcmNsUHdk4oCZID0gQUxURVJBRE8gUEFSQSBBIFNFTkhBIERPIFVTVUFSSU8g4oCYKioqKioq4oCZCgpzcF9hZGRsaW5rZWRzcnZsb2dpbiDigJhPcmNsRELigJksIGZhbHNlLCDigJhjaXJhbeKAmSwg4oCYc3lzdGVt4oCZLCDigJgqKioqKirigJkKCkVhY2ggT3JhY2xlIGRhdGFiYXNlIGluc3RhbmNlIGhhcyBvbmx5IG9uZSBjYXRhbG9nIHdpdGggYW4gZW1wdHkgbmFtZS4gVGFibGVzIGluIGFuIE9yYWNsZSBsaW5rZWQgc2VydmVyIG11c3QgYmUgcmVmZXJlbmNlZCB1c2luZyBhIGZvdXItcGFydCBuYW1lIG9mIHRoZSBmb3JtIE9yYWNsZUxpbmtlZFNlcnZlck5hbWUuLk93bmVyVXNlck5hbWUuVGFibGVOYW1lLiBGb3IKZXhhbXBsZSwgdGhpcyBTRUxFQ1Qgc3RhdGVtZW50IHJlZmVyZW5jZXMgdGhlIHRhYmxlIFNBTEVTIG93bmVkIGJ5IHRoZSBPcmFjbGUgdXNlciBNQVJZIGluIHRoZSBzZXJ2ZXIgbWFwcGVkIGJ5IHRoZSBPcmNsREIgbGlua2VkIHNlcnZlcjoKCiMgU0VMRUNUICogRlJPTSBPcmNsREIuLk1BUlkuU0FMRVMgKERFRkFVTFQgRE8gRVhFTVBMTykKClNFTEVDVCAqIEZST00gT3JjbERCLi5DT1JQLlVTVUFSSU87CklOU0VSVCBJTlRPIE9yY2xEQi4uQ09SUC5VU1VBUklPIFZBTFVFUyAoNCzigJlGZXJuYW5kb+KAmSk7CiNPayEgRlVOQ0lPTkFORE8uCgrigJQgQWNhYm91IGFxdWkgbyBkb2N1bWVudG8h[/et_pb_dmb_code_snippet]

Após esta etapa de criação do LINKED SERVER tem-se que criar uma procedure, uma trigger ou uma função para preencher as tabelas do Oracle, tendo como origem os dados do SQL Server.

Abaixo um exemplo de uma função, retirada do mesmo fórum, feita por Daniel Teixeira.

—
1) Consultar Oracle e Preencher SQl Server através de linked Server, realmente aplicável através do exemplo abaixo:

Com uma função:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”Q1JFQVRFIEZVTkNUSU9OIENvbnN1bHRhT3JhY2xlKEBkZXBhdGFtZW50byBpbnQpIFJFVFVSTlMgVEFCTEUKQVMgUkVUVVJOIChTRUxFQ1QgKiBGUk9NIE9QRU5RVUVSWShPcmFjbGVMaW5rZWRTZXJ2LOKAmXNlbGVjdCAqIGZyb20gb3JhZGIuLlNDT1RULmRlcGFydGFtZW50byBXSEVSRSBkZXB0bm8KPSBAZGVwYXRhbWVudG/igJkpCgoxLjEpU0VMRUNUICogaW50byB0YWJlbGFzcWwgRlJPTSBDb25zdWx0YU9yYWNsZSgxMCkKCjEuMilTRUxFQ1QgKiBpbnRvIHRhYmVsYXNxbCBGUk9NIE9QRU5RVUVSWShPcmFjbGVMaW5rZWRTZXJ2LOKAmXNlbGVjdCAqIGZyb20gb3JhZGIuLlNDT1RULmRlcGFydGFtZW50byBXSEVSRSBkZXB0bm8gPSAyMOKAsik=” hover_enabled=”0″]Q1JFQVRFIEZVTkNUSU9OIENvbnN1bHRhT3JhY2xlKEBkZXBhdGFtZW50byBpbnQpIFJFVFVSTlMgVEFCTEUKQVMgUkVUVVJOIChTRUxFQ1QgKiBGUk9NIE9QRU5RVUVSWShPcmFjbGVMaW5rZWRTZXJ2LOKAmXNlbGVjdCAqIGZyb20gb3JhZGIuLlNDT1RULmRlcGFydGFtZW50byBXSEVSRSBkZXB0bm8KPSBAZGVwYXRhbWVudG/igJkpCgoxLjEpU0VMRUNUICogaW50byB0YWJlbGFzcWwgRlJPTSBDb25zdWx0YU9yYWNsZSgxMCkKCjEuMilTRUxFQ1QgKiBpbnRvIHRhYmVsYXNxbCBGUk9NIE9QRU5RVUVSWShPcmFjbGVMaW5rZWRTZXJ2LOKAmXNlbGVjdCAqIGZyb20gb3JhZGIuLlNDT1RULmRlcGFydGFtZW50byBXSEVSRSBkZXB0bm8gPSAyMOKAsik=[/et_pb_dmb_code_snippet]

2) Preencher Oracle consultando o SQL.

Acredito que a melhor técnica seja usando o DTS se for o SQL Server 2000 ou SSIS se for 2005.
—

Ambiente criado para os testes:

[et_pb_dmb_code_snippet _builder_version=”4.0.6″ code=”U2VydmVyMSAoT3JpZ2VtKTogU1FMIFNlcnZlciBFeHByZXNzIChGUkVFIHYuIGUgSW5zdGFsYcOnw6NvIEZVTEwpIGUgT3JhY2xlIEV4cHJlc3MgRWRpdGlvbiAoRlJFRSkgYW1ib3MgZW0gdW0gV2luZG93cyAyMDAwIFByb2Zlc3Npb25hbC4KClNlcnZlcjIgKERlc3Rpbm8pOiBPcmFjbGUgRXhwcmVzcyBFZGl0aW9uIChGUkVFKSBlbSB1bSBXaW5kb3dzIFhQCgrigJMgVmVyc8OjbyBkbyBPcmFjbGUgRXhwcmVzcyBFZGl0aW9uOgpPcmFjbGUgRGF0YWJhc2UgMTBnIEV4cHJlc3MgRWRpdGlvbiBSZWxlYXNlIDEwLjIuMC4xLjAg4oCTIEJldGEKUEwvU1FMIFJlbGVhc2UgMTAuMi4wLjEuMCDigJMgQmV0YQpDT1JFIDEwLjIuMC4xLjAgQmV0YQpUTlMgZm9yIDMyLWJpdCBXaW5kb3dzOiBWZXJzaW9uIDEwLjIuMC4xLjAg4oCTIEJldGEKTkxTUlRMIFZlcnNpb24gMTAuMi4wLjEuMCDigJMgQmV0YQoK4oCTIFZlcnPDo28gZG8gU1FMIFNlcnZlciBFeHByZXNzCk1pY3Jvc29mdCBTUUwgU2VydmVyIE1hbmFnZW1lbnQgU3R1ZGlvIEV4cHJlc3MgOS4wMC4yMDQ3LjAwCk1pY3Jvc29mdCBEYXRhIEFjY2VzcyBDb21wb25lbnRzIChNREFDKSAyMDAwLjA4MS45MDUzLjAwCk1pY3Jvc29mdCBNU1hNTCAyLjYgMy4wIDQuMCA2LjAKTWljcm9zb2Z0IEludGVybmV0IEV4cGxvcmVyIDYuMC4yODAwLjExMDYKTWljcm9zb2Z0IC5ORVQgRnJhbWV3b3JrIDIuMC41MDcyNy40MgpPcGVyYXRpbmcgU3lzdGVtIDUuMC4yMTk1″ hover_enabled=”0″]U2VydmVyMSAoT3JpZ2VtKTogU1FMIFNlcnZlciBFeHByZXNzIChGUkVFIHYuIGUgSW5zdGFsYcOnw6NvIEZVTEwpIGUgT3JhY2xlIEV4cHJlc3MgRWRpdGlvbiAoRlJFRSkgYW1ib3MgZW0gdW0gV2luZG93cyAyMDAwIFByb2Zlc3Npb25hbC4KClNlcnZlcjIgKERlc3Rpbm8pOiBPcmFjbGUgRXhwcmVzcyBFZGl0aW9uIChGUkVFKSBlbSB1bSBXaW5kb3dzIFhQCgrigJMgVmVyc8OjbyBkbyBPcmFjbGUgRXhwcmVzcyBFZGl0aW9uOgpPcmFjbGUgRGF0YWJhc2UgMTBnIEV4cHJlc3MgRWRpdGlvbiBSZWxlYXNlIDEwLjIuMC4xLjAg4oCTIEJldGEKUEwvU1FMIFJlbGVhc2UgMTAuMi4wLjEuMCDigJMgQmV0YQpDT1JFIDEwLjIuMC4xLjAgQmV0YQpUTlMgZm9yIDMyLWJpdCBXaW5kb3dzOiBWZXJzaW9uIDEwLjIuMC4xLjAg4oCTIEJldGEKTkxTUlRMIFZlcnNpb24gMTAuMi4wLjEuMCDigJMgQmV0YQoK4oCTIFZlcnPDo28gZG8gU1FMIFNlcnZlciBFeHByZXNzCk1pY3Jvc29mdCBTUUwgU2VydmVyIE1hbmFnZW1lbnQgU3R1ZGlvIEV4cHJlc3MgOS4wMC4yMDQ3LjAwCk1pY3Jvc29mdCBEYXRhIEFjY2VzcyBDb21wb25lbnRzIChNREFDKSAyMDAwLjA4MS45MDUzLjAwCk1pY3Jvc29mdCBNU1hNTCAyLjYgMy4wIDQuMCA2LjAKTWljcm9zb2Z0IEludGVybmV0IEV4cGxvcmVyIDYuMC4yODAwLjExMDYKTWljcm9zb2Z0IC5ORVQgRnJhbWV3b3JrIDIuMC41MDcyNy40MgpPcGVyYXRpbmcgU3lzdGVtIDUuMC4yMTk1[/et_pb_dmb_code_snippet]

O que eu pensei na época era criar uma procedure e atualizar uma tabela no Oracle, mas como as minhas informações não eram de locais fixos, eram muito variadas não resolveu assim e a solução encontrada foi via aplicar mesmo, fazendo acesso as duas bases diferentes via ODBC, pois estava sendo desenvolvido um sistema para fazer os acessos, ai foi resolvido assim.

Espero ter ajudado.

— Aqui acabou! 😀

sem mais,
capin