Status: Verified
GOAL:
How to run sql 92 query on the OpenEdge Database from Oracle?
GOAL:
How to link OpenEdge ODBC client to Oracle?
GOAL:
OpenEdge with Oracle
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
FIX:
Oracle provided ODBC link properties file under $ORACLE_HOME/hs/admin named either inithsodbc.ora or initdg4odbc.ora.
You need to verify whether $ORACLE_HOME/bin/hsodbc or $ORACLE_HOME/bin/db4odbc is 32-bit or 64-bit binary using the file command on the UNIX.
Usually hsodbc is a 32-bit executable the later db4odbc may come in both 32-bit and 64-bit executable. This verification is important as the Progress/OpenEdge odbc can be either
32-bit or 64-bit ( in later 10.2x version) which will have 64-bit libraries.
If your Oracle installation is 64-bit, but the $ORACLE_HOME/bin/hsodbc is 32-bit, you need to use $ORACLE_HOME/lib32 for the Oracle library reference on the listener.ora configuration. Oracle supplies $ORACLE_HOME/bin/hsodbc 32-bit only. The latest Oracle version provides $ORACLE_HOME/bin/dg4odbc which can be 32-bit or 64-bit depending on the Oracle installation.
In this example $ORACLE_HOME/bin/hsodbc is used as it is the only one available on the 64-bit Oracle installation on the server while writing this solution.
This solution steps through the setup on the Oracle side assuming that the Progress/OpenEdge ODBC is already setup (P21252 ) and working on the UNIX using testodbc
c-based program (P21252 ).
Make sure you know the Oracle's default user Oracle's password as it is necessary. Any oracle configuration changes made by any other users will not be saved.
You need to "su oracle" on UNIX before any other listed oracle file configuration and start/stop of the Oracle's listener.
Oracle Steps are below:
1. Copy $ORACLE_HOME/hs/admin/inithsodbc.ora or $ORACLE_HOME/hs/admin/initdg4odbc.ora to $ORACLE_HOME/hs/admin/inithsodbcoe.ora or $ORACLE_HOME/hs/admin/initdg4odbcoe.ora.
You can name it anyway you like but the format needs to be init<any_name>.ora.
For the rest of the steps, "inithsodbcoe.ora" will be used for consistencies sake instead of using both inithsodbcoe.ora/initdg4odbcoe.ora. Also the term "DLC" is used for the OpenEdge installation path
2. Add ODBC related information on the file as the following and comment rest of the entries with "#". Failure to do so will give error later.
But the error will show the line number that is not a valid entry to read by Oracle. After you add all the information, save inithsodbcoe.ora
The inithsodbc.ora has the following parameter:
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = <odbc data_source_name>
HS_FDS_TRACE_LEVEL = <trace_level>
HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>
#
# ODBC specific environment variables
#
set ODBCINI=<full path name of the odbc initilization file>
#
# Environment variables required for the non-Oracle system
#
set <envvar>=<value>
Sample from a working configuration:
#
HS_FDS_CONNECT_INFO =results
HS_FDS_TRACE_LEVEL = 0
HS_FDS_SHAREABLE_NAME =DLC/odbc/lib/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/tmp/makbar/odbc.ini
set ODBCINST=/tmp/makbar/odbcinst.ini
set LD_LIBRARY_PATH=DLC/lib:DLC/odbc/lib:$LD_LIBRARY_PATH
#
# Environment variables required for the non-Oracle system
#
# set <envvar>=<value>
3. cd to $ORACLE_HOME
etwork/admin. You will need to add a new entry to tnsnames.ora and also on the listener.ora files.
3a. On the listener.ora, you need to add a new "SID_DESC" under the "SID_LIST_LISTENER" section. Do not remove the existing "SID_DESC" that is already there..
A working sample is shown below:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr2/ora/product/10.2.0/Db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = hsodbcoe)
(ORACLE_HOME = /usr2/ora/product/10.2.0/Db_1)
(PROGRAM = hsodbc)
(ENVS=LD_LIBRARY_PATH=DLC/lib:DLC/odbc/lib:$ORACLE_HOME/lib32:$LD_LIBRARY_PATH)
)
)
On the above, the PROGRAM refers to the actual Oracle's binary name under the $ORACLE_HOME/bin which you already did unix "file" command to findout whether it is 32-bit or 64-bit.
If you have dg4odbc binary, you need to add dg4odbc as a value.
3b. On the tnsnames.ora, add a new entry at the end of the list of existing name as
HSODBCOE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = uxtssolar3)(PORT = 1521))
)
(CONNECT_DATA =
(SID = hsodbcoe)
)
(HS=OK)
)
4. Now all the files are configured on the oracle side and assuming you are able to save them using oracle user "oracle". You need to restart the Oracle listener.
Again, you need to be user "oracle" to restart the listener using the following command:
lsnrctl stop # to stop the running listener
lsnrctl start # to start the listener
lsnrctl services # to show all the running Oracle SID services
For the for the hsodbcoe, it will display as the following. But, do not worry as the hsodbcoe is a runtime service that connect to the 3rd party database through ODBC client
and not all the time as other Oracle instances are running.
Service "hsodbcoe" has 1 instance(s).
Instance "hsodbcoe", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:2 refused:0
LOCAL SERVER
if you use the command "tnsping hsodbcoe" afterward, you will see similar to the following output:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = uxtssolar3)(PORT = 1521))) (CONNECT_DATA = (SID = hsodbcoe)) (HS=OK))
OK (20 msec)
5. Now that all the configuration is done, you need to create a dblink on the oracle to link the hsodbcoe using sqlplus as:
First log in to oracle instance as:
sqlplus <oracle userid>/<password>@<SQL*NET Service Name>
sqlplus ts10g2so">john/dow@ts10g2so # using your existing SQL*NET service that you work on.
The above will connect you to Oracle database as usual. Use the following command to create a new public dblink:
CREATE PUBLIC DATABASE LINK hsodbclink CONNECT TO "john" IDENTIFIED BY "dow" using 'hsodbcoe';
Now that you are all set, you can use the following query to query the OpenEdge Database:
SELECT * FROM <table_name>@hsodbclink;
SELECT * FROM pub.state@hsodbclink; # from sports2000 database
If you have windows or other UNIX oracle client, you can connect to the oracle as usual using the same service name as you use to connect to oracle from those machines, and use the SELECT query shown above.
.