When we were hooking up the "Copy to Oracle" functionality, Dermot added a cool bit of code to allow us to do queries across connections.The Copy to Oracle function allows you point at a table in any supported connection type and choose copy to oracle from the menu. This then goes and creates the table in Oracle and populates it with data from the source table.
Now, as I said, to do this, Dermot added some functionality to enable this. This functionality is very flexible and the UI in Copy to Oracle uses this mechanism to achieve the functionality.
The command shown here is just the basics of what this can do and we will document this in more detail as we go on.
BRIDGE DEMO_CUSTOMERS AS access
(SELECT * FROM "Order Details") ;
This command can be broken into a few pieces. After the BRIDGE token, the identifier is the name of the new table to be create in the current Oracle connection. The access identifier represents the connection that we will copy the table from and the query between the braces is the query which will be run on the source connection as the data for the new table.
Running the commands listed here, drop the table if it exists and creates it on the Oracle Connection.
We then run a query against that connection we get the following results in the results panel.
This is just a small view into what the bridge command can do and how it is used in the Copy to Oracle Functionality today.
Now we have the table is in Oracle. However, we can do something even cooler than this. We can join a table in Oracle with a table in Access and show the results.
BRIDGE TEMPcustomers AS access
(SELECT * FROM "Order Details")
SELECT * FROM TEMPcustomers,Products
where Products.productid=TEMPcustomers.ProductID;
This is a little step further. We take the table in Access, copy it to Oracle as a temporary table and join it with a table in Oracle to show the results below.
View comments