get data from oracle and sql in one sql statement...

[lidds]

I will try and explain my problem as simply as I can

I have an Oracle database (external app) that contains a number of tables that I need to get data from, however I also have an SQL database that contains a number of tables that my app is using.

e.g. Oracle Table name PD_112

Structure:

ID DISP_ID
1 5
2 8
3 5

e.g. SQL Table name DispTbl

DISP_ID DESC
5 Piping
6 Electrical

What I want to do is get all the data from the Oracle table to display in a list control however I want the values in the DISP_ID column of the Oracle table to be change to the corrosponding DESC values from the DispTbl e.g. 5 from the Oracle table would display Piping instead of 5.

I know how to do this if both the tables where in one database system e.g. Oracle or SQL, however I am not sure the best why to do this when the tables are in 2 different data sources.

Below is the code for doing this in the same datasource:

SELECT PD_112.ID, DispTbl.DESC FROM PD_112 INNER JOIN DispTbl ON PD_112.DISP_ID = DispTbl.ID

I am using VB.Net 2005, just for info

Any help would really be appreshiated

Thanks

Simon

[leinad31]

Create a database link in Oracle to access SQL database, or implement comparable feature in SQL Server... its essentially a direct connection between the databases rather than relying on connection to application. The other table would then be a "remote table" that can be joined to local tables.

[abhijit]

Also remember, the performance of your query will noticeably degrade.
The other alternative is to create a materialized view across the network in your Oracle DB. This view could be set to be refreshed overnight.

You might want to read this.

[lidds]

I was thinking, is there anyway that I could run an SQL query on the Oracle database and hold this in a datatable and then run another SQL query on the datatable to achieve this?

[abhijit]

I was thinking, is there anyway that I could run an SQL query on the Oracle database and hold this in a datatable and then run another SQL query on the datatable to achieve this?

A materialized view is akin to a table, except you cannot insert records in it.

Yes, you could also store the records from your SQL database to your Oracle Database. There are two ways I am familiar with.

1) This is the least complicated way. Export the SQL server table to a text file. Load this text file using SQLLoader utility in Oracle.

2) Establish a link between Oracle Data and SQL Server. Then run a CREATE TABLE AS SELECT * FROM {SQL Server Table}. This will create the table for you.

There's a third way I have heard of, but never used personally.
3) SSIS. This will let you push data from SQL server to Oracle.

[leinad31]

I was thinking, is there anyway that I could run an SQL query on the Oracle database and hold this in a datatable and then run another SQL query on the datatable to achieve this?

Depends on how many records are involved in the tables to be "joined by the application".

Yet another alternative, if the number of records from SQL Server table is relatively small and rarely changes would be to spawn a temporary table in Oracle and fill that up with records from SQL Server (passed through application connections)... subsequent queries can then be done at Oracle side.

But the best approach for OLTP would still be the database link; its relatively real time, doesn't incur additional storage and has to be maintained/synced regularly unlike materialized view, doesn't use up memory for long periods unlike temp table, and you'll need the link anyway if you want to go ahead with materialized view.