--*************************************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.
--***********************************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;
-- 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}));
-- 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 cotion.
-- 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;
With: Oracle SQL Developer 3.0 Early Adopter 2 (3.0.02.83)
From: dermotoneill.blogspot.com/2010/11/cross-database-bridge-statement.html
No comments:
Post a Comment