Cross Database Bridge Statement

Oracle allows you to query separate databases using Database Links.
You can even setup a Database Link in an Oracle database with a non Oracle database such as SQL Server or Sybase.

Creating these types of Database Links can take some time, and sometimes you just want to reference a small bit of data on another Database and use it in your query.

SQL Developer allows you define connections to multiple databases including non Oracle databases. You can query each connection separately but until now you couldn't easily query different data sources (connections) at the same time in the one statement.

BRIDGE statement
This is a SQL Developer only command. It is not supported in SQL*Plus or directly in the Oracle database. SQL Developer interprets this statement and performs multiple actions to return the result.

BRIDGE statement can be used in two ways
1) To copy a table and its data from one connection to another
2) To facilitate cross connection/database queries

--*******INTRO*******
--The BRIDGE command is an Oracle SQL Developer client command
--It is not suported by any other product (SQL*Plus,....)
--It is used within Oracle SQL Developer to enable the "Copy to Oracle" feature and peform complex migration reports
--It is not suported as a standalone command, but can be experimented with
--The BRIDGE command is still immature and the parsing of its syntax including spaces is still basic.

-- *******SETUP*******
-- CREATE MS Access Northwind connection called, accessNorthwind
-- CREATE SQL Server 2005 Northwind connection called, sqlserverNorthwind
-- CREATE MySQL 5 world connection called, mysqlWorld
-- Run each part of this script against an Oracle connection


-- This new Oracle SQL Developer client command to create a SQL Developer Connection at the same time as creating a schema
CREATE CONNECTION oracleTest(GRANT DBA TO oracleTest identified by oracleTest);
CREATE CONNECTION oracleTest2(CREATE USER oracleTest2 identified by oracleTest2);
GRANT connect,resource,create view to oracleTest2;


--*******FEATURE SUMMARY*******
-- BRIDGE command can be used in two distinct ways
-- 1) TO COPY A TABLE from one connection (A third party connection or oracle connection) to another connection.
-- 2) BRIDGE QUERIES (TO QUERY TABLES OVER MULTIPLE CONNECTIONS)
-- COPY A TABLE is fairly straight forward, you specify the target table name and the query and connection used to define and populate the target table
-- BRIDGE QUERIES  uses the same method as above to move data from different connections to a target connection, but it then allows you to specify a query to run against the newly migrated tables/data.
-- It then DROPs the new Tables after the Query has run. From a user point of view it looks like a Query have taken place between different connections as they never see the target tables created,populated,queries and droped behind the scenes.

--*******SYNTAX*******
/*
<BRIDGE>:
BRIDGE <TABLEDEFINITION> ( ',' <TABLEDEFINTION>)*
[<targetQuery>] ;

<TABLEDEFINITION>:
[<targetConnectionName>](<newTargetTableName>)  AS [<sourceConnectionName>](<SOURCEQUERY>)[APPEND|REPLACE]

<SOURCEQUERY>:
SQL valid on the sourceConnection. Supports Dynamic Substitution.
({<defaultQuery}) gets replaced with a comma delimited list of values
{<defaultQuery} iterates the source query for each value returned

*/
--*******COPY A TABLE*******
-- CROSS CONNECTION TABLE/DATA MIGRATION
--This creates a new NEWcustomers1 table in the current connection(oracle), using a table from access (customers) referenced through the accessNorthwind connection.
BRIDGE NEWcustomers1 AS accessNorthwind(SELECT * FROM customers);

--Note that the SQL that defines the target table is run against the source Connection (accessNorthwind), so you have to use the source databases SQL syntax. In this case, MS Access.
BRIDGE NEWcustomers2 AS accessNorthwind(SELECT TOP 10 * FROM customers);

--A target table is defined using any valid query, so it doesnt have to be a straight copy of a particular table.Rows can be filtered, columns can be added, infact the target table could be based on a VIEW.
--In this case, no rows are returned , but an empty table NEWcustomers3 is created
BRIDGE NEWcustomers3 AS accessNorthwind(SELECT * FROM customers WHERE 1=2);

--If a target table name already exists, then an error is thrown.
--if you want to insert data to an existing table use the APPEND flag
BRIDGE NEWcustomers3 AS accessNorthwind(SELECT TOP 10 * FROM customers)APPEND;
BRIDGE NEWcustomers3 AS accessNorthwind(SELECT TOP 10 * FROM customers)APPEND;

--If the table already exists but you want to replace it , use the REPLACE flag
BRIDGE NEWcustomers3 AS accessNorthwind(SELECT TOP 10 * FROM customers)REPLACE;

--Heres an example of how the target table can be defined using an "inline view". Note, NOW() is a MS Access function returning the current time
BRIDGE NEWcustomers4 AS accessNorthwind(SELECT contactName, address AS NewAddress, NOW() AS currentTime FROM customers);

--DYNAMIC SUBSTITUTION
--A lot of times you want to filter the amount of rows being copied to the target table.
--It is handy to use a resource on the target connection to define how to filter the source table.
--Dynamic Substitution can be used to
--A) sub in a list of values Ex: 'London','Dublin','San Francisco'
--   Just place the substitution within ({})
--B) iterate over the query n times using a differnt value
--  Just place the substitution within {} with no round brakets
--  Note that column names can be used as bind variables elsewhere in the source query

--Example using dynamic substitution to create a list.
--{SELECT 'London' FROM DUAL} is run first on the default connection, the list is substituted into the source query
--SELECT * FROM customers WHERE city IN ('London')
BRIDGE NEWcustomers5 AS accessNorthwind(SELECT * FROM customers WHERE city IN ({SELECT 'London' FROM DUAL}));

--Example using dynamic substitution to iterate the query n times
--  {select 'Categories' SQLDEVTABLENAME FROM DUAL UNION select 'Customers' SQLDEVTABLENAME FROM DUAL UNION select 'Employees' SQLDEVTABLENAME FROM DUAL} is run first on the default connection
-- The following queries are created
--  select count(*) AS numrows, 'Categories' AS name FROM Categories
--  select count(*) AS numrows, 'Customers' AS name FROM Customers
--  select count(*) AS numrows, 'Employeees' AS name FROM Employeees
-- The rows from each query is inserted into the target table NEWLineSizes

BRIDGE NEWLineSizes AS accessNorthwind(select count(*) AS numrows, ':SQLDEVTABLENAME' AS name FROM
                                 {select 'Categories' SQLDEVTABLENAME FROM DUAL UNION select 'Customers' SQLDEVTABLENAME FROM DUAL UNION select 'Employees' SQLDEVTABLENAME FROM DUAL})APPEND;

--Example of defining two target tables using a comma to seperate the two distinct tables.
BRIDGE NEWcustomers6 AS accessNorthwind(SELECT * FROM customers),
  NEWcustomers7 AS accessNorthwind(SELECT TOP 5 * FROM customers);

--Example of defining two target tables using two seperate connection.
--NEWcustomers8 uses an access connection, NEWemployees8 uses a SQL Server connection
BRIDGE NEWcustomers8 AS accessNorthwind(SELECT * FROM customers),
  NEWemployees8 AS sqlserverNorthwind(SELECT * FROM employees);

--Example of creating the target table on a different connection from the default WorkSheet connection  
BRIDGE oracleTest(NEWcustomer9) AS accessNorthwind(SELECT * FROM customers);

--Example of creating on another target connection using the default connection to define the source
BRIDGE oracleTest(Newcustomer10) AS (SELECT * FROM NEWcustomers1);

--Example of using an Oracle connection as the source connection
BRIDGE NewCustomer11 AS oracleTest(SELECT * FROM NewCustomer10);

--********BRIDGE QUERIES**********
--BRIDGE can be used to peform cross connection queries. The user does not need to know that tables are created and populated on the target as they are deleted automatically after the query has run
--This show how to run an Oracle query against a MS Access table
BRIDGE TEMPcustomers AS accessNorthwind(SELECT * FROM customers)
SELECT * FROM TEMPcustomers WHERE rownum <=10;

--This shows how you can join a MS Access table and an Oracle table together in one query
BRIDGE TEMPcustomers AS accessNorthwind(SELECT * FROM customers)
SELECT * FROM TEMPcustomers,all_users;

--This shows how to query a MS Access database, a SQL Server database and an Oracle database all within the one query.
--To do this in the Oracle database you would have to setup mutiple DATABASE LINKS which can be difficult to do.
BRIDGE TEMPcustomers AS accessNorthwind(SELECT * FROM customers),
  TEMPemployees AS sqlserverNorthwind(SELECT * FROM employees),
  TEMPCustomersO  AS oracleTest(SELECT * FROM NewCustomer10)
SELECT  * FROM TEMPcustomers, TEMPemployees, TEMPCustomersO;  

-- MIGRATION EXAMPLES
--RUN STATEMENT ON MIGRATION REPOSITORY CONNECTION
-- This example is used in the migration reports to list the status of migrated objects.
-- It queries the target Oracle database(connection) for the status of objects found in the migration repository.
-- Both Oracle target database and Oracle migration repostitory can be on seperate Oracle databases
BRIDGE all_objects$targetdb AS &&SQLDEVPREF_TARGETCONN(SELECT status,object_name,owner FROM all_objects WHERE UPPER(owner) IN ({SELECT DISTINCT UPPER(schemaname) FROM  mgv_all_schema_details}))
SELECT r.desc_object_name,
  d.schemaname,
  'SQLDEV:LINK:&&SQLDEVPREF_TARGETCONN:null:'||UPPER(d.schemaname)||':'||r.DESC_OBJECT_NAME||':'||UPPER(d.objname)||':oracle.dbtools.migration.workbench.core.ConnectionAwareDrillLink' ObjectName,
  NVL2(a.status,a.status,  'Missing') status
FROM mgv_all_details d LEFT OUTER JOIN all_objects$targetdb a
                  ON (UPPER(d.schemaname) = UPPER(a.owner)
                  AND UPPER(d.objname) = UPPER(a.object_name)) , md_registry r
WHERE d.capturedorconverted='CONVERTED'
AND d.objtype in('MD_TABLES','MD_VIEWS','MD_INDEXES','MD_STORED_PROGRAMS','MD_TRIGGERS')
AND r.object_name = d.objtype
ORDER BY schemaName ASC, objtype DESC,objname ASC;

Search This Blog

Dermot's Tech Tips

Cross Database Bridge Statement