forked from OpenLinkSoftware/SQL-Scripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Attach-Oracle-Table-To-Virtuoso
58 lines (45 loc) · 3.18 KB
/
Attach-Oracle-Table-To-Virtuoso
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- Oracle HR Demo Database
-- ODBC Data Source Name (DSN): ora10ma-hr
-- OpenLink ODBC Driver Connection Attributes (X.500 Name): DSN=ora10ma-hr;HOST={request-broker-dns-name};SVT=Oracle;DATABASE=HR
-- Remove FOREIGN KEYS (a Referential Integrity Constraint)
-- Cleanup
ALTER TABLE "Oracle"."HR"."COUNTRIES" DROP CONSTRAINT "COUNTR_REG_FK" FOREIGN KEY ("REGION_ID") REFERENCES "Oracle"."HR"."REGIONS" ("REGION_ID") ;
ALTER TABLE "Oracle"."HR"."DEPARTMENTS" DROP CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "Oracle"."HR"."EMPLOYEES" ("EMPLOYEE_ID") ;
ALTER TABLE "Oracle"."HR"."EMPLOYEES" DROP CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "Oracle"."HR"."JOBS" ("JOB_ID") ;
ALTER TABLE "Oracle"."HR"."EMPLOYEES" DROP CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "Oracle"."HR"."EMPLOYEES" ("EMPLOYEE_ID") ;
ALTER TABLE "Oracle"."HR"."EMPLOYEES" DROP CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "Oracle"."HR"."DEPARTMENTS" ("DEPARTMENT_ID") ;
ALTER TABLE "Oracle"."HR"."JOB_HISTORY" DROP CONSTRAINT "JHIST_EMP_FK" FOREIGN KEY ("EMPLOYEE_ID") REFERENCES "Oracle"."HR"."EMPLOYEES" ("EMPLOYEE_ID") ;
ALTER TABLE "Oracle"."HR"."JOB_HISTORY" DROP CONSTRAINT "JHIST_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "Oracle"."HR"."JOBS" ("JOB_ID") ;
ALTER TABLE "Oracle"."HR"."JOB_HISTORY" DROP CONSTRAINT "JHIST_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "Oracle"."HR"."DEPARTMENTS" ("DEPARTMENT_ID")
ALTER TABLE "Oracle"."HR"."LOCATIONS" DROP CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID") REFERENCES "Oracle"."HR"."COUNTRIES" ("COUNTRY_ID") ;
-- Drop Currently Attached (Remote or External) Tables
DROP TABLE "Oracle"."HR"."LOCATIONS" ;
DROP TABLE "Oracle"."HR"."COUNTRIES" ;
DROP TABLE "Oracle"."HR"."COUNTRIES2" ;
DROP TABLE "Oracle"."HR"."DEPARTMENTS" ;
DROP TABLE "Oracle"."HR"."EMPLOYEES" ;
DROP TABLE "Oracle"."HR"."JOBS" ;
DROP TABLE "Oracle"."HR"."JOB_HISTORY" ;
DROP TABLE "Oracle"."HR"."REGIONS" ;
-- Prepare Remote Data Source (ODBC DSNs) for use by Virtuoso
-- Note: http://docs.openlinksw.com/virtuoso/fn_vd_remote_data_source/
DB..VD_REMOTE_DATA_SOURCE ('ora10ma-hr','','','') ;
-- Attach Tables via Data Source
ATTACH TABLE "HR"."COUNTRIES" AS "Oracle"."HR"."COUNTRIES" FROM 'ora10ma-hr' ;
ATTACH TABLE "HR"."COUNTRIES2" AS "Oracle"."HR"."COUNTRIES2" FROM 'ora10ma-hr' ;
ATTACH TABLE "HR"."DEPARTMENTS" AS "Oracle"."HR"."DEPARTMENTS" FROM 'ora10ma-hr' ;
ATTACH TABLE "HR"."EMPLOYEES" AS "Oracle"."HR"."EMPLOYEES" FROM 'ora10ma-hr' ;
ATTACH TABLE "HR"."JOBS" AS "Oracle"."HR"."JOBS" FROM 'ora10ma-hr' ;
ATTACH TABLE "HR"."JOB_HISTORY" AS "Oracle"."HR"."JOB_HISTORY" FROM 'ora10ma-hr' ;
ATTACH TABLE "HR"."HR.LOCATIONS" AS "Oracle"."HR"."LOCATIONS" FROM 'ora10ma-hr' ;
ATTACH TABLE "HR"."REGIONS" AS "Oracle"."HR"."REGIONS" FROM 'ora10ma-hr' ;
COMMIT WORK ;
-- Test Queries
SELECT TOP 5 * FROM "Oracle"."HR"."COUNTRIES" ;
SELECT TOP 5 * FROM "Oracle"."HR"."COUNTRIES2" ;
SELECT TOP 5 * FROM "Oracle"."HR"."DEPARTMENTS" ;
SELECT TOP 5 * FROM "Oracle"."HR"."JOBS" ;
SELECT TOP 5 * FROM "Oracle"."HR"."JOB_HISTORY" ;
SELECT TOP 5 * FROM "Oracle"."HR"."COUNTRIES" ;
SELECT TOP 5 * FROM "Oracle"."HR"."LOCATIONS" ;
SELECT TOP 5 * FROM "Oracle"."HR"."REGIONS" ;