So the previous blog post configures and tests the ODBC configuration.
Next steps would be to configure the Oracle HS Services to use the installed ODBC driver.
First steps login the the Oracle RDBMS host.
cd $ORACLE_HOME/hs/admin
Create a new configuration file <init>DSNNAME.ora i.e. initSQLSERVERHLC.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
## HS init parameters
#HS_FDS_CONNECT_INFO=SQLSERVERHLC
HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
HS_NLS_NCHAR=UCS2
HS_LONG_PIECE_TRANSFER_SIZE=1024
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
cd $ORACLE_HOME/network/admin, add following entry to tnsnames.ora
SQLSERVERHLC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SID = SQLSERVERHLC)
)
(HS = OK)
)
cd $ORACLE_HOME/network/admin, add following entry to listener.ora
(SID_DESC =
(SID_NAME=SQLSERVERHLC)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM=dg4odbc)
)
Reload the listener via : lsnrctl reload, you should see the new service is listening
Service “SQLSERVERHLC” has 1 instance(s).
Instance “SQLSERVERHLC”, status UNKNOWN, has 1 handler(s) for this service…
You can now create a database link within Oracle using syntax as below
CREATE DATABASE LINK “SQLSERVERHLC”
CONNECT TO ‘username’ IDENTIFIED BY ‘password’
USING ‘SQLSERVERHLC’;
Test the dblink via a SQL statement such as :-
select * from dbo.tbl_hlc_time@sqlserverhlc
That should now complete the Oracle HS configuration, and you should be connected to SQL Server.