Have Geek Brain

Query SQL Server from Oracle - update metadata

I am a SQL Server developer with a challenge in Oracle. The DBA has set up a DBLink in Oracle that points to a SQL Server database. I am writing a SQL Server data view and then an Oracle side view to join with additional Oracle data.

Problem: If I change the definition of the view on SQL Server, even "Select * From myview @dblink" with "Invalid Column" error. Closing TOAD and reopening seems to fix the problem, but the real question is, how do I get Oracle to reread the metadata without reloading the connection?

0


a source to share


4 answers


This seems to be an issue with the Oracle 10g client. The current solution is to disconnect and reconnect. Considering that I have not been able to find anyone else with this problem, I will assume that it is a problem with my client configuration.



0


a source


This sounds like a problem with TOAD, not oracle. What happens if you do this in SQL * Plus?



+1


a source


I don't know if you understand you, but if you have dblink

one that points to the SQL server database in your Oracle DB and you need the data in the SQL server:

SELECT *
FROM TABLE@dblink

SELECT "COL", "COL2", "COL3
from TABLE@dblink


SELECT T."COL", H."COL"
FROM TABLE1@dblink T, TABLE2@dblink H
WHERE T."ID" = H."ID"
+1


a source


Maybe you can do ?:

alter view <<view_name>> compile;

I have not tested this because I have no db connection from Oracle to MSSQL.

0


a source






More articles:


All Articles