Database Gateway
Install Gateway
To install Oracle Database Gateway for MSSQL Server...
unzip download...
cd gateways
./runInstaller
Oracle base: /opt/oracle
Software location: /opt/oracle/product/18.0.0
Component Name: Oracle Database Gateway for Microsoft SQL Server
Enter host & server name.
Edit Listener
Add an entry to the 'listener.ora' file...
(SID_DESC =
(GLOBAL_DBNAME = dg4msql.company.com)
(SID_NAME = dg4msql)
(ORACLE_HOME = /opt/oracle/product/18.0.0 )
(ENVS="LD_LIBRARY_PATH=/opt/oracle/product/18.0.0/dg4msql/driver/lib:/opt/oracle/product/18.0.0/dg4msql/lib:/opt/oracle/product/18.0.0/lib")
(PROGRAM=dg4msql)
)
Edit TNS Names
Add an entry to the 'tnsnames.ora' file...
dg4msql.duracell.com=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host.company.com)(PORT=1524)))(CONNECT_DATA=(SID=dg4msql))(HS=OK))
Edit the Gateway settings
Edit /opt/oracle/product/18.0.0/dg4msql/admin/initdg4msql.ora
HS_FDS_CONNECT_INFO=[host]:1433//server
Create a Database Link
Create a database link...
CREATE PUBLIC DATABASE LINK dblink_name
CONNECT TO "username"
IDENTIFIED BY "password"
USING ‘dg4msql’;
Restart the Listener
To restart the default listener...
lsnrctl stop
lsnrctl start
To restart a custom listener...
lsnrctl stop LISTENER_1524
lsnrctl start LISTENER_1524
Test the Connection
To test the connection in SQLPlus...
select count
from mssql_tbl@dblink_name;
Import Data
To create a stored procedure that imports data from MSSQL into ORACLE...
create or replace my_load_proc
begin
insert into my_oracle_table
select *
from mssql_tbl@dblink_name;
end;
Create Views
Instead of importing data, you can create a view into the remote database like...
create view MSSQL_TBL as select * from MSSQL_TBL@DBLINK_NAME;
To make the view visible to multiple users, create a role and assign that role the user.
A lazy way to do this is...
grant select on MSSQL_TBL to CONNECT;
Views within a View
In order to perform an operation like...
select count from DBLIMSDEV.INSTRUMENTS@lablt101;
You need to...
1) Create the schema...
create user dblimsdev identified by <password>;
grant all privileges to delimsdev;
2) Create a view to a table...
create view INSTRUMENTS as select * from INSTRUMENTS@DBLINK_NAME;
3) Create a traditional DBLink to the new schema...
CREATE DATABASE LINK lablt101
CONNECT TO dblimsdev IDENTIFIED BY <password>
USING
'(DESCRIPTION = (SOURCE_ROUTE = OFF) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = <port>))) (CONNECT_DATA = (SID = <sid>) (SERVER = DEDICATED)))';