Link to the name of the same table in different schemas

Question

I ran into a problem with Oracle Query in a .NET based Windows application. I use System.Data.OracleClientto connect to the oracle database. The name of the database myDB. Below the connection string I use:

Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)
(HOST =  172.16.0.24)(PORT = 1522)))(CONNECT_DATA =(SERVICE_NAME = ORCL)));
User ID=myDB;Password=myDB;Unicode=True

If I run the following query, it will give me an incorrect result (here an incorrect result means invalid data. The data does not belong to myDB):

SELECT ID, NAME  
FROM MyTempTable
WHERE ID IN (10780, 10760, 11890)

But if I add the name of the database along with it, it gives the correct result:

SELECT ID, NAME
FROM "myDB".MyTempTable
WHERE ID IN (10780, 10760, 11890)

My limitation is that I cannot add the database name as it is a general application and can work with any database at runtime. Please, help.

Answer 1

, :

CREATE PUBLIC SYNONYM MyTempTable MyTempTable;

, , , ?

---

: , ? , , , , , .

Answer 2

am

ALTER SESSION SET CURRENT_SCHEMA=abc;

abc , .

Answer 3

-, , .net Oracle - . , .

Answer 4

, stjohnroe, , , , , .
myDB , , . ( , "myDB", , , , /proc, , ).
:
1. "myDB", ( 904 )
2. Create a synonym for the schema and table that you really want to access (for example, CREATE SYNONYM myTable FOR aschema.myTable;)
3. Do not forget to grant access rights from the schema that the table belongs to (for example: GRANT SELECT, INSERT, DELETE ON myTable TO myDB (here myDB refers to the user / schema))

Answer 5

Try to add

CONNECT_DATA=(SID=myDB)(SERVICE_NAME=ORCL)

in the connection string.