2

I am new to this forum and already searched for 45 minutes to find a solution for my problem. I hope you can help me. A Gateway to a remote Microsoft SQL Database was installed on a Oracle Server (Oracle 12c). The tsnnames.ora file was appropiately set up. For the connection, I created a database link (In the Oracle DB) as follows:

CREATE DATABASE LINK TEL CONNECT TO "fb_B2C" IDENTIFIED BY "passwort" USING 'dg1msql';

When I now execute the Select statement: 

SELECT "name" FROM "sys"."databases"@TEL

it shows me the according databases. Among others, I can see the AB_Colors database. Now, I want to select a view in the AB_Colors database.

Due to the fact I can connect to this database via Excel, I know that in the database AB_Colors, there are 10 Views(A,B,C,..). I would like to select the View C from the database AB_Colors via the DB LInk.

Owner of the View is b2b. How do i need to formulate the select statement to do it? I already tried different writings:

SELECT * FROM b2b.C@TEL;

SELECT * FROM "AB_colors"."b2b"."C"@TEL;

SELECT * FROM [AB_Colors].[b2b].[C]@TEL;

The common error message is: View/Table does not exist

I highly appreciate your help,

Fedja

KPavezC
  • 305
  • 2
  • 5
  • 20
Rebell112
  • 31
  • 5

1 Answers1

0

This is the correct format

SELECT * FROM "b2b"."C"@TEL;

The issue maybe because the database you want to select from is not the one specified in the gateway for dg1msql. You can't add the database name to the query so you must specify it in the gateway connection.

This is defined in

$ORACLE_HOME/dg4msql/admin/initdg4msql.ora

where you should check HS_FDS_CONNECT_INFO

  • Hi Lunc, thanks already for your answer. Do I have to specify it on the Client Level or on the database server (I have to ask the DB Admin) – Rebell112 Jan 30 '19 at 14:15
  • On the database level in the Gateway home. –  Jan 30 '19 at 15:04
  • `HS_FDS_CONNECT_INFO=host_name:port_name//database_name` https://docs.oracle.com/en/database/oracle/oracle-database/12.2/otgiw/init-param-gateway.html#GUID-F5EE8DA1-1429-4CED-8920-EE995141821B – Dmitry Demin Jan 30 '19 at 15:06